Excel SQL VBA: adding more than one cmd parameter

I am writing SQL-query, and I get error with string value:

Dim cn As Object, rs As Object, output As String, sql As String
Dim dt As Date
Dim lne As String
Const adCmdText As Integer = 1, adDate As Integer = 7, adChar As Integer = 8
 
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" _
                    & ThisWorkbook.FullName & ";" _
                    & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

With Worksheets("Page 2")
     dt = CDate(Replace(.Cells(i, 2).Value, ".", "-"))
     lne = .Cells(2, 1).Value ' lne = mystr
End With

sql = "SELECT Name, SUM(Worktime) as summa FROM [Page 1$] WHERE DateTime = ? AND Line = " & lne & " GROUP BY Name"
Set cmd = CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = cn
    .CommandText = sql
    .CommandType = adCmdText
    
    .Parameters.Append .CreateParameter("dt", adDate, 1, , dt)
    
    Set rs = .Execute ' ERROR: One or more required parameters are missing a value.
End With

I tried to add more than one cmd parameter to solve this problem:

sql = "SELECT Name, SUM(Worktime) as summa FROM [Page 1$] WHERE DateTime = ? AND Line = ? GROUP BY Name"

Set cmd = CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = cn
    .CommandText = sql
    .CommandType = adCmdText
    
    .Parameters.Append .CreateParameter("dt", adDate, 1, , dt)
    
    .Parameters.Append .CreateParameter("lne", adChar, 2, , lne)
    
    Set rs = .Execute
End With

But I also get an error. How do I set more than one cmd parameter and use it in my query?

Table I am working with: here.

Answer

The parameter to CreateParameter are

command.CreateParameter (Name, Type, Direction, Size, Value)

The Direction – parameter should be adParamInput (that is a 1) for all parameters

My experience is that for a string parameter (adChar), you need to provide the size (length of the string).

Try

.Parameters.Append .CreateParameter("lne", adChar, adParamInput, len(lne), lne)

Advertisement

Answer