Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

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

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
andryuha
Asked:
andryuha
  • 7
  • 5
  • 2
  • +3
1 Solution
 
bvinsonCommented:
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
 
apolloisCommented:
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
 
andryuhaAuthor Commented:
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
Independent Software Vendors: 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!

 
apolloisCommented:
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
 
jarwCommented:
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
 
andryuhaAuthor Commented:
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
 
apolloisCommented:
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
 
andryuhaAuthor Commented:
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
 
apolloisCommented:
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
 
andryuhaAuthor Commented:
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
 
bvinsonCommented:
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
 
WakieCommented:
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
 
apolloisCommented:
Wakie,

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

>apollois<
0
 
WakieCommented:
oh, *last inserted record*.

I read it as *last record*.

My mistake :)

Wakie.
0
 
andryuhaAuthor Commented:
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
 
apolloisCommented:
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
 
apolloisCommented:
========================================
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
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now