finding out the id of a recently created record

[TheDude5B]

Hi,

i am wanting to add some data to one table within my MySQL database
using asp.net in VB, however, i want to also add some of the same data
to another table at the same time. Easy yes, but one field within the
second table must contain the unique ID of the record which has just
been created in the other table.

For example.
table 1 will contain: name, address, tel, email etc...

table 2 will contain: unique id of record just created in table 1,
account details etc...

so is there a way to retreive this id once the record has just been
created?

Here is my code so far which is assigned to a button event.

mySqlQuery = "INSERT INTO table1(name, address,tel,email)"
mySqlQuery &= " VALUES ('" & strName & "','" & strAd & "','" & strTel &
"', '" & strEmail & "')"

Dim strConn As String =
ConfigurationSettings.AppSettings("MySqlConnectionStr")

Dim myConnection As New MySqlConnection(strConn)

myConnection.Open()

Dim myCommand As New MySqlCommand()

myCommand.Connection = myConnection
myCommand.CommandText = mySqlQuery
myCommand.ExecuteNonQuery()
myConnection.Close()

 

[S. Justin Gengo]

Dude,

This is how I get the identity back after an insert via stored procedure:

1.. Just after the stored procedure's insert command put:
2..
3.. SELECT @@Identity AS pk_NewRowId
4.. RETURN
For your code (it looks like you're not using a stored procedure) you'd do
it like this:

mySqlQuery = "INSERT INTO table1(name, address,tel,email)"
mySqlQuery &= " VALUES ('" & strName & "','" & strAd & "','" & strTel &"',
'" & strEmail & "');SELECT @@Identity AS pk_NewRowId"

(Note that there is a semicolon separating the two statements.)

Then use a MyCommand.ExecuteScalar to return the new id.

By the way, instead of concatenating your long string with &= you can just
continue the line like this:

mySqlQuery = "INSERT INTO table1(name, address,tel,email)" _
& " VALUES ('" & strName & "','" & strAd & "','" & strTel &"', '" & strEmail
& "');SELECT @@Identity AS pk_NewRowId"

That's a space then an underscore after the first line. That tells visual
studio that next the line is a continuation of the first. When you
concatenate with &= you are actually creating a new string and merging the
two old ones together which is unnecessarily high on processing power.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[TheDude5B]

thanks for that S. Justin

it all works now thanks and using _ & will come in very handy!!

I also found that i can use

"Select LAST_INSERT_ID()"
myCommand.ExecuteScalar

Thanks again

 

[Mark Rae]

I also found that i can use

"Select LAST_INSERT_ID()"
myCommand.ExecuteScalar

You might also have a look at SCOPE_IDENTITY in BOL in case that's more
appropriate for your needs...