asked on

Scope Identity with MySQL table adapter

I am having an issue witha table adapter and the scope identity when using a MySQL database.

If I was creating an Insert statement in a tableadapter to a MSSQL I woudl simply do a
Select Scope_Identity()...so that in teh TableAdapater the insert reads

INSERT INTO [tblApplicationType] ([ApplicationType], [Active]) VALUES (@ApplicationType, @Active);
SELECT SCOPE_IDENTITY()

with a Scalar execution mode and the Insert woudl return the value of teh primary key for that new inserted row...

However adding the

Select Scope_Identity()

to an insert statement when the table adapater is poitning to a MySQL table returns an error about incorrect syntax.

I need to return the value of the inserted row and capture it..Can someone tell me the correct syntax I need?

I have tried

INSERT INTO tblapplicationtype
(ApplicationType, Active)
VALUES (@ApplicationType, @Active)
Select last_insert_id()

But that isnt working...returns and error trying to parse the query.

I am using the MySQL connector 5.2.2

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Prysson

ASKER

I could have deleted this question since I found the answer..but I want to add it to my knowledge base AND I want the answer to be available should anyone else have the same questions.

The answer is this.
INSERT INTO tblapplicationtype
(ApplicationType, Active)
VALUES (@ApplicationType, @Active); SELECT last_insert_id()

So Basically...just as with the MSSQL database you have to set the ExecutionMode to Scalar

Then add ; SELECT last_insert_id() to the end of the insert statement. Mine was failing specifically because I wasnt puttin in the ;

Prysson

ASKER

He He...you answered just as I was writing the solution I found..Ill give you the credit..the answer you provided is the right one.