Solved

LAST_INSERT_ID() with OLEDB and ASP returns 0

Posted on 2004-09-19
8
763 Views
Last Modified: 2008-02-01
I am trying to retrieve the auto increment ID of a record inserted into a MySQL table.  I am using the MyOLEDB 3 driver with ADO in ASP (JScript).  The INSERT statement is successful but a subsequent call to execute SELECT LAST_INSERT_ID() always returns 0.  My code (simplified) is below:

var sSql="INSERT INTO Registration (RegistrationID, name, email) VALUES (NULL, 'Me', 'me@mail.com')";
oConn.Open(sConn);
oConn.Execute(sSql);
sSql="SELECT LAST_INSERT_ID()";
var iRegID=oConn.Execute(sSql).Fields(0).Value;

0
Comment
Question by:pattoi
8 Comments
 
LVL 6

Expert Comment

by:peyox
ID: 12099191
0
 
LVL 26

Expert Comment

by:ushastry
ID: 12099194
0
 

Author Comment

by:pattoi
ID: 12099359
I have alread looked at both of these articles today and neither of them addresses my problem.  The Experts Exchange item is about a problem that the ID returned is from a different table whereas I am getting 0.  I tried that solution anyway and still get 0.  I also tried the solution from the 4 Guys from Rolla article but I get an error trying to execute the 2 commands separated by a semi-colon.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 6

Expert Comment

by:peyox
ID: 12099397
Try to run your script with this query:
var sSql="INSERT INTO Registration (name, email) VALUES ('Me', 'me@mail.com')";
0
 

Author Comment

by:pattoi
ID: 12099409
I have alread tried this also without any success
0
 

Author Comment

by:pattoi
ID: 12119959
I tried the same code using the MyODBC Driver instead and it works fine so obviously the problem is with MyOLEDB.  That's enough of a solution for me now.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 13738752
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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