Solved

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

Posted on 2013-11-25
1
770 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
A phishing scam that claims a recipient’s credit card details have been “suspended” is the latest trend in spoof emails.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

740 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