时间戳

首页 > 解决方案 > 向 SQL 查询添加参数

sql - 向 SQL 查询添加参数

问题描述

我想使用宏内部的 SQL 查询从 Excel 表中获取一些数据。

我的代码是：

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

如果我尝试使用sql = "SELECT * FROM [Page 1$] WHERE Job = 2"，我没有错误。

我如何在我的 SQL 查询中粘贴Value2和值？Value1

标签： sqlexcelvba

解决方案

像这样试试

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

推荐阅读