ADODB 命令:参数问题(VBA) [英] ADODB Command : Trouble with parameters (VBA)

问题描述

我正在尝试使用 ADODB.Command 对象执行 UPDATE SQL 查询.我以前从未使用过它，所以我可能犯了一些愚蠢的错误，她是我代码的副本:

I am trying to execute an UPDATE SQL query using an ADODB.Command object. I never used it before so I may be doing stupid mistakes, her's a copy of my code:

Public Function setOffre(idO As Integer, idS As Integer, ref As String, dateO As Date, dateF As Date, nomR As String, idEntRecr As Integer, emploi As String, idEtat As Integer, lieu As String, plage As String, College As String)
Dim sqlQuery As String
Dim rs As ADODB.Recordset

Set connect = New ADODB.Connection
connect.Open connString              

sqlQuery = "UPDATE offres " & _
                "SET source_ID = @idSource, " & _
                "reference = @idRef, " & _
                "recruteur = @recruteur, " & _
                "lieu = @lieu, " & _
                "etat_ID = @idEtat, " & _
                "emploi = @emploi, " & _
                "plage = @plage, " & _
                "college = @college, " & _
                "offre_Date = @dateO, " & _
                "offre_Forclusion = @forcO, " & _
                "offre_Mois = @moisO, " & _
                "entite_ID = @idEntite " & _
           "WHERE offre_ID = @idOffre;"

With New ADODB.Command
    .ActiveConnection = connect
    .CommandType = adCmdText
    .NamedParameters = True
    .CommandText = sqlQuery

    .Parameters.Append .CreateParameter("@idSource", adInteger, adParamInput, 10, idS)
    .Parameters.Append .CreateParameter("@idRef", adLongVarChar, adParamInput, 10, ref)
    .Parameters.Append .CreateParameter("@recruteur", adLongVarChar, adParamInput, 100, nomR)
    .Parameters.Append .CreateParameter("@lieu", adLongVarChar, adParamInput, 100, lieu)
    .Parameters.Append .CreateParameter("@idEtat", adInteger, adParamInput, 10, idEtat)
    .Parameters.Append .CreateParameter("@emploi", adLongVarChar, adParamInput, 200, emploi)
    .Parameters.Append .CreateParameter("@plage", adLongVarChar, adParamInput, 10, plage)
    .Parameters.Append .CreateParameter("@college", adLongVarChar, adParamInput, 200, College)
    .Parameters.Append .CreateParameter("@dateO", adDate, adParamInput, 10, Format(dateO, "yyyy-mm-dd"))
    .Parameters.Append .CreateParameter("@forcO", adDate, adParamInput, 10, Format(dateF, "yyyy-mm-dd"))
    .Parameters.Append .CreateParameter("@moisO", adInteger, adParamInput, 10, Month(dateO))
    .Parameters.Append .CreateParameter("@idEntite", adInteger, adParamInput, 10, idEntRecr)
    .Parameters.Append .CreateParameter("@idOffre", adInteger, adParamInput, 10, idO)

    .Execute
End With

connect.Close
End Function

我在 .Execute 上的错误触发器显示 [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.16]Invalid parameter type.adLongVarChar 不是用于字符串值的正确类型吗?

My error trigger on .Execute it says [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.16]Invalid parameter type. Is adLongVarChar not the right type to use for a string value ?

尝试@h2so4 的方法(将参数值连接到命令字符串中)后，我收到另一个错误消息

EDIT : After trying @h2so4 's method (concatenating the parameter values into the command string), I get an other error saying

[MySQL][ODBC 5.3(a) Driver][mysqld-5.5.16]我们的冠军这个 MySQL 版本".

不知道它是什么意思，但它在我的执行行上触发:

No idea what it means but it triggers on my executing line :

connect.Execute sqlQuery, , adCmdText

推荐答案

最后，我的错误是表名offres不对，是offre.

In the end, my error was that the table name offres was not right it was offre.

这篇关于ADODB 命令:参数问题(VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！