0

I have this code:

With shtControleblad
    Dim strsql_basis As String
        strsql_basis = "INSERT INTO is_calculatie (offerte_id) VALUES ('" & Sheets("controleblad").Range("D1").Value & "')"

        rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic

        Dim last_id As String
        last_id = "select last_insert_id()"
End With

The string last_id is not filled. What is wrong? I need to find te last_insert_id so I can use it in an other query.

Muiter
  • 41
  • 1
  • 1
  • 4

2 Answers2

1

you have to add rs.movelast after you open the recordset, that should help

Whakkee
  • 1,867
  • 1
  • 16
  • 18
  • How would that apply to my situation? And what about when more people are using the db? – Muiter Mar 21 '10 at 19:35
  • 1
    What is it you're really trying to do? You'd like to select the last inserted id, based on an insert statement? Maybe you should run a select statement first, then move to the last record, read from it, and then with that data, do the insert statement. – Whakkee Mar 21 '10 at 20:56
1
last_id = "select last_insert_id()"

You have set the sql statement to be executed, but have not executed it.
Call rs.Open with the above statement to get the 'last_insert_id` instead.

If mysql supports multiple sql statements on single line, you could do 

strsql_basis = "INSERT INTO is_calculatie (offerte_id)  
VALUES ('" & Sheets("controleblad").Range("D1").Value & "')
; select last_insert_id()"

rs.Open strsql_basis, oConn, adOpenDynamic, adLockOptimistic
shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
  • It does not support multiple statements. I have tried Dim last_id As String last_id = "select last_insert_id()" rs.Open last_id, oConn, adOpenDynamic, adLockOptimistic But not the result wanted. – Muiter Mar 21 '10 at 19:46