ADODB CreateParameter in SELECT without specifying Parameter in CommandText

Question

Using SQL Server Express 2014 with Access 2016

Front end contains a form intended to be used to search for records in the database. The VBA code on the submit for the form builds the WHERE of the SELECT statement as one long string.

This is an abbreviated example.

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"

Is it possible to convert this to an ADODB parameterized query with a dynamic WHERE clause, essentially a variable number of columns, each represented by a different parameter?

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

How can parameters be dynamically added to the WHERE clause?

Parfait · Accepted Answer · 2016-11-19 00:08:01Z

Consider using a collection that holds the WHERE clause statement with ? placeholders and a dictionary of corresponding parameters:

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

Then in your actual database call, retrieve above function's returned collection and use in prepared statement and .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

Collectives™ on Stack Overflow

ADODB CreateParameter in SELECT without specifying Parameter in CommandText

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related