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

Natty · Accepted Answer · 2017-05-03 08:32:00Z

0

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

answered May 3, 2017 at 8:32

Natty

5692 gold badges12 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

h2so4 · Accepted Answer · 2017-04-28 12:12:39Z

-1

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, 2017 at 12:12

h2so4

1,5771 gold badge10 silver badges11 bronze badges

6 Comments

Natty Over a year ago

That was my first try, but I was advised to use the adodb.Command instead : stackoverflow.com/questions/43521839/… . The question is not the same but the query is

A.S.H Over a year ago

@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...

Natty Over a year ago

@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 Over a year ago

@A.S.H Tried it : it gets me a new error, I'll edit it in the question

A.S.H Over a year ago

The query still needs some tweaking. i.e. use '...' only for varchar fields not numeric fields, enclose dates with #...# instead of '...' etc. @NattyRoots

|

Collectives™ on Stack Overflow

ADODB Command : Trouble with parameters (VBA)

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related