mySQL, Excel, VBA和ADODB(命令)参数问题

Question

我有一个数据库(localhost)与用户数据。我使用mySQL。我想通过使用excel表格更新这些数据。我写了一些VBA代码。我已经研究了这个解决方案:参数和vba的问题，但这对我不起作用。

我做了一个VBA宏并做了一些测试。宏仅适用于"正常"sql字符串。我已经把不同的测试和错误的代码。我不知道我做错了什么。我已经用谷歌搜索了不同的错误，但我找不到关于

的足够信息。[MySQL][ODBC 8.0(w) Driver][mysqld-5.5.5-10.4.14-MariaDB]无效参数Type

我检查了数据库和列的值类型设置(varChar)，所以看起来(?)ok。有人能帮我一下吗?致以最亲切的问候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

我从来没有处理过命名参数，所以我将只展示如何处理未命名参数。
a) sql命令文本在您想要插入参数的地方获得一个简单的?。

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

b)参数必须按正确的顺序追加。您可以设置参数名称，但这是可选的。重要的是顺序，您需要为查询中的每个?创建一个参数。您可能也有一个Where-子句的参数(在您当前的查询中，您将更新整个表，因为where-子句user_id = user_id对所有记录都为真(除了那些user_id为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)不是100%确定mySQL，但我认为你需要设置参数类型为adVarChar和长度参数为实际字符串长度。

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

d)使用Debug.Print，您将始终看到您输入的命令文本。该命令与参数一起发送到数据库引擎，由数据库引擎解析。ADODB不关心数据库端使用的语法。这使您和ADODB免于为Date参数输入所有正确的引号或正确的语法，或者使用哪个字符作为小数分隔符。