• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4064
  • Last Modified:

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
0
Prysson
Asked:
Prysson
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you try this:
INSERT INTO tblapplicationtype
                      (ApplicationType, Active)
VALUES     (@ApplicationType, @Active);
Select last_insert_id();

Open in new window

0
 
PryssonAuthor Commented:
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  ;


0
 
PryssonAuthor Commented:
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.

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now