• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

mysql_insert_id equivalent in vb6

Same question as here:


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.
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Hi  DrDamnit

Use the following Sql expresion to return the last inserted ID of a connection:

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
DrDamnitAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might try to use the following line to see if that is indeed the problem:
    Dim rs As ADODB.Recordset

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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
dancebert: we are here on MySQL, not on MSSQL, hence, @@identity and scope_identity are not applicable...
@@identity is applicable on MySql
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, I never saw @@identity in the documentation yet, can you show me the relevant chapter?
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.


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).

Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, I had searched the online manuals, but didn't find that. thanks for that update!
This is the Url to the said page:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
No problem, and your welcome
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now