get last record id using mySQL

GregH · July 15th, 2004, 06:52 AM

Hi all,

I'm using MySQL with ASP and am trying to retrieve a new record ID immediately after insertion so I can work with it uniquely.

No matter what I do, I always return a blank when trying to use either @@IDENTITY or LAST_INSERT_ID().

Could someone please advise the correct query structure I should be using?

This is my connection method which works OK and correctly inserts a new record.

Dim RS
set RS= Server.CreateObject("ADODB.Command")
RS.ActiveConnection = "Driver={MySQL}; DATABASE=myData; UID=myUser; PASSWORD=myPassword;"
RS.CommandText = "INSERT INTO table_name (field_name) VALUES ('field_value')"
RS.Execute

all advice gratefully received :)

thanks

Greg

happygv · July 15th, 2004, 08:04 AM

Hi Greg,

Check out this page.

How to Get the Unique ID for the Last Inserted Row

IMO, you should do that all in one transaction. May be write a procedure in mysql that takes all the parameters for Insert and returns the Unique Id. When you do that with 2 recordsets as 2 transactions, you won't be getting the value at all, as it(insert and getting Unique ID) has to be done as a single transaction, which I think you are missing there.

Or construct the SQL string as given below

Code:

strsql = "insert into TABLE_NAME (COL1, Col2) values('You','Me');
strsql = strsql & "select last_insert_id() as NewUniqueId;"
RS.CommandText = strsql
RS.Execute
Response.write RS("NewUniqueId");

Something like this should help you getting that out.

Else you will have to write a procedure that inserts values into table and returns the UniqueId back.

--Create you procedure in MYSQL

Code:

CREATE PROCEDURE InsertVal (IN param1 INT, IN param2 VARCHAR(30))
BEGIN
    Insert into TABLENAME(Col1, Col2) Values(param1,param2);
    SELECT last_insert_id() as NewUniqueId;
END

'In ASP, your code should be like

Code:

strsql = "Call InsertVal(10,'yourname');"
RS.CommandText = strsql
RS.Execute
Response.write RS("NewUniqueId");

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

GregH · July 15th, 2004, 09:38 AM

Hi happygv, thanks for the quick response.

Using your first suggestion, I get an error when trying to concat both 'strsql' strings together.

I'm using mySQL 4.0 (win32) locally and mySQL 3.23.56 (linux/chilisoft) on the web server.

I get errors at the same place on both, at the ';' separator in the CommandText line.

Are these versions compatible with this method?

thanks

Greg

happygv · July 15th, 2004, 10:01 AM

Just noticed, there was a " missing in my previous post.

strsql = "insert into TABLE_NAME (COL1, Col2) values('You','Me');"

If you have corrected that at your end and still you get that error, probably you can try removing those ;s from there and see if that works.

Cheers!

_________________________
- Vijay G
Strive for Perfection

GregH · July 15th, 2004, 12:46 PM

Hi,

I did spot the missing " but it makes no difference.

It just doesn't seem to like the INSERT... SELECT combination.

I had a look here

http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html

and tried various key options but I'm still rather stuck, whatever I try just returns 0 or blank or an error message.

Ho hum

Greg

happygv · July 17th, 2004, 11:38 PM

That INSERT... SELECT combination is to populate the table with values from another table. That is not the one going to help you in this. So you don't have to worry about that link you posted.

Have you tried the second option, doing it with the help of creating stored procedure?

Cheers!

_________________________
- Vijay G
Strive for Perfection

rmaccue · May 11th, 2008, 08:29 PM

Does anyone have any feedback, keep getting 'type mismatch' error...Trying to get the auto-increment value. Any help would be great!!

Code:

SQL_RunNum="SELECT LAST_INSERT_ID()"
dbconn.execute(SQL_RunNum)
response.write(SQL_RunNum(0))