Identity

L

Leeus

Well-known member
Joined
Dec 23, 2002
Messages
50
Hi all this is my code for a start.
Sub sqlinsert()
Try
kermitconnection = New SqlConnection("Server=localhost;uid=USERNAME;pwd=PASSWORD;database=DATABASE")
kermitconnection.Open()
Dim cmdinsertsupportitem As SqlCommand
cmdinsertsupportitem = New SqlCommand("Insert employees (lastname,firstname,department) Values (@thelastname,@thefirstname,@thedepartment)", kermitconnection)
cmdinsertsupportitem.Parameters.Add("@thelastname", alastname.text)
cmdinsertsupportitem.Parameters.Add("@thefirstname", afirstname.text)
cmdinsertsupportitem.Parameters.Add("@thedepartment", adepartment.text)
cmdinsertsupportitem.ExecuteNonQuery()

kermitconnection.Close()
Catch
End Try
End Sub
Click to expand...
I have looked all over and found quite a few examples of getting the Identity after I have done this insert, does anyone know of a way of implementing this into my existing code?
 
I assume this is SQL server? Youll have to add "SELECT @@IDENTITY" to your SqlCommand and run an actual query, not ExecuteNonQuery. You can use a DataReader or a DataSet - but you need to get the value out. The "SELECT @@IDENTITY" will give you the ID of the last inserted row, in column1 or row1 of your executed statement.

You may have to also run "SET NOCOUNT ON" as the first part of your SQL statement as the rowcount of the actual INSERT may cause problems with retrieving the ID (I cant remember).

Heres the final SQL statement that should be built (all in one SQL string):
Code: 
SET NOCOUNT ON
Insert employees (lastname,firstname,department) Values (@thelastname,@thefirstname,@thedepartment)
SELECT @@IDENTITY

-Ner
 
Back
Top