ADODB CreateParameter在SELECT没有CommandText中

问题描述：

指定参数使用SQL Server Express 2014年访问2016ADODB CreateParameter在SELECT没有CommandText中

前端包含旨在用于搜索数据库中的记录形式。提交表单上的VBA代码将SELECT语句的WHERE构建为一个长字符串。

这是一个简化的例子。

Set thisDb = DBEngine.Workspaces(0).Databases(0) 
Set qDef = thisDb.CreateQueryDef("tempPTQ") 
qDef.Connect = "ODBC;Driver={ODBC Driver 11 for SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;" 

strFields = "field1, field2, field3" 

strTable = "dbo_SomeTable" 

strParam = "WHERE field1=" & txtBox1.Value & ", AND field2=" & txtBox2.Value & ", AND field3=" & txtBox3.Value 

strSQL = "SELECT " & strFields & " FROM " & strTable & " WHERE " & strParam & ";" 

qDef.SQL = strSQL 

DoCmd.RunSQL "INSERT INTO " & strDestTbl & " SELECT * FROM tempPTQ"

是否有可能这与动态WHERE子句，基本上列的可变数目的，每一个由不同的参数来表示转换为ADODB参数化查询？

strSQL = "SELECT field1, field2, field3 FROM someTable" 
Set dbCon = New ADODB.Connection 

With dbCon 
    .ConnectionString = "Driver={SQL Server Native Client 11.0};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;" 
    .Open 
End With 

Set dbCmd = New ADODB.Command 

With dbCmd 
    .ActiveConnection = dbCon 
    .CommandText = strSQL 
    If txtBox1.Value <> "" Then 
     .CreateParameter("param1", adChar) 
     .Parameters(0).Value = txtBox1.Value 
    End If 
    If txtBox2.Value <> "" Then 
     .CreateParameter("param2", adChar) 
     .Parameters(1).Value = txtBox2.Value 
    End If 
    If txtBox3.Value <> "" Then 
     .CreateParameter("param3", adChar) 
     .Parameters(2).Value = txtBox3.Value 
    End If 

    Set rst = .Execute() 
    rst.Close 
    Set rst = Nothing 
End With

如何参数动态地添加到WHERE子句？

答

考虑使用保持着与?占位符WHERE条款声明及相应参数的字典集合：

Private Function FilterCriteria() As Collection 
    Dim sqlCollection As New Collection 
    Dim strCriteria As String 
    Dim params As Object 

    Set params = CreateObject("Scripting.Dictionary") 

    strCriteria = "1 = 1"     ' ALWAYS TRUE CONDITION TO START WHERE CLAUSE  
    If txtBox1.Value <> "" Then 
     strCriteria = strCriteria & " AND field1 = ?" 
     params.Add "field1param", txtBox1.Value 
    End If 

    If txtBox2.Value <> "" Then 
     strCriteria = strCriteria & " AND field2 = ?" 
     params.Add "field2param", txtBox2.Value 
    End If 

    If txtBox3.Value <> "" Then 
     strCriteria = strCriteria & " AND field3 = ? " 
     params.Add "field3param", txtBox3.Value 
    End If 

    sqlCollection.Add strCriteria 
    sqlCollection.Add params 

    Set FilterCriteria = sqlCollection 

End Function

然后在你实际的数据库调用，检索上述功能的恢复收集和准备语句中使用和.CreateParameters：

Dim sqlCollection As New Collection 
Set sqlCollection = FilterCriteria  ' CALLING ABOVE FUNCTION (RETURNED COLLECTION) 

Set dbCon = New ADODB.Connection  
With dbCon 
    .ConnectionString = "Driver={SQL Server Native Client 11.0};SERVER=" & _ 
         stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;" 
    .Open 
End With 

' CONCATENATE WHERE CLAUSE STRING TO SQL STATEMENT 
strSQL = "SELECT field1, field2, field3 FROM someTable WHERE " & sqlCollection(1) 

Set dbCmd = New ADODB.Command   
With dbCmd 
    .ActiveConnection = dbCon 
    .CommandText = strSQL 

    ' BIND PARAMETERS FROM PARAMS DICT (KEYS=NAME, VALUES=PARAM VALUE) 
    For Each key In sqlCollection(2).keys 
     cmd.Parameters.Append cmd.CreateParameter(key, adVarChar, adParamInput, 255, _ 
           sqlCollection(2)(key)) 
    Next key 

    Set rst = .Execute() 
    rst.Close 
    Set rst = Nothing 
End With

ADODB CreateParameter在SELECT没有CommandText中

相关推荐