[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql_insert_id equivalent in vb6

Posted on 2006-05-24
14
Medium Priority
?
925 Views
Last Modified: 2008-01-09
Same question as here:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21446881.html

I need example code of how to use last_insert_id or whatever VB6 has as an equivalent of mysql_insert_id(). The PAQ above has a link to some stuff, but I cannot find the example code.
0
Comment
Question by:DrDamnit
14 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16756162
I assume you have a connection to the mysql database?

then simply run the following query:
dim rs as recordset
set rs = yourconnection.execute("select last_insert_id() as r ")
debug.print rs.fields("r").value
0
 
LVL 13

Expert Comment

by:iHadi
ID: 16756632
Hi  DrDamnit

Use the following Sql expresion to return the last inserted ID of a connection:
SqlStr = "SELECT @@IDENTITY AS Last_ID"

This retuns a single row, single column table which has the ID Value. If no inserts has been done it returns a recordset with no rows or columns (an empty recordset):
LastID = Recordset.Fields("Last_ID").Value
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 16756965
Both of those are giving me a type mismatch error:

Public Function vb6_insert_id()
    Dim rs As Recordset
    Set rs = cn.Execute("SELECT @@IDENTITY AS Last_ID")
'    Set rs = cn.Execute("SELECT last_insert_id() as r")
    vb6_insert_id = rs.Fields("Last_ID").Value
End Function
0
Technology Partners: 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16757857
what is CN of data type?

note that, when both ADO and DAO are in the project references, DAO takes precendence.
hence, if the connection is ADO, your code will actually be like:

    Dim rs As DAO.Recordset

while cn.execute() returns a ADODB.Recordset which will then issue the type mismatch error.

Now, you might want to tell us on which line the error occurs, and specify how you initiate the connection object
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16757862
PS:
you might try to use the following line to see if that is indeed the problem:
    Dim rs As ADODB.Recordset
0
 
LVL 9

Expert Comment

by:dancebert
ID: 16765032
>SqlStr = "SELECT @@IDENTITY AS Last_ID"

Most of the time this is true.  If the table you're making an insert to has a trigger that inserts a record to another table, it's not true and you need to use SCOPE_IDENTITY() to get the correct value.  The following three paragraphs are from the Sql Server Books Online for SCOPE_IDENTITY:

---------- begin quote --------
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
---------- end quote --------

In this situation @@IDENTITY returns the last identity value generated for T2. SCOPE_IDENTITY returns the last identity value for T1.




0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16766404
dancebert: we are here on MySQL, not on MSSQL, hence, @@identity and scope_identity are not applicable...
0
 
LVL 13

Expert Comment

by:iHadi
ID: 16767823
angelIII
@@identity is applicable on MySql
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16767876
well, I never saw @@identity in the documentation yet, can you show me the relevant chapter?
0
 
LVL 13

Expert Comment

by:iHadi
ID: 16774846
Well you could have done a little search on the online manual of MySql where you would have found that this variable has been around since 2001 (MySql 4.0 Alpha)

And using @@identity has an advantage that makes your application compatable with other DB systems (MSSQL, Sybase ...) and if its done right you wouldn't need to modify anything in the code other than the connection string to change the DB system your application is dealing with.

FROM MYSQL SITE MANUAL:

D.2.28. Changes in release 4.0.0 (October 2001: Alpha)
-> Added the IDENTITY variable as a synonym for the LAST_INSERT_ID variable (like Sybase).

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16775195
iHadi:
well, I had searched the online manuals, but didn't find that. thanks for that update!
0
 
LVL 13

Expert Comment

by:iHadi
ID: 16776348
This is the Url to the said page:
http://dev.mysql.com/doc/refman/4.1/en/news-4-0-0.html
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16776621
Thanks! I think I wrote my sentence wrong: "I didn't find that"
I wanted to say: "I had not found that previously". When you had posted that IDENTITY is indeed available, I could find it indeed.
0
 
LVL 13

Expert Comment

by:iHadi
ID: 16777392
No problem, and your welcome
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 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