Adding parameter to SQL query

Question

I want to get some data from Excel table using SQL-query inside macros.

My code is:

Sub Button1_Click()
Dim cn As Object, rs As Object, output As String, sql As String


Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

Value1 = Range("B4").Value ' = 30.10.2020
Value2 = Range("C4").Value ' = 2

'I want to insert Value2 = 2 inside my SQL query
sql = "SELECT * FROM [Page 1$] WHERE Job = Value2 AND DateTime = Value1"
Set rs = cn.Execute(sql) 'but it doesn't work, stops here

Do
   output = output & rs("Name") & ";" & rs(1) & ";" & rs(2) & vbNewLine
   Debug.Print rs(0); ";" & rs(1) & ";" & rs(2)
   rs.Movenext
Loop Until rs.EOF
MsgBox output

rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub

If I try using sql = "SELECT * FROM [Page 1$] WHERE Job = 2", I have no errors.

How could I paste Value2 and Value1 values inside my SQL-query?

Change: sql = "SELECT * FROM [Page 1$] WHERE Job = Value2" to: sql = "SELECT * FROM [Page 1$] WHERE Job = " & Value2 — Zac
– Zac, Commented Oct 30, 2020 at 10:18
If you want to add more than 1 value, you could do something like: sql = "SELECT * FROM [Page 1$] WHERE Job = " & Value1 & Value2 — Zac
– Zac, Commented Oct 30, 2020 at 11:10

Vignesh Kumar A · Accepted Answer · 2020-10-30 10:57:35Z

2

Try like this

Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter

Set param1 = cmd.CreateParameter("@Value2", Value2, adParamInput)
param1.Value = Range("C4").Value
cmd.Parameters.Append param1

Set param2 = cmd.CreateParameter("@Value1", Value1, adParamInput)
param2.Value = Range("B4").Value
cmd.Parameters.Append param2

With cmd
    .ActiveConnection = cn
    .CommandText = "SELECT * FROM [Page 1$] WHERE Job = ? AND DateTime = ?"

    Set rs = .Execute
End With

edited Oct 30, 2020 at 10:57

answered Oct 30, 2020 at 10:21

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

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

8 Comments

Hel O'Ween Over a year ago

Use Parameterized Queries as shown in the example above whenever humanly possible. It a) helps securing your application by e.g. proper string escaping and b) converts your variable values/data types to the correct equivalent SQL format.

Vignesh Kumar A Over a year ago

@Angelika You can add more parameter

Angelika Over a year ago

Yes, but how could I access them? Using index inside cmd?

Vignesh Kumar A Over a year ago

Yes you can add how many you want, but you should maintain the order.

Angelika Over a year ago

I get compile error on Dim cmd As New ADODB.Command User-defined type not defined

|

Collectives™ on Stack Overflow

Adding parameter to SQL query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related