pattoi
asked on
LAST_INSERT_ID() with OLEDB and ASP returns 0
I am trying to retrieve the auto increment ID of a record inserted into a MySQL table. I am using the MyOLEDB 3 driver with ADO in ASP (JScript). The INSERT statement is successful but a subsequent call to execute SELECT LAST_INSERT_ID() always returns 0. My code (simplified) is below:
var sSql="INSERT INTO Registration (RegistrationID, name, email) VALUES (NULL, 'Me', 'me@mail.com')";
oConn.Open(sConn);
oConn.Execute(sSql);
sSql="SELECT LAST_INSERT_ID()";
var iRegID=oConn.Execute(sSql)
var sSql="INSERT INTO Registration (RegistrationID, name, email) VALUES (NULL, 'Me', 'me@mail.com')";
oConn.Open(sConn);
oConn.Execute(sSql);
sSql="SELECT LAST_INSERT_ID()";
var iRegID=oConn.Execute(sSql)
peyox
https://www.experts-exchange.com/questions/20890178/last-insert-id.html
ASKER
I have alread looked at both of these articles today and neither of them addresses my problem. The Experts Exchange item is about a problem that the ID returned is from a different table whereas I am getting 0. I tried that solution anyway and still get 0. I also tried the solution from the 4 Guys from Rolla article but I get an error trying to execute the 2 commands separated by a semi-colon.
Try to run your script with this query:
var sSql="INSERT INTO Registration (name, email) VALUES ('Me', 'me@mail.com')";
var sSql="INSERT INTO Registration (name, email) VALUES ('Me', 'me@mail.com')";
ASKER
I have alread tried this also without any success
ASKER
I tried the same code using the MyODBC Driver instead and it works fine so obviously the problem is with MyOLEDB. That's enough of a solution for me now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.