Solved

Scope Identity with MySQL table adapter

Posted on 2008-06-12
3
3,995 Views
Last Modified: 2013-12-17
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
Comment
Question by:Prysson
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21770079
can you try this:
INSERT INTO tblapplicationtype
                      (ApplicationType, Active)
VALUES     (@ApplicationType, @Active);
Select last_insert_id();

Open in new window

0
 

Author Comment

by:Prysson
ID: 21770161
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
 

Author Comment

by:Prysson
ID: 21770174
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now