update record and get return ID

[unseth]

I'm using mysql currently, and i want to lock a record, update it, and get the current id.

For example, I have a table with a field called nextmsg (int). On my sql statement, i want to update the nextmsg + 1, and get the new value.

I was doing the following:

   set rslck = Server.CreateObject("ADODB.Recordset")
   rslck.LockType = 2
   s = "select nextmsg FROM forum_index WHERE forumid = 3"
   rslck.Open s, myconn
   rslck("nextmsg") = rslck("nextmsg") + 1
   midd = rslck("nextmsg")
   rslck.update
   rslck.close
   set rslck = nothing

midd is the correct value, however that is updating every record in forum_index to the nextmsg + 1, not just where forumid = 3

i have to use locking because this asp file is called frequently.

mysql handles things a bit differently then sql...
any thoughts?

 

[pgferro]

I would try it like this

set rslck = Server.CreateObject("ADODB.Recordset")
   rslck.LockType = 2
   s = "select nextmsg FROM forum_index WHERE forumid = 3"
   rslck.Open s, myconn
   midd = rslck("nextmsg")+1
   s = "update forum_index set nextmsg="& midd &" where forumid=3"
   rslck.Open s, myconn
   rslck.close
   set rslck = nothing

let me know...