update, getting primarykey aswell of the current record added

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Once a user clicks on the "add" button it adds the new record to the DB but
i want at the same time to get the primarykey of the record i just added. Is
this possible without writing a new "SELECT * bla bla"? and if so, how 2?

thx in advance,
Richard

My code
-----------------
Dim strSQL As String = "insert into tbl_Users" & _
"(U_Username, U_Password, U_Email, U_KlantenNR, U_Auth_Code,
U_ToegangsID, U_Registered_On, U_Posts, U_Last_Logged_In, U_Banned,
U_Avatar, U_IP, U_Active, U_Deleted, U_MSN)" & _
"values('"& tbGebruikersNaam.Text & "'," & _
"'"& tbWachtwoord.Text & "'," & _
"'"& tbEmailAdres.Text & "'," & _
""& strKlantenNR &"," & _
"'"& i & "'," & _
"1," & _
"'"& DateTime.Now.ToString("MM'-'dd'-'yyyy HH':'mm':'ss") &"'," & _
"0," & _
"'"& DateTime.Now.ToString("MM'-'dd'-'yyyy HH':'mm':'ss") &"'," & _
"0," & _
"'no_avatar.gif'," & _
"'"& strIP & "'," & _
"0," & _
"0," & _
"'"& strMSN & "')"

Dim intID As Integer
Dim MyCommand As New SqlCommand(strSQL, myConnection)
myConnection.Open()
MyCommand.ExecuteNonQuery()
myConnection.Close()

------
 
The best way would be a stored procedure in your SQL database. In a SQL stored procedure you can have more than one command. For this I would put your INSERT command, then a command that looks like this:

SELECT @Key = @@IDENTITY
RETURN @Key

Your INSERT Command would have a number of Parameters to use instead of creating a string command.

If you want to do the quick and dirty, and keep string commands, create a second command string (strSQL2 = "SELECT @@IDENTITY")

Your execution code would look like this:
myConnection.Open()
MyCommand.ExecuteNonQuery()
MyCommand.CommandString = strSQL2
RetVariable = MyCommand.ExecuteScalar()
myConnection.Close()
 
both thx, it worked perfect!

Miha Markic said:
Hi,

Just place "; SELECT SCOPE_IDENTITY()" after insert statement.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Richard said:
Once a user clicks on the "add" button it adds the new record to the DB but
i want at the same time to get the primarykey of the record i just
Click to expand...
added.
Click to expand...
 
Back
Top