Excel SQL VBA: adding more than one cmd parameter

Question

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.

For second code block, you say But I also get an error. Is this the same error? If not, post its message. — Parfait
– Parfait, Commented Nov 11, 2020 at 16:29

Pᴇʜ · Accepted Answer · 2020-11-11 16:01:18Z

3

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)

edited Nov 11, 2020 at 16:01

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

answered Nov 11, 2020 at 15:27

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

3 Comments

MGP Over a year ago

Its important to note, that the size of the string is restricted by the FieldSize in the Database. If len(lne) > max_field_size you will get an error.

FunThomas Over a year ago

@MG92: Had no problem with that using SQL server and Oracle, and I doubt it will be a problem using Excel as database. Of course it will give an error when you use the parameter in an Insert or Update statement.

MGP Over a year ago

We had some issues when using a Access-DB. When a field is initialized with varchar(32) and a parameter was added with a size bigger than 32, we received an error.

Collectives™ on Stack Overflow

Excel SQL VBA: adding more than one cmd parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related