|
This article describes how to retrieve the identity value when you add a record into a SQL Server table with an auto incrementing identity field.
Getting the value of an identity field when you add a record to database in most of the cases is useful.
One way to accomplish that is to use store procedures. But how you do it if you don't want to use a stored procedure? By using ExecuteScalar method in OleDbCommand or whichever provider you use.
Sample Code:
Dim sSql As String = "INSERT INTO Contents(CustID, FirstName, LastName) VALUES(?,?); SELECT SCOPE_IDENTITY();"
Dim cn As New OleDbConnection(myConnectionString)
Dim cm As New OleDbCommand(sSql, cn)
cm.Parameters.AddWithValue("@FirstName", "FirstName")
cm.Parameters.AddWithValue("@LastName", "LastName")
cm.Connection.Open()
iCustID = cm.ExecuteScalar()
cm.Connection.Close ()
1) In case you use oledb provider you can use named parameters otherwise parameters must be written as a question mark (?) in your sql statement.
2) While you fill the parameters you have to add them in the same order they are written in sql statement.
Thomas is an experienced Visual Basic developer, with expertise of 7+ years developing especially financial applications. His main IT skills are VB, SQL, Crystal Reports - should you need a Visual Basic developer for your projects feel free to contact Thomas through his personal website www.Kaloyani.com or through VBprofs.com - the newest Visual Basic and VB.NET resources portal.
Source: www.VBprofs.com |
