auto increment using mysql & ASP.net

Hello ALL,

Need HELP with my coding. I am new to ASP and MYSQL. :confused: I am trying to get the value of my auto increment ID so I could pass it on to my other tables. I tried to use Last_Insert_ID but with no luck. I am not sure if I used it right? Also I am not sure if it's necessary for me to open and close my connection everytime that I have to do a query??? datagrid??? Any help would greatly be appreciated.

here's what I have:

Dim ConnStr As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=db;uid=user;pwd=pword;option=3
Dim con As OdbcConnection = New OdbcConnection(ConnStr)
Dim SQL as String = "INSERT INTO Table1(field2,field3,field4,....) Values ('"+field2+"',.......)
Dim cmd As OdbcCommand = New OdbcCommand(SQL, con)
con.Open()
dgrTm.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
dgrTm.DataBind()

Dim SQL1 as String = "SELECT Last_Insert_ID() as mID from Table1"
Dim cmd1 as OdbcCommand = New OdbcCommand(SQL1, con)
con.Open()
dgrTm.DataSource = cmd1.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
dgrTm.DataBind()

Dim SQL2 as String = "INSERT INTO Table2(T2_ID, T2_Name, T2_Mid .....)
VALUES ('"+mID+"', '"+name+"', .......)

Dim cmd2 as OdbcCommand = New OdbcCommand(SQL2, con)
con.Open()
dgrTm.DataSource = cmd2.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
dgrTm.DataBind()

Dim SQL3 as String = "INSERT INTO Table3(T3_ID, T3_Email, T3_Phone)
VALUES ('"+mID+"', '"+email+"',.......)

Dim cmd3 as OdbcCommand = New OdbcCommand(SQL3, con)
con.Open()
dgrTm.DataSource = cmd3.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
dgrTm.DataBind()


Thanks! :)

You don't specify a table; just use SELECT LAST_INSERT_ID(); (the alias is ok, though). I don't know ASP, but you shouldn't have to close and reopen connections between queries (but be sure to call LAST_INSERT_ID() right after the INSERT query you want to check). There's probably an ASP function that will return the last id, too -- you may wish to use it instead.

Thanks for the reply. :) I've tried that already but It didn't work. I also tried using just one open() and Closed() statement but it's only taking my last query.

cant remember if I've ever used this on mysql but it works on sql server and it cant hurt ;)
"SELECT @@identity" returns the value of the identity field of the last record to be inserted.

On the db side, you should be fine. I'll have to defer to someone else to make sure your ASP's ok, though. You might ask a mod to move this to the ASP forum.

Thanks nancy and Kid charming. I finally got it to work by using executescalar :)

Dim ConnStr As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=db;uid=user;pwd=pwd;option=3"

Dim com As New OdbcCommand
com.Connection = New OdbcConnection(ConnStr)
com.CommandText = "INSERT INTO Table1( f1, f2, ...) Values(.....)
Dim memID As String
com.Connection.Open()
com.ExecuteNonQuery()
com.CommandText = "SELECT Last_Insert_ID()"
memID = CStr(com.ExecuteScalar())
com.Connection.Close()

........