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" <ti*******@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...
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("MySqlConnection Str")
Dim myConnection As New MySqlConnection(strConn)
myConnection.Open()
Dim myCommand As New MySqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = mySqlQuery
myCommand.ExecuteNonQuery()
myConnection.Close()
