getting primary key id on insert

i have the following code in visual studio 2005 using VB
it is running an insert query - this works fine but i want to know how can i get the primaty key value(which is auto generated) of the row that i just inserted...

Dim conn As New SqlConnection(My.Settings.connStr)

conn.Open()

Dim sql As String = "INSERT INTO tblProspect (Prspct_FirstName, Prspct_LastName, Prspct_PropIDPrimary, Prspct_PropIDSecondary, Prspct_ApplicationStatus, Prspct_DateSubmittedOn, Prspct_PrimaryRent, Prspct_SecondaryRent, Prspct_MoveInDate) VALUES ('" & Me.txtFName.Text & "','" & Me.txtLName.Text & "','" & Me.cmbPrimary.SelectedValue & "','" & Me.cmbSecondary.SelectedValue & "','Pending','" & Now & "','" & Me.txtPrimRent.Text & "','" & Me.txtSecRent.Text & "','" & Me.dtMoveIn.Value & "')"

Dim cmd As New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

i want to get the Prspct_Id which is the primary key of the row that i just inserted..

thanks

Your auto generated key is referred to as an IDENTITY column in SQL Server. The way you obtain its value is to follow the insert with a SELECT of one of the @.@.IDENTITY server variables (actually these are really system functions but behave like variables) - data type numeric(38,0): @.@.IDENTITY, @.@.IDENT_CURRENT('table_name'), or @.@.SCOPE_IDENTITY. See http://msdn2.microsoft.com/en-us/library/ms187342.aspx for more information.

However, rather than firing off two adhoc queries I would define these two steps in a stored procedure and call that, and have it return the identity as an output variable or a simple return value.

Also, your VB as it stands looks as though it leaves you wide open to a SQL injection attack - as a general rule one should use the parameterised approach rather than building up query strings that include directly concatenated values taken straight from input fields. Here's an example of this approach (it's in C# rather than VB I'm afraid, but you should get the gist regardless).

SqlConnection dbConn =

new SqlConnection(connString);

dbConn.Open();

try

{

SqlCommand command = dbConn.CreateCommand();

command.CommandText =

"insert into tblProspect([Prspct_FirstName], [Prspct_LastName])" +

"values (@.firstName, @.lastName)";

command.Parameters.AddWithValue("@.firstName", txtFName.Text().Trim());

command.Parameters.AddWithValue("@.lastName", txtLName.Text().Trim());

command.ExecuteNonQuery();

}

finally

{

dbConn.Close();

}

|||

hi,

Michael is absolutely right about the dynamic SQL code and relative SQL Injection perils, and you should at least use a parametrized execution query with relative parameters instead of simple positional substitution of value markers combining the execution string...

so the sqlString = " .... VALUES ( '" & me.txtBox.text &"');" should be avoided as evil

and Michael is again absolutely right advising for a stored procedure instead of dynamic SQL...

but, just as didactival sample, you can write

Private Sub ParametQuery()

Dim conn As New System.Data.SqlClient.SqlConnection

With conn

.ConnectionString = "Server=(Local);Database=tempdb;Trusted_Connection=True;"

.Open()

End With

Dim cmd As New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "CREATE TABLE dbo.TestTB ( Id int NOT NULL IDENTITY PRIMARY KEY, Data varchar(10) NOT NULL );"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

For i As Integer = 1 To 5

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "INSERT INTO dbo.TestTB VALUES ( @.Data ); SELECT @.NewId = SCOPE_IDENTITY();"

.CommandType = CommandType.Text

.Connection = conn

Dim par As System.Data.SqlClient.SqlParameter

par = New System.Data.SqlClient.SqlParameter

With par

.Direction = ParameterDirection.Input

.ParameterName = "@.Data"

.SqlDbType = SqlDbType.VarChar

.Size = i.ToString.Length

.SqlValue = i.ToString

End With

.Parameters.Add(par)

par = New System.Data.SqlClient.SqlParameter

With par

.Direction = ParameterDirection.Output

.ParameterName = "@.NewId"

.SqlDbType = SqlDbType.Int

End With

.Parameters.Add(par)

par = Nothing

End With

cmd.ExecuteNonQuery()

Console.WriteLine("Retrieving NewId from parameter: value = {0}", cmd.Parameters("@.NewId").Value)

Next

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "DROP TABLE dbo.TestTB;"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

conn.Dispose()

cmd = Nothing

conn = Nothing

End Sub

a parameter collection is defined, passing the "in" value to be used for insertion for the [Data] column..
as SQL Server supports multistatement execution in execution queries as well (even in stored procedures), you can get the new autogenerated IDENTITY value via already pointed out SCOPE_IDENTITY() built in function.. so you can add an additional parameter to your execution query which will be loaded with the result of
SELECT @.NewId = SCOPE_IDENTITY();
and, after the command has been executed, you can inspect the parameter value for your personal uses as desired..

regards

|||

thanks for the help;

i am kinda new to this and i am just modifying a previously written code.. so i dont want to make and big changes cause i dont know where else i would then need to modify - how can i use SCOPE_IDENTITY() with my current code?

i can add SELECT SCOPE_IDENTITY() AS LASTID at the end of the insert statement, and the how do i read or copy the value of LASTID to another variable

thanks

|||

hi,

imranmp wrote:

thanks for the help;

i am kinda new to this and i am just modifying a previously written code.. so i dont want to make and big changes cause i dont know where else i would then need to modify - how can i use SCOPE_IDENTITY() with my current code?

i can add SELECT SCOPE_IDENTITY() AS LASTID at the end of the insert statement, and the how do i read or copy the value of LASTID to another variable

thanks

very bad

as you say you are new, you should learn the "good" way and not write code just for having the job done

anyway, if you really want to go that way just replace the .ExecuteNonQuery method with a .ExecuteScalar. similar to

Dim conn As New System.Data.SqlClient.SqlConnection

With conn

.ConnectionString = "Server=(Local);Database=tempdb;Trusted_Connection=True;Connection Timeout=5;"

.Open()

End With

Dim cmd As New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "CREATE TABLE dbo.TestTB ( Id int NOT NULL IDENTITY PRIMARY KEY, Data varchar(10) NOT NULL );"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

For i As Integer = 1 To 5

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "INSERT INTO dbo.TestTB VALUES ( " & i.ToString & " ); SELECT SCOPE_IDENTITY();"

.CommandType = CommandType.Text

.Connection = conn

End With

Dim iNewId As Integer = cmd.ExecuteScalar()

Console.WriteLine("NewId: value = {0}", iNewId.ToString)

Next

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "DROP TABLE dbo.TestTB;"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

conn.Close()

cmd = Nothing

conn = Nothing

but please consider the best practices..

regards