The Underscore Archives

Hi Jason,

Monday, May 19, 2008, 6:12:01 PM, you wrote:

> I'm using tableadapters to insert a row into a MySQL database. I
> want to get the primary key of the inserted row returned. Normally
> I'd tag a SELECT SCOPE_IDENTITY() statement onto the end of my
> insert and make the command a scalar but when I use the equivalent
> MySQL command (SELECT LAST_INSERT_ID()) I get a syntax error. Does anybody have a working example?
> Here is my SQL statement;
> INSERT INTO tblaccess
>                       (siteid, floor, room, area, reason, accessed, accessdate, comment)
> VALUES     (?, ?, ?, ?, ?, ?, ?, ?);
> SELECT LAST_INSERT_ID() AS ID;
> And I get error "Unable to parse query text"

You need to run it as two queries, because whatever MySQL
implementation you're using doesn't allow multiple queries. In PHP you
can use mysqli_multi_query() and the use/store result and more/next
functions to advance through the result sets. However even that won't
work with a prepared statement, because prepared statements can only
have 1 query.

So either move this into a stored procedure with in/out parameters,
multi query it, or run one query after the other. I'd go for the SP
personally.

Cheers,

Rich
-- 
PHP: http://www.corephp.co.uk / Zend Certified Engineer
AS3: http://www.photonstorm.com / Full Gamer Alchemist

"Never trust a computer you can't throw out of a window"