adding rows then grab id 1

[derwent]

I add rows to mysql DB using and try to grab ID straight back out (ID is an auto increment, don`t try to add it in code below) however the id is coming out blank on the page. The item is entered into the DB no probs and creating the unique id.

SQL = "SELECT * FROM delreg"
set rs = server.CreateObject("ADODB.RECORDSET")
rs.open SQL,objConn, 3,3

rs.AddNew
rs("item1") = request.form("item2")
etc etc etc

bid = rs("id")
rs.close

response.write bid

Does anyone know where |I am going wrong to get the ID straight back out?

Thanks

 

[johnwm]

You need the Last_Insert_ID

Have a look at:

http://dev.mysql.com/doc/refman/5.0/en/odbc-and-last-insert-id.html

or:

http://dev.mysql.com/doc/refman/4.1/en/odbc-and-last-insert-id.html

for older versions

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[derwent]

So I would use

SELECT LAST_INSERT_ID(id) from delreg

?

 

[johnwm]

I don't think you should pass a parameter. On the end of your insert query just add:
SELECT LAST_INSERT_ID() from delreg

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[litton1]

select max(last_insert_id) from delreg may also work

Age is a consequence of experience

 

[derwent]

I have this

SQL = "SELECT * FROM delreg; select last_insert_id() as bid;"

but I`m getting the error "ODBC driver does not support the requested properties.

 

[guelphdad]

select max(last_insert_id)

does not work. the last_insert_id is returned for the last auto incremented id you used for your session. you don't need the max.

also using
select max(nameofautoincrementcolumn)
is not good practice either as you could end up selecting info after someone else has made an entry.

 

[johnwm]

Did you follow the references I gave earlier? They give detailed examples of usage. The Select_Last_ID should be called immediately after the relevant Insert

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[derwent]

Thanks johnwm, I used the example to come up with the sql SELECT * FROM delreg; select last_insert_id() as bid; which brought the error "ODBC driver does not support the requested properties."

My connectrion is

Driver={MySQL ODBC 3.51 Driver};SERVER=myserver;DATABASE=mydb;USER=me;PASSWORD=mypass;OPTION=3;

 

[johnwm]

Did you try using the 'select last_insert_id() as bid' immediately after the INSERT statement per the reference and as per my previous post? You seem to be trying to use it after a SELECT statement

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[derwent]

I`m not using insert I am using addnew. This is because there are lots of fields to be inserted and find the addnew method much easier to see.

 

[guelphdad]

are you using mysql? what is addnew? is it something from a front end? the only way to get data into your database is through an import from a file or from an INSERT. irrespective if you have a tool called addnew, you still call the last_insert_id() at that point.

 

[litton1]

does not work. the last_insert_id is returned for the last auto incremented id you used for your session. you don't need the max.

o right sry, i thought the max function would return the highest number?

Age is a consequence of experience

 

[ingresman]

Addnew is an ADO method on a recrodset object. I recall you have to do a select first to "prime" the column names. Some old documentation I have says if you just issue the addnew without field and values as parameters(which this code is doing , see the etc etc bit) you have to call the update method to actualy do the insert. This seems fair enough as ADO wont have a clue when your finshed doing the col=value calls. The rs.close is probabbly doing the same thing but I would do the select on last_insert_id just after the call to update. Something like.

rs.AddNew
rs("item1") = request.form("item2")
etc etc etc
rs.update
SQL2 = "SELECT last_insert_id() as lid from delreg"
set rs2 = server.CreateObject("ADODB.RECORDSET")
rs2.open SQL2,objConn, 3,3

or something close !

 

[derwent]

haha, thanks ingresman.

I had just done as you suggested and tested it, all worked fine. As soon as my test worked, I received the tek tips email saying you had responded. Great minds eh??