Hi Jerry,

There is always the possibility of using a trigger on INSERT - would that solve your problem? I think that prior to MySQL 5.1 you need SUPER privileges to work with triggers, after that it's a separate permission. I see no reason why an INSERT trigger wouldn't pick up the correct value from the autoincremented column. Alternatively, you could put the trigger on UPDATE (on UPDATE SET b=autoidfield) and then simply run an UPDATE tbl SET autoidfield=autoidfield after your INSERT - that should still throw the trigger, and because the row's already been inserted, the autoincremented value will be set and retrievable.

Otherwise, what isn't working when you SELECT LAST_INSERT_ID() via MyODBC? I code a global function into all my VBA/MyODBC projects that takes a ADODB connection and returns the last insert id just by running that select. As long as you maintain the same connection object for the INSERT and the subsequent SELECT (or in your case UPDATE) then last_insert_id should be retrieved correctly.

If not, could you post back what you do get?

Hope that helps,

Al


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii....]
Sent: 21 January 2011 19:57
To: myodbc@list...
Subject: Retrieving last_insert_id

I have an auto_increment field in my table, but I need to set another field to the same value. (Please don’t ask why.)



It seems I would need to insert the record, and then update it; but I haven’t figured out how to get the value of last_insert_id().



Any ideas?



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail:  <mailto:jerry@gii....> jerry@gii....

Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=subscriber@open...