Home

Creating Parameter Queries with ADO

This sample project demonstrates using ADO to create parameter queries. A parameter query is a query that when run prompts the user for information, such as criteria for retrieving records or a value to insert in a field. You can design the query to prompt users for more than one piece of information; for example, you can design it to prompt a user for two dates. This project demonstrates parameter queries with both single and multiple parameters.

Download a copy of the project.

Making the ADO Parameter Queries Project

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ActiveX Data Objects 2.1 Library .
  3. Add two command buttons to the form. Set properties on the command buttons as listed in the following table.

    Object Property Value
    1st command button Name cmdSingleParam
    1st command button Caption Query with single parameter
    2nd command button Name cmdTwoParams
    2nd command button Caption Query with two parameters

    When you're done, the form might look like this.

    screen shot

  4. Copy the following code into the form.

    Option Explicit
    Private Sub cmdSingleParam_Click()
        Call SingleParameter
    End Sub

    Private Sub cmdTwoParams_Click()
        Call TwoParameters
    End Sub

    Sub SingleParameter()
        Dim cmdNWind As ADODB.Command
        Dim cnnNWind As ADODB.Connection
        Dim prmOrders As ADODB.Parameter
        Dim rstOrders As ADODB.Recordset
        Dim fldOrders As ADODB.Field
        Dim strSQL As String

        Set cmdNWind = New Command
        Set prmOrders = New Parameter
        Set cnnNWind = New Connection

        'Initialize SQL string and parameters array.
        strSQL = "SELECT * From OrdersWHERE (((Orders.ShippedDate)>? And(Orders.ShippedDate)<#8/31/94#));"

        'Open the connection.
        cnnNWind.Open "northwind"
        cmdNWind.ActiveConnection = cnnNWind

        'Set Command object properties.
        With cmdNWind
            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 15
        End With

        'Set Parameter object properties.
        With prmOrders
            .Type = adChar
            .Size = 20
            .Direction = adParamInput
            .Value = Trim(InputBox _
            ("Enter date:" & vbCrLf & vbCrLf & "(Suggested value = 8/1/1994)"))
            cmdNWind.Parameters.Append prmOrders
        End With

        'Execute the command.
        Set rstOrders = New ADODB.Recordset
        Set rstOrders = cmdNWind.Execute()

        'Display recordset values in the Immediate window.
        With rstOrders
            Do While Not .EOF
                For Each fldOrders In rstOrders.Fields
                    Debug.Print fldOrders.Value & "; ";
                Next
                Debug.Print
                .MoveNext
            Loop
        End With

        'Cleanup.
        rstOrders.Close
        Set rstOrders = Nothing
        Set prmOrders = Nothing
        Set cnnNWind = Nothing
        Set cmdNWind = Nothing
    End Sub

    Sub TwoParameters()
        Dim cmdNWind As ADODB.Command
        Dim cnnNWind As ADODB.Connection
        Dim rstOrders As ADODB.Recordset
        Dim fldOrders As ADODB.Field
        Dim varParamValue1 As Variant
        Dim varParamValue2 As Variant
        Dim strSQL As String
        Dim arrParameters As Variant

        Set cmdNWind = New Command
        Set cnnNWind = New Connection

        'Initialize SQL string and parameters array.
        strSQL = "SELECT * From Orders WHERE "
        strSQL = strSQL &   "(((Orders.ShippedDate)>? And (Orders.ShippedDate)<?));"
        varParamValue1 = Trim(InputBox _
        ("Enter beginning date:" & vbCrLf & vbCrLf & "(Suggested value = 8/1/1994)"))
        varParamValue2 = Trim(InputBox _
        ("Enter ending date:" & vbCrLf & vbCrLf & "(Suggested value = 8/31/1994)"))
        arrParameters = Array(varParamValue1, varParamValue2)

        'Open the connection.
        cnnNWind.Open "northwind"
        cmdNWind.ActiveConnection = cnnNWind

        'Set Command object properties.
        With cmdNWind
            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 15
        End With

        'Execute the command and pass in the parameters array.
        Set rstOrders = New ADODB.Recordset
        Set rstOrders = cmdNWind.Execute(, arrParameters)

        'Display recordset values in the Immediate window.
        With rstOrders
            Do While Not .EOF
                For Each fldOrders In rstOrders.Fields
                    Debug.Print fldOrders.Value & "; ";
                Next
                Debug.Print
                .MoveNext
            Loop
        End With

        'Cleanup.
        rstOrders.Close
        cnnNWind.Close
        Set rstOrders = Nothing
        Set cnnNWind = Nothing
        Set cmdNWind = Nothing
    End Sub
  5. Save the project and arrange the windows on your desktop so that the Immediate window is visible. Run the project, and respond to the prompts with valid values like the ones suggested in the input boxes.
Last changed 6/22/00