PDA

View Full Version : auto increment using mysql & ASP.net



needhelpplz
Sep 19th, 2005, 06:51 AM
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! :)

Kid Charming
Sep 19th, 2005, 07:20 AM
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.

needhelpplz
Sep 19th, 2005, 09:29 AM
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.

NancyJ
Sep 19th, 2005, 01:31 PM
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.

Kid Charming
Sep 19th, 2005, 05:23 PM
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.

needhelpplz
Sep 21st, 2005, 05:50 AM
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()

........