使用参数化从 VBA 查询数据库

用户6089076

我试图从 VBA 查询数据库,为了避免 SQL 注入这样的问题,我被告知我应该参数化 SQL 代码字符串。我目前拥有的代码不返回错误,但也没有结果(!)是:

Sub ImportData_Click()


Dim strSql As String
Dim strDate As String
Dim StrDatetwo As String
Dim strinfo As String
Dim strinfotwo As String
Dim strgroup As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String

'Parameters
strDate = Format(Range("Date"), "DDMMMYY")
StrDatetwo = Format(Range("Datetwo"), "DDMMMYY")
strinfo = "someinfo"
strinfotwo = "total"
strgroup = "total"

'open connection
ConnectionString = "Provider=name ;Data Source=name;Initial Catalog=name;Integrated Security=SSPI"
objConn.Open ConnectionString

'SQL string
 strSql = "Declare @mindate date," & _
                  "@maxdate date " & _
         "set @mindate ='" & strDate & _
         "'; set @maxdate ='" & StrDatetwo & _
         "'; SELECT MIN([results].run_id) " & _
         "FROM [results] " & _
         "inner join [official_run_table] on ([results].run_id=[official_run_table].run_id and [official_run_table].run_type_id='1' )" & _
         " WHERE Info ='" & strinfo & _
         "' and two ='" & strinfotwo & _
         "' and group= '" & strgroup & _
         "' AND [results].date between @mindate and @maxdate"

'MsgBox strSQL '- to check data

rst.Open strSql, objConn


If Not rst.EOF Then
Worksheets("Reference").Range("E2").CopyFromRecordset rst
rst.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If


'Clean up
 objConn.Close
 Set objConn = Nothing
 Set rst = Nothing

 End Sub 

没有错误但没有结果意味着此查询的数据库中不存在任何值,但我知道否则!我也打算对 strinfo、strinfotwo、strgroup 进行参数化,但首先我想让上述工作正常进行。我怎么弄错了?提前致谢

丹·古兹曼

首先,使用 ? 参数化查询字符串。参数标记(用于 OLE DB 和 ODBC 驱动程序)而不是字符串文字。注意查询字符串中的参数值不包含在引号中。我将group列名在方括号中,因为它是保留的 SQL 关键字并且不符合标识符规则。

 strSql = "SELECT MIN([results].run_id) " & _
         "FROM [results] " & _
         "inner join [official_run_table] on ([results].run_id=[official_run_table].run_id and [official_run_table].run_type_id='1' )" & _
         " WHERE Info = ?" & _
         " AND two = ?" & _
         " AND [group] = ?" & _
         " AND [results].date between ? and ?;"

下面的示例使用 ADODB.Command 对象并向查询添加参数。我猜到了您的实际数据类型,因此更改参数类型和长度以匹配您的实际表架构。这是完成此任务的众多方法之一,但应该可以帮助您入门。有关更多信息和示例,请参阅ADODB 命令参考

Set parmInfo = command.CreateParameter("@Info", adVarChar, adParamInput, 10, strinfo)
Set parmInfoTwo = command.CreateParameter("@InfoTwo", adVarChar, adParamInput, 10, strinfotwo)
Set parmGroup = command.CreateParameter("@Group", adVarChar, adParamInput, 10, strgroup)
Set parmMinDate = command.CreateParameter("@MinDate", adDBTimeStamp, adParamInput, , strDate)
Set parmMaxDate = command.CreateParameter("@MaxDate", adDBTimeStamp, adParamInput, , strDatetwo)

command.Parameters.Append(parmInfo)
command.Parameters.Append(parmInfoTwo)
command.Parameters.Append(parmGroup)
command.Parameters.Append(parmMinDate)
command.Parameters.Append(parmMaxDate)

Set rst = command.Execute()

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章