ASP MySql Insert-Retrieve Problem

[struth]

I can't seem to get my insert-retrieve last ID working since migrating from Access to MySql

After the insert routine I used to just:

rs.update
session(""ident") = rs("ID")

and there it was...

I have tried some of the MySql routines eg

MySql_insert_ID()

but nothing.

tia
Struth



"Away from the actual ... everything is virtual"

 

[mjonson]

Hi am having same prob thread333-882677
will let you know if i crack it

 

[vongrunt]

Have you tried mysql last_insert_id() ?

 

[mjonson]

vongrunt
how do i implement it
have tried
tmpid = mysql_insert_id()

but get error
Type mismatch: 'mysql_insert_id'
/dfasp/custadddeal.asp, line 136

 

[vongrunt]

This is mysql function, not ASP function. Use something like:

'conn is connection object, rs is recordset
Set rs = conn.Execute( "select mysql_insert_id()" )
last_id = rs.Fields(0).value

 

[struth]

Thanks for your input. unfortunately Mysql_insert_id is not supported by mysql's ODBC driver.

This is the best work around I could come up with but I would think there is a possibility in a heavy traffic site that the last_insert_id could possibly end up NOT being the actual one you've just inserted. But for me this solution should work fine - for the moment.

If you come up with a better one, please let me know.

<% 


  cat  = Request.form("Cat")
  desc = Request.form("Desc")
  'connect to MySQL server using MySQL ODBC 3.51 Driver
Set objConn = Server.CreateObject("ADODB.Connection")
' 
objConn.open = "CONNECTION STRING HERE"

Set rs = Server.CreateObject("ADODB.Recordset")
' Create the SQL for our insert statement.
sql = "INSERT INTO myTable " & _ 
       			"( thisID, thisCategory, thisDesc )" & _ 
	   " VALUES " & _ 
	          "(NULL,'"&cat&"','"&desc&"')"       
' then the insert.
set rs = objConn.execute( sql ) 

'now get the id BUT I'd be aware this is probably not perfect for a multi-transaction application as it is built on similar lines to that I'd use for MAX(id) routine but if I try to tack this onto the the first sql it just gives an error.

sql_ID = "select LAST_INSERT_ID() As ident"

set rs = objConn.execute( sql_ID ) 

' Put the ID into a variable or session
session("ident") = rs("ident")
 
' Clean-up objects
rs.close
set rs = nothing 
objConn.close
set objConn = nothing 




%>

"Away from the actual ... everything is virtual"

 

[struth]

I have just seen the solution to mjonson's thread Thread333-882677. This seems to remove the need for a session so this might be the way to go....

"Away from the actual ... everything is virtual"

 

[vongrunt]

Assuming that cusID is auto_increment primary key, I don't see any difference. Retrieve generated ID immediately after INSERT and store it somewhere else. Btw. last_insert_id() is limited to connection scope - see

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

 

[struth]

Thanks vongrunt. Checked out the link and discovered what you meant by 'connection scope' ...

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client.

Very handy. MySql - the more I use it the more I love it.


"Away from the actual ... everything is virtual"