Here is a simple way to get identity while inserting records in the database.
Lets create a Customers table
CREATE TABLE [dbo].[customers] (
[customersID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) ,
[LastName] [varchar] (50) ,
[CompanyName] [varchar] (50) ,
[LoginName] [varchar] (50) ,
[Password] [varchar] (16) ,
) ON [PRIMARY]
In order to get identity while inserting the data in the table we will have to customize our code a little.
Function addCustomer()
Dim dbConnection As System.Data.IDbConnection = _
New System.Data.SqlClient.SqlConnection("Connection String")
Dim queryString As String = "INSERT INTO [customers] ([FirstName], " & _
" [LastName], [CompanyName], [LoginName], [Password]) " & _
" VALUES ('" & txtFirstName.Text & "', '" & txtLastName.Text & _
"', '" & txtCName.Text & "'," & _
"'" & txtLogName.Text & "', '" & txtpass.Text & "') " _
"select @@Identity as customersID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim rowsAffected As Integer = 0
dbConnection.Open()
Try
Dim customersID As Long
Dim dataReader As System.Data.IDataReader = _
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
While dataReader.Read
customersID = dataReader("customersID")
rowsAffected = 1
End While
dataReader.Close()
If rowsAffected <> 0 Then
Message.Text = "Record Successfully inserted. Your ID is " & customersID
End If
Finally
dbConnection.Close()
End Try
End Function
Normally for data insertion we would have used ExecuteNonQuery (dbCommand.ExecuteNonQuery) method but in this case we also need the identity. So will be using ExecuteReader method. Further more SQL String is also modified.
What we have done over here is retrieved the identity value in CustomerID in a DataReader and stored it in a variable now we can use this value for further development according to the situation
|
| Author: Aplemakh Alexander 22 Oct 2004 | Member Level: Bronze Points : 0 |
Thank you very much for this article! I try to find an information about getting identity values in ASP.NET a very long time. It seems strange that this problem not so popular. So, thank you very much again!
|
| Author: Ammar Ahmed Siddiqui 09 Feb 2005 | Member Level: Silver Points : 0 |
Dear Aplemakh Alexander,
Thank you for your feedback. I am very sorry for replying so late.
Best Regards,
Ammar Siddiqui
|