Mysql stored procedure returns LAST_INSERT_ID as zero

Question

I have a problem with return output parameter from mysql stored procedure to VBA macro. I wrote this mysql stored procedure InsertProduct to insert new product into database:


DELIMITER $$
CREATE PROCEDURE `InsertProduct`(IN `p_modelno` VARCHAR(40), IN `p_name` VARCHAR(120), OUT `p_last_id` BIGINT)
BEGIN
    INSERT INTO product (product_id, name, model_no) VALUES (null, p_name, p_modelno);
    SET p_last_id =  LAST_INSERT_ID(); 
END$$
DELIMITER ;

When I test this procedure in phpmyadmin, it returns Last_Insert_ID correctly (for example - new record got the ID 4454, it display p_last_id=4454), see this image: phpmyadmin testing procedure returns correct ID

Then I have an userform in excel with two text boxes (txtModelNo, txtName) as a front-end to save new product into database. I've written VBA macro to save the product into database and then I want to dipslay msgbox with this last insert record ID. Here is the problem, p_last_id in macro always returns 0 ...

Code in VBA for running this stored procedure and use this output parameter:



Sub InsertProduct()

  
    Dim cnSqlConn As New ADODB.Connection
    cnSqlConn.Connectionstring = "DSN=XXX"
    cnSqlConn.Open
    
    Dim cmd As New ADODB.Command
    
    With cmd
        .ActiveConnection = cnSqlConn
        .CommandType = adCmdStoredProc
        .CommandText = "InsertProduct"
        .CommandTimeout = 15
    End With
    

    cmd.Parameters.Append cmd.CreateParameter("p_modelno", adVarChar, adParamInput, 40, Me.txtModelNo) 
    cmd.Parameters.Append cmd.CreateParameter("p_name", adVarChar, adParamInput, 120, Me.txtName)
    cmd.Parameters.Append cmd.CreateParameter("p_last_id", adInteger, adParamOutput, 11)
    cmd.Execute lngResult

     Msgbox cmd.Parameters("p_last_id") /****** HERE COMES THE PROBLEM - IT ALWAYS RETURNS 0  !! *******/

    Set cmd = Nothing
    cnSqlConn.Close
    Set cnSqlConn = Nothing

End Sub()

I've read many articles about this topic, but nothing helps. Thanks in advance for any advice.

odbc doesn't support oUt parameters see http://www.artfulsoftware.com/infotree/tip.php?id=130 use a `sELECT LAST_INSERT_ID(); ` and grab the record set — nbk, Mar 19 '21 at 23:22

score 0 · Answer 1 · answered Mar 20 '21 at 07:04

OK, I rewrite the procedure without output parameter:


DELIMITER $$
CREATE PROCEDURE `InsertProduct`(IN `p_modelno` VARCHAR(40), IN `p_name` VARCHAR(120))
BEGIN
    INSERT INTO product (product_id, name, model_no) VALUES (null, p_name, p_modelno);
    SELECT LAST_INSERT_ID() as last_id FROM product; 
END$$
DELIMITER ;

VBA procedure code:


Sub InsertProduct()

  
    Dim cnSqlConn As New ADODB.Connection
    cnSqlConn.Connectionstring = "DSN=XXX"
    cnSqlConn.Open
    
    Dim cmd As New ADODB.Command
    
    With cmd
        .ActiveConnection = cnSqlConn
        .CommandType = adCmdStoredProc
        .CommandText = "InsertProduct"
        .CommandTimeout = 15
    End With
    

    cmd.Parameters.Append cmd.CreateParameter("p_modelno", adVarChar, adParamInput, 40, 
    Me.txtModelNo) 
    cmd.Parameters.Append cmd.CreateParameter("p_name", adVarChar, adParamInput, 120, 
    Me.txtName)
    
    
    dim rstProduct as new ADODB.Recordset
    set rstProduct = cmd.Execute

    Msgbox rstProduct.Fields("last_id").Value /* displays the correct value  */
    
    rstProduct.Close
    set rstProduct = nothing
    Set cmd = Nothing
    cnSqlConn.Close
    Set cnSqlConn = Nothing

End Sub()

Mysql stored procedure returns LAST_INSERT_ID as zero

1 Answers1