?
Solved

getting id of a record just inserted from MySQL in ASP? How?

Posted on 2003-02-28
18
Medium Priority
?
563 Views
Last Modified: 2007-12-19
Hello Experts!

I'm using ASP and MySQL for a db.

I'm inserting a record and need to know what the id of the last inserted record was.

I looked up MySQL documentation and supposedly "SELECT LAST_INSERT_ID()" takes care of that, it does work in actual MySQL.

But it doesn't want to work with DNS-less connection, here is the code, followed by the error.

set rs=conn.execute("SELECT LAST_INSERT_ID()")
session("id")=rs(0)

looks fine but i'm getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80020009'
[Microsoft][ODBC Driver Manager] Driver does not support this parameter


Please, help me out.

Thanks
0
Comment
Question by:andryuha
[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
  • 7
  • 5
  • 2
  • +3
18 Comments
 
LVL 6

Expert Comment

by:bvinson
ID: 8044379
This is an issue that has been address so many times, because at one time or another, everyone has to face it.  :)

From what I understand, the general advice is not to use auto-increment fields to assign IDs and to create unique number on the fly and insert that with the rest of the record...how do you create a unique number?  That is as varied as the number of people who have tried to combat this issue...the one I have seen and liked the most is taking the date and adding a randomly generated number to it, that way if two people update the db at the same time on the same date, it will still be unique.

Why not just take another dip in to the database to see what the last record was?  Well, if there is more than one user, the possibility exists that the other person will add a record before you make your query...in which case you get a bad result (not to mention the added overhead to the server)...and you should make your app scaleable even if you never expect it to be used beyond a specific purpose (that's how we got the who Y2K issue).

I hope this helps, and good luck with your project.
bvinson
0
 
LVL 10

Expert Comment

by:apollois
ID: 8044423
andryuha,

Here is an approach that works for SQL Server and Access.  Don't know about MySQL.  If MySQL supports the "SELECT @@IDENTITY" statement, then it probably will.

BTW, I disagree with bvinson about use of autonumber/identity.  A large number of experienced SQL developers make good use of this.  I really don't know of any down-side to using it.  I recommend it.

Best Regards,
apollois

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
GET THE PRIMARY KEY OF A RECORD JUST ADDED
FOR SQL SERVER AND MS ACCESS 2000 and Later
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REF: http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

This brief article was written by
apollois
http://www.experts-exchange.com/memberProfile.jsp?mbr=apollois
Experts-Exchange ASP Expert
Last Revision: Dec 16, 2002

Here is one of the better approaches for getting the value of the Primary Key field of a record just added.  This assumes that the PK field is "autonumber" for Access or "IDENTITY" for SQL Server.  

For other approaches, see:
http://www.kamath.com/tutorials/tut007_identity.asp

For an approach to use with Access97, see
Get PK of Just Added Record - Access97.txt
=============================================================
'...
DIM cnnSomeDB
DIM strSQL
DIM rstPK
DIM intPK_of_new_record

'--- OPEN CONNECTION TO DB ---

' This assumes a Microsoft Access DB.  For connection strings to
' other databases, see:
'     http://asp101.com/articles/john/connstring/default.asp
'     http://www.connectionstrings.com/index.asp1


SET cnnSomeDB = Server.CreateObject("ADODB.Connection")
cnnSomeDB.Open "Provider=MICROSOFT.JET.OLEDB.4.0; " _
    & "DATA SOURCE=" & Server.MapPath("somedir\somedb.mdb")

'--- BUILD SQL STRING ---
strSQL = "INSERT INTO someTable (fld1, fld2) VALUES(123,'xyz')"

'--- EXECUTE THE INSERT ---
cnnSomeDB.Execute strSQL

'--- GET THE PRIMARY KEY VALUE OF THE JUST INSERTED RECORD ---
SET rstPK = cnnSomeDB.Execute( "SELECT @@IDENTITY" )

intPK_of_new_record = rstPK(0)     '<===== THIS IS IT

'--- CLOSE AND RELEASE OBJECTS NO LONGER NEEDED ---
rstPK.Close
cnnSomeDB.close
SET rstPK = nothing
SET cnnSomeDB = nothing
...
===================================================================
0
 

Author Comment

by:andryuha
ID: 8044642
bvinson - thanks for your opinion, but in this case getting the id of a JUST inserted record is a proven method. Meaning that insert and getting id are one after another.

appolois - thank you as well, but I did exactly the same thing when I worked with Access and SQL Server.

I have to work with MySQL in this case and the ODBC is giving errors. The statement works in MySql db, its just ODBC drivers aren't cooperating.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:apollois
ID: 8044782
Try this:

======================================================
DIM rstID
DIM strSQL
DIM intID

rstID = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT LAST_INSERT_ID()"
rstID.Open strSQL,conn

IF NOT rstID.EOF THEN
     intID = rstID(0)
ELSE
     Response.write "<BR> The following SQL did not return any records:" _
               & "<BR>" & strSQL & "<BR>"
END IF
=====================================================

If you get an error, please post:
     exact error message
     line# of error
     corresponding line of code

Best Regards,
apollois

0
 
LVL 3

Expert Comment

by:jarw
ID: 8045700
I always use autonumber fields, but I haven't used mySQL. In my experience with MS SQL Server and MS Access, the OLE DB drivers work better than the ODBC ones for this problem. (Does that exist for mySQL?)

Anyway, my code would usually be:

...

' This could be a select statement too, i think...
rs.Open "table_name", conn, adOpenKeyset, adLockOptimistic, adCmdTable
           
rs.AddNew()

rs("col1") = val1
rs("col2") = val2
rs("col3") = val3

....blah, blah, blah....

rs.Update()

' Get the id of this record in the database. The name of
' the database column is "id".
recordID = rs("id")

...

This is very easy in SQL Server and Access and no worries about simultaneous updates with other users.

Hope that helps.
0
 

Author Comment

by:andryuha
ID: 8045746
appolois - it turns out that "SELECT LAST_INSERT_ID()" didn't return anything, I tried the snippet you posted here and i tried using Recorset in my code. Same result, recordset is EOF.  

Now why is that? If I do Select LAST_INSERT_ID() in MySql then it does return last record, but not to through ADO? I don't get it.

Please, any ideas regarding this are welcome
0
 
LVL 10

Expert Comment

by:apollois
ID: 8045784
This is beginning to sound like a driver problem.

Have you checked for ODBC driver updates?
I'd check at MS and at MySQL.

Even if you have the latest, somtimes files can become corrupt.  So you might even try a reinstall.

Oh, you are executing the Select LAST_INSERT_ID() SQL IMMEDIATELY after the INSERT, right?

>apollois<
0
 

Author Comment

by:andryuha
ID: 8045798
appolois - it turns out that "SELECT LAST_INSERT_ID()" didn't return anything, I tried the snippet you posted here and i tried using Recorset in my code. Same result, recordset is EOF.  

Now why is that? If I do Select LAST_INSERT_ID() in MySql then it does return last record, but not to through ADO? I don't get it.

Please, any ideas regarding this are welcome
0
 
LVL 10

Expert Comment

by:apollois
ID: 8045805
Please do not use the browser's refresh/reload button.  Due to a bug in EE, it will cause a duplicate post.

To refresh the question, click the link "Reload this question" in the upper left corner of this page.

Thanks.
0
 

Author Comment

by:andryuha
ID: 8045809
yes, I use "last insert id()" immediately after insert statement, same way i used "SELECT @@IDENTITY" with Access/SQL Server

The website is hosted somewhere, so driver could very well may be the problem. I'll have to check with the host.
0
 
LVL 6

Expert Comment

by:bvinson
ID: 8046169
Sorry guys...didn't mean to give the wrong impression...but I use autoincrement in almost every database I have... Its just that I have seen A LOT of posts saying not to use it if you need to retrieve the value on submit...  posts here and on the Internet in general.  I can see the benefits of creating your own unique ID as opposed to requerying the db, but by all means, do what you need to do to get the results you need.

<no response to this response necessary>

bvinson
0
 
LVL 4

Expert Comment

by:Wakie
ID: 8046593
Hello andryuha, try this:

<%
'Database connection here

'Query database
SQL = "SELECT * Table"
Set RS = DB.Execute(SQL)

'Loop thru recordsets
Do Until RS.EOF
   LastRecordset = RS("anyColumn")
   RS.MoveNext
Loop

Response.Write "Last recordset: " & LastRecordset
%>

Regards,
Wakie.
0
 
LVL 10

Expert Comment

by:apollois
ID: 8046643
Wakie,

Surely you're not serious about using this as a method for getting the ID of the newly inserted record?

>apollois<
0
 
LVL 4

Expert Comment

by:Wakie
ID: 8047194
oh, *last inserted record*.

I read it as *last record*.

My mistake :)

Wakie.
0
 

Author Comment

by:andryuha
ID: 8048391
ok, guys thank you for all the help, but I finally found the issue at hand.

Check this page:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20127781.html

basically, the connection option should have been set to:
OPTION=16384

That fixed the problem, not only could you not "Last_Insert_ID()" but "Count(*) as well.

But setting the option to 13384 fixed the problem! I was really close to giving up.
0
 
LVL 10

Expert Comment

by:apollois
ID: 8126203
Hi andryuha,

Since you solved the problem yourself, you can close this question and obtain a refund of your points by posting a zero-point question with Community Support (http://www.experts-exchange.com/Community_Support/).  Include a link to this question and your request.

Good luck on your project.

Best Regards,
>apollois<
0
 
LVL 10

Expert Comment

by:apollois
ID: 8208585
========================================
ABANDONED QUESTION FINAL NOTICE
========================================

It appears that this question has been abandoned:
     -- Days since the question was opened:          25
     -- Last post of the question asker:          03/01/2003

I will make a final recommendation to the EE Moderators on its resolution in one week. I appreciate any comments that would help me to make a recommendation.  If you disagree with my initial recommendation, I urge you to post a comment.

In the absence of responses, I will make the below recommentation.

RECOMMENDATION:
      [SAVE AS PAQ AND REFUND POINTS]

Silence = You agree with recommendation or don't care.

DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

>apollois<
EE Cleanup Volunteer
~~~~~~~~~~~~~~~~~~~~~~~~
andryuha, if you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, you may post a request in Community support (with a link to this page) to refund your points. http://www.experts-exchange.com/Community_Support/
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8358898
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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