Gus Koutsivitis
asked on
Dreamweaver VB.Net - Get current Identity number from SQL and increment by one
Need to get the current sql identity number or any number within a table and increment by one. I need this to be done during the OnLoad event of a form. basically it will autogenerate an id number incremented by one everytime a form is loaded. I am using Dreamweaver VB.Net. I am also using SQL 2005 Server. Please, I need your help ASAP. Thank You!!
Here is a simple example which reads a table called tblNextID which has just the one column in it called NextID. Once it reads it, the value in that table is incremented.
Now this example uses MSAccess, you can just change the connection string to your database
Dim nextId As String
Dim conn As New OleDbConnection("Provider= Microsoft. Jet.OleDb. 4.0;Data Source=C:\Web_EE\Big EE.mdb")
conn.Open()
Dim oleCmd As OleDbCommand
'Run the SQL to get the id
oleCmd = New OleDbCommand("SELECT NextID FROM tblNextID", conn)
nextId = oleCmd.ExecuteScalar().ToS tring()
MsgBox("Next Available ID is " & nextId)
'Now increment that ID
oleCmd.CommandText = "UPDATE tblNextID SET NextID = NextID + 1"
oleCmd.ExecuteNonQuery()
'Close
conn.Close()
conn = Nothing
Now this example uses MSAccess, you can just change the connection string to your database
Dim nextId As String
Dim conn As New OleDbConnection("Provider=
conn.Open()
Dim oleCmd As OleDbCommand
'Run the SQL to get the id
oleCmd = New OleDbCommand("SELECT NextID FROM tblNextID", conn)
nextId = oleCmd.ExecuteScalar().ToS
MsgBox("Next Available ID is " & nextId)
'Now increment that ID
oleCmd.CommandText = "UPDATE tblNextID SET NextID = NextID + 1"
oleCmd.ExecuteNonQuery()
'Close
conn.Close()
conn = Nothing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why cant you just create and maintain your own table? Just have one column in there which you read and update each time