mySQL, Excel, VBA and ADODB (command) trouble with parameters

Question

I have a database (localhost) with user data. I use mySQL. I want to update these data by using an Excelsheet. I have written some VBA code. I have looked into this solution: adodb trouble with parameters and vba but that does not work for me.

I made a VBA macro and did some tests. The macro only works with the 'normal' SQL-string. I have put the different tests and errors in the code. I can't figure out what i am doing wrong. I already searched with Google for the different Errors, but i can''t find sufficient information about

[MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.4.14-MariaDB] Invalid parameter Type

I checked the database and the value type settings for the columns (varChar), so that seems (?)ok. Can some one help me out? Best Regards M.

Sub Addres_to_Database_2_Update()
'Update an MySQL database (database: samueldb, table: users) on localhost
'with data form an excel sheet called "Testblad"

Dim Connect As ADODB.Connection
Dim sqlQuery As String

Dim user_id As String
Dim firstname As String
Dim lastname As String
Dim address As String
Dim contact As String

user_id = Sheets("Testblad").Range("B6").Value
firstname = Sheets("Testblad").Range("B7").Value
lastname = Sheets("Testblad").Range("B8").Value
address = Sheets("Testblad").Range("B9").Value
contact = CStr(Sheets("Testblad").Range("B10").Value)

MsgBox "Data: " & user_id & " " & firstname & " " & lastname & " " & address & " " & contact

Set Connect = New ADODB.Connection
Connect.Open "DRIVER= {MySQL ODBC 8.0 Unicode Driver}; SERVER=localhost;DATABASE=samueldb;USER=root;    PASSWORD=;"
'password is empty

'This normal SQL string works fine
'sqlQuery = "UPDATE users " & _
'                 "SET FirstName = '" & firstname & "', " & _
'                 "LastName = '" & lastname & "', " & _
'                 "address = '" & address & "', " & _
'                 "contact = '" & contact & "' WHERE user_id= " & user_id & " "

Debug.Print sqlQuery
'Result: UPDATE users SET FirstName = 'Henkie', LastName = 'Strijbos', address = 'Amsterdam', contact     = '35673567' WHERE user_id= 23
'Result = OK
'Database will be Updated when SQL is executed

'=== But: testing with parameters does not work
'see: https://stackoverflow.com/questions/43679797

sqlQuery = "UPDATE users " & _
                "SET firstname = ?firstname, " & _
                "lastname = ?lastname, " & _
                "address = ?address, " & _
                "contact = ?contact " & _
                 "WHERE user_id = user_id"
Debug.Print sqlQuery
'Result when debugged > UPDATE users SET firstname = ?firstname, lastname = ?lastname, address = ?address, contact = ?contact WHERE user_id = user_id
'Result = OK


With New ADODB.command
    .ActiveConnection = Connect
    .CommandType = adCmdText
    .NamedParameters = True
    .CommandText = sqlQuery
    
' ========== Test 1   > Run-time error '3708':
'
 '   .Parameters.Append .CreateParameter("?firstname", , adParamInput, , firstname)
'RESULT: Run-time error '3708':Parameter Object is improperly defined. Inconsistent or incomplete information was provided
' code stops her
 '  .Parameters.Append .CreateParameter("?lastnamer", , adParamInput, , lastname)
'   .Parameters.Append .CreateParameter("?address", , adParamInput, , address)
'   .Parameters.Append .CreateParameter("?contact", , adParamInput, , contact)

'========== Test 2 This does not work, same failure  > Run-time error '3708':
'
'   .Parameters.Append .CreateParameter("?firstname", , , , firstname)
'RESULT: Run-time error '3708':Parameter Object is improperly defined. Inconsistent or incomplete information was provided
' code stops her
'    .Parameters.Append .CreateParameter("?lastnamer", , , , lastname)
'    .Parameters.Append .CreateParameter("?address", , , , address)
'    .Parameters.Append .CreateParameter("?contact", , , , contact)

 '========== Test 3 This does not work, Run-Time error 13
 '
 '   .Parameters.Append .CreateParameter("?firstname", firstname)
'Result: Run-Time error 13: Type Mismatch.
' code stops her
 '   .Parameters.Append .CreateParameter("?lastnamer", lastname)
 '   .Parameters.Append .CreateParameter("?address", address)
 '   .Parameters.Append .CreateParameter("?contact", contact)
    
'======= Test 4 This does not work
'
'    .Parameters.Append .CreateParameter("?firstname", adChar, adParamInput, 30, firstname)
'    .Parameters.Append .CreateParameter("?lastnamer", adChar, adParamInput, 30, lastname)
'    .Parameters.Append .CreateParameter("?address", adChar, adParamInput, 150, address)
'    .Parameters.Append .CreateParameter("?contact", adChar, adParamInput, 20, contact)

'Debug.Print sqlQuery
'Result when debugged >  UPDATE users SET firstname = ?firstname, lastname = ?lastname, address = ?address, contact = ?contact WHERE user_id = user_id
'I do not understand, because the values should be appointed

'should it not be:
'UPDATE users SET FirstName = 'Henkie', LastName = 'Strijbos', address = 'Amsterdam', contact = '35673567' WHERE user_id= 23

'When executed >Run-time Error -2147217887 (80040e21)
' [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.4.14-MariaDB] Invalid parameter Type

'=========== Test 5 This does not work
   .Parameters.Append .CreateParameter("?", adChar, adParamInput, 30, firstname)
   .Parameters.Append .CreateParameter("?", adChar, adParamInput, 30, lastname)
   .Parameters.Append .CreateParameter("?", adChar, adParamInput, 150, address)
   .Parameters.Append .CreateParameter("?", adChar, adParamInput, 20, contact)

Debug.Print sqlQuery
'Result when debugged > UPDATE users SET firstname = ?firstname, lastname = ?lastname, address = ?address, contact = ?contact WHERE user_id = user_id
'I do not understand, because the values should be appointed

'should it not be:
'UPDATE users SET FirstName = 'Henkie', LastName = 'Strijbos', address = 'Amsterdam', contact = '35673567' WHERE user_id= 23

'When executed >Run-time Error -2147217887 (80040e21)
' [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.4.14-MariaDB] Invalid parameter Type
' ==
  .Execute
End With

Connect.Close
End Sub

Answer 1

I never managed to work with named parameters, so I will only show how to work with unnamed.
a) The sql command text get a simple ? at the place where you want to insert a parameter.

sqlQuery = "UPDATE users " _
         & "   SET firstname = ?, lastname = ?, address = ?, contact = ? " _
         & " WHERE user_id = ?"

b) Parameter must be appended in the correct order. You can set a parameter name, but it's optional. The important thing is the order, and you need to create exactly one parameter for every ? in your query. You probably have also a parameter for the Where-Clause (in your current query, you would update the whole table as the where-clause user_id = user_id is true for all records (except for those where the user_id is null).

 .Parameters.Append .CreateParameter(, adChar, adParamInput, 30, firstname)
 .Parameters.Append .CreateParameter(, adChar, adParamInput, 30, lastname)
 .Parameters.Append .CreateParameter(, adChar, adParamInput, 150, address)
 .Parameters.Append .CreateParameter(, adChar, adParamInput, 20, contact)
 .Parameters.Append .CreateParameter(, adChar, adParamInput, 20, user_id)

c) Not 100% sure about mySQL, but I think you need to set the parameter type to adVarChar and the length parameter to the actual string length.

 .Parameters.Append .CreateParameter(, adVarChar, adParamInput, len(firstName), firstname)
 (...)

d) With Debug.Print, you will always see the command text that you have entered. The command is send together with the parameters to the database engine, and the engine resolves this. ADODB does not care about the syntax that is used on the database side. This saves you and ADODB from the nightmare of putting all the correct quotes or the correct syntax for a Date parameter, or which character is used as decimal separator.