title: mySQL, Excel, VBA and ADODB (command) trouble with parameters
body: <p>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 <a href="https://stackoverflow.com/questions/43679797/adodb-command-trouble-with-parameters-vba">this solution: adodb trouble with parameters  and vba</a> but that does not work for me.</p>
  <p>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</p>
  <p><em>[MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.4.14-MariaDB] Invalid parameter Type</em></p>
  <p>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.</p>
  <pre><code>Sub Addres_to_Database_2_Update()
  'Update an MySQL database (database: samueldb, table: users) on localhost
  'with data form an excel sheet called &quot;Testblad&quot;
  
  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(&quot;Testblad&quot;).Range(&quot;B6&quot;).Value
  firstname = Sheets(&quot;Testblad&quot;).Range(&quot;B7&quot;).Value
  lastname = Sheets(&quot;Testblad&quot;).Range(&quot;B8&quot;).Value
  address = Sheets(&quot;Testblad&quot;).Range(&quot;B9&quot;).Value
  contact = CStr(Sheets(&quot;Testblad&quot;).Range(&quot;B10&quot;).Value)
  
  MsgBox &quot;Data: &quot; &amp; user_id &amp; &quot; &quot; &amp; firstname &amp; &quot; &quot; &amp; lastname &amp; &quot; &quot; &amp; address &amp; &quot; &quot; &amp; contact
  
  Set Connect = New ADODB.Connection
  Connect.Open &quot;DRIVER= {MySQL ODBC 8.0 Unicode Driver}; SERVER=localhost;DATABASE=samueldb;USER=root;    PASSWORD=;&quot;
  'password is empty
  
  'This normal SQL string works fine
  'sqlQuery = &quot;UPDATE users &quot; &amp; _
  '                 &quot;SET FirstName = '&quot; &amp; firstname &amp; &quot;', &quot; &amp; _
  '                 &quot;LastName = '&quot; &amp; lastname &amp; &quot;', &quot; &amp; _
  '                 &quot;address = '&quot; &amp; address &amp; &quot;', &quot; &amp; _
  '                 &quot;contact = '&quot; &amp; contact &amp; &quot;' WHERE user_id= &quot; &amp; user_id &amp; &quot; &quot;
  
  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 = &quot;UPDATE users &quot; &amp; _
                  &quot;SET firstname = ?firstname, &quot; &amp; _
                  &quot;lastname = ?lastname, &quot; &amp; _
                  &quot;address = ?address, &quot; &amp; _
                  &quot;contact = ?contact &quot; &amp; _
                   &quot;WHERE user_id = user_id&quot;
  Debug.Print sqlQuery
  'Result when debugged &gt; 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   &gt; Run-time error '3708':
  '
   '   .Parameters.Append .CreateParameter(&quot;?firstname&quot;, , adParamInput, , firstname)
  'RESULT: Run-time error '3708':Parameter Object is improperly defined. Inconsistent or incomplete information was provided
  ' code stops her
   '  .Parameters.Append .CreateParameter(&quot;?lastnamer&quot;, , adParamInput, , lastname)
  '   .Parameters.Append .CreateParameter(&quot;?address&quot;, , adParamInput, , address)
  '   .Parameters.Append .CreateParameter(&quot;?contact&quot;, , adParamInput, , contact)
  
  '========== Test 2 This does not work, same failure  &gt; Run-time error '3708':
  '
  '   .Parameters.Append .CreateParameter(&quot;?firstname&quot;, , , , firstname)
  'RESULT: Run-time error '3708':Parameter Object is improperly defined. Inconsistent or incomplete information was provided
  ' code stops her
  '    .Parameters.Append .CreateParameter(&quot;?lastnamer&quot;, , , , lastname)
  '    .Parameters.Append .CreateParameter(&quot;?address&quot;, , , , address)
  '    .Parameters.Append .CreateParameter(&quot;?contact&quot;, , , , contact)
  
   '========== Test 3 This does not work, Run-Time error 13
   '
   '   .Parameters.Append .CreateParameter(&quot;?firstname&quot;, firstname)
  'Result: Run-Time error 13: Type Mismatch.
  ' code stops her
   '   .Parameters.Append .CreateParameter(&quot;?lastnamer&quot;, lastname)
   '   .Parameters.Append .CreateParameter(&quot;?address&quot;, address)
   '   .Parameters.Append .CreateParameter(&quot;?contact&quot;, contact)
      
  '======= Test 4 This does not work
  '
  '    .Parameters.Append .CreateParameter(&quot;?firstname&quot;, adChar, adParamInput, 30, firstname)
  '    .Parameters.Append .CreateParameter(&quot;?lastnamer&quot;, adChar, adParamInput, 30, lastname)
  '    .Parameters.Append .CreateParameter(&quot;?address&quot;, adChar, adParamInput, 150, address)
  '    .Parameters.Append .CreateParameter(&quot;?contact&quot;, adChar, adParamInput, 20, contact)
  
  'Debug.Print sqlQuery
  'Result when debugged &gt;  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 &gt;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(&quot;?&quot;, adChar, adParamInput, 30, firstname)
     .Parameters.Append .CreateParameter(&quot;?&quot;, adChar, adParamInput, 30, lastname)
     .Parameters.Append .CreateParameter(&quot;?&quot;, adChar, adParamInput, 150, address)
     .Parameters.Append .CreateParameter(&quot;?&quot;, adChar, adParamInput, 20, contact)
  
  Debug.Print sqlQuery
  'Result when debugged &gt; 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 &gt;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
  </code></pre>