Solved

Get Auto Increment Primary Key from row inserted into MySQL database using ADO

Posted on 2013-11-25
1
765 Views
Last Modified: 2013-12-09
I am trying to figure out how to determine the primary key, an Auto Increment field, of a row, after it is inserted into the database.  I know the standard answer to this is to use LAST_INSERT_ID(), but I believe my database design is causing this to not work for me. Let me explain a little bit.

Let's say I have two tables, calendar and event, that I would like to have as sub-types of a more generic table calendar_base.  The main reason for doing this is to simplify the relations with other tables in the DB.  So the calendar_base table generates the auto incremented primary key, which is used as a foreign key in the calendar and event tables.  In order to ease inserts into these tables, I have a before insert trigger on the calendar and event tables that inserts a row in the calendar_base table and set the foreign key in the calendar and event tables to LAST_INSERT_ID() .

So when I insert the rows into calendar and event tables, LAST_INSERT_ID() returns 0, presumably since if is within the trigger, which runs in a different "context" than my insert query, that the auto increment is generated and therefore my connection does not have a valid LAST_INSERT_ID().

Is there anything shy of a store procedure that will allow me to grab the ID that I need?  Am I missing a simple change that will let this work better?
0
Comment
Question by:SterlingMcClung
1 Comment
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 500 total points
ID: 39676704
Hello SterlingMcClung,

I am not master of triggers and stored procedures but check following.

did you check that your trigger works fine and you are getting row inserted in your calendar_base table with new incremented primary key for your row?

are you calling trigger before running a stored procedure?

one solution can be that you put your insert statement in your stored procedure that your are running, so all will be in same context and you can get last_insert_id

see if this helps you.

Thank you.
Amar Bardoliwala
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
A phishing scam that claims a recipient’s credit card details have been “suspended” is the latest trend in spoof emails.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

10 Experts available now in Live!

Get 1:1 Help Now