ADODB Command : Trouble with parameters (VBA)

Question

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

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 ?

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]our le champ 'this MySQL version'.

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

connect.Execute sqlQuery, , adCmdText

score 0 · Answer 1 · answered Apr 28 '17 at 12:12

0

to my opinion you could adapt your sql like this and get rid of the parameters.append

sqlQuery = "UPDATE offres " & _
                "SET source_ID = '" & idS & "', " & _
                "reference = '" & ref & "', " & _
                "recruteur = '" & nomR & "', " & _
                "lieu = '" & lieu & "', " & _
                "etat_ID = '" & idEtat & "', " & _
                "emploi = '" & emploi & "', " & _
                "plage = '" & plage & "', " & _
                "college = '" & college & "', " & _
                "offre_Date = '" & Format(dateO, "yyyy-mm-dd") & "', " & _
                "offre_Forclusion = '" & Format(dateO, "yyyy-mm-dd") & "', " & _
                "offre_Mois ='" & Month(dateO) & "', " & _
                "entite_ID = '" & identRecr & "'" & _
           "WHERE offre_ID = '" & ido & "';"

answered Apr 28 '17 at 12:12

h2so4

1,559
1
10
11

That was my first try, but I was advised to use the adodb.Command instead : http://stackoverflow.com/questions/43521839/inserting-null-values-in-a-database-from-vba-code/43523092#43523092 . The question is not the same but the query is – Natty Apr 28 '17 at 12:18
@NattyRoots you should give this a try at least to make sure that the problem is in the way you use the `ADODB.Command` and not in the query itself, or in the parameter values... – A.S.H Apr 28 '17 at 12:45
@A.S.H I went through every parameters value in debug mode and they were fine. I'll try it out anyway just in case – Natty Apr 28 '17 at 12:48
@A.S.H Tried it : it gets me a new error, I'll edit it in the question – Natty Apr 28 '17 at 12:56
The query still needs some tweaking. i.e. use `'...'` only for varchar fields not numeric fields, enclose dates with `#...#` instead of `'...'` etc. @NattyRoots – A.S.H Apr 28 '17 at 13:12
@A.S.H That's actually what I did, since this method was my original try, it was still in my code as comment. – Natty Apr 28 '17 at 13:43

score 0 · Accepted Answer · answered May 03 '17 at 08:32

0

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

answered May 03 '17 at 08:32

Natty

497
1
11
23

ADODB Command : Trouble with parameters (VBA)

2 Answers2

Linked