-2

I have a very straight forward and simple Stored Procedure accepting 1 parameter. When I called the sp from VBA, I got the below error from command.Execute:

-2147217887 [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.31-log]Invalid parameter type

However, I cannot determine what parameter type is incorrect from my view. I'm really running out of wits

Update:
Excel VBA MySql parameterised update 'invalid parameter type' has the exact same error, however, the answer is for non- Stored Procedure calls.

My code is like the below
Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_load_visit`(
IN _nid char(18)
)
BEGIN
SET @query = concat("
    select visiting_date, method, visiting_details from tbl_visit where visiting_customer=? order by visiting_date asc;
    ");
PREPARE stmt FROM @query;
SET @_nid = _nid;
EXECUTE stmt USING @_nid;
DEALLOCATE PREPARE stmt;
END

VBA code:

 Public Sub formControl_Update()
    Dim cmd As New ADODB.Command
    Dim conn As ADODB.Connection
    Dim prm As ADODB.Parameter
    Dim strConn As String
    Dim strSQL As String

    strConn = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=127.0.0.1;Database=jkydb1;Uid=admin;pwd=123456"

    conn.Open strConn
   
    command.ActiveConnection = myConn
    command.CommandText = "sp_load_visit"
    command.CommandType = adCmdStoredProc
    command.CommandTimeout = 10
    command.NamedParameters = True
    command.Prepared = True
    Set prm = command.CreateParameter("_nid", adChar, adParamInput, 18, cell_customer_nid.value)
    command.Parameters.Append prm
    
    Set rs = command.Execute


End Sub

The post I've read: Parameter Object
run stored procedure and return values from VBA

julie
  • 45
  • 5
  • *How to know if the parameters are in the correct order of Stored Procedures?* Execute `SHOW CREATE PROCEDURE sp_UpdateUserInfo;` and look for parameters ordering and types. PS. You have NO specified the parameters in the procedure executing SQL... maybe you must use something like `cmd.CommandText = "sp_UpdateUserInfo(OrderID, OrderDate)"`? – Akina Dec 21 '20 at 06:54
  • Hi @Akina, my actual code has a lot more than these two fields, if i have to specify the order as you put it, I'll also re-arrange the orders of getting values from cells. – julie Dec 21 '20 at 09:38
  • No, you add parameters by name, not by position, so it doesn't matter in which order you add the values. The ordering is specified by the parameter names mentioned in CALL statement. – Akina Dec 21 '20 at 09:57
  • Are you sure you are using mysql? Your connection string is for ms sql server, so your code would throw an error if you tried to connect to a mysql server with it. – Shadow Dec 21 '20 at 10:35
  • Hi @Shadow, thank you for your comment! Indeed I copied the connection string from this site as I can't remote to my PC when asking the question – julie Dec 21 '20 at 11:57
  • @Shadow but I am sure that I'm using MySQL – julie Dec 21 '20 at 11:58
  • Hi @Akina, that's exactly what I need. I've set `command.NamedParameters = True`, is there something else? Should I match the parameter name to the stored procedure input or table columns? – julie Dec 21 '20 at 12:12
  • @Shadow I've corrected the connection string. Thanks for pointing that out! – julie Dec 21 '20 at 12:16
  • I updated the command as : "sp_load_visit(_nid)" and "sp_load_visit(?)" but no luck. – julie Dec 25 '20 at 08:51

1 Answers1

0

After some research and testings, the best way to work around this I found is to write customized insert, update, delete, select queries and use recordSet.Open queryString, sqlConnection, adOpenStatic to get the result from recordSet.

julie
  • 45
  • 5