Executing a Parameter Query

See Also

RDO

The procedure below illustrates a method for performing a parameterized Select query: that is, a SELECT statement that requires one or more parameters. This process is done in two steps:

1. Build a query that expects a parameter, pass in the first parameter, then perform the query.
   
   
2. Perform the query again with new parameters.

The first time the query is called, RDO attempts to create a new RDO query object. Since the object is appended to the rdoConnection object’s rdoQueries collection, you can reference it each time the procedure is called. Each subsequent time the procedure is called, the Refresh method re-executes the query. This technique builds a temporary stored procedure (SP) behind the scenes that is referenced by the Requery method. The temporary SP is dropped when the connection is closed.

Private Sub ParmQueryButton_Click()
   Dim Qy As New rdoQuery
   Dim rs As rdoResultset
   Static FirstTime As Boolean
   If cn.rdoQueries.Count = 0 Then
      FirstTime = True
      sql = "select * from authors where year_born = ?"
      Set Qy = cn.CreateQuery("Pq", sql)
   End If
   Qy(0) = QueryParam.Text
   If FirstTime Then
      Set rs = Qy.OpenResultset()
      FirstTime = False
   Else
      rs.Requery
   End If
   rdoGrid1.ShowData rs
   rs.Close
End Sub

ADO

This procedure is designed to perform a table-access query that accepts a parameter. You use the "?" character (as in the previous RDO example) to indicate where the parameter is to be placed. In this case, though, you don't create an rdoQuery object that is kept in a collection off the rdoConnection object; you instead use a stand-alone ADO Command object created (and scoped) earlier. The first time through, you set up the Command properties, and each time thereafter, you simply execute the command after having changed the parameter.

ADO gives you a lot of flexibility here—more, in some cases than RDO. If you tell ADO everything it needs to know about a query, it won't have to perform informational queries against the database to get missing information, so queries run faster.

Note   You don’t have to build the ADO Parameters collection in code, since it's automatically created for you just like when you use RDO. However, it is possible to do so, and doing so can improve performance, at the cost of a little code complexity. If you elect to do it, make sure that the Command is associated with an open connection so ADO can query the service provider (and the server) for the parameter's description.

To run the query and create the resultset, use the Execute method on the Command object.

Private Sub ParmQueryButton_Click()
   If Cmd.CommandText = "" Then
      Cmd.ActiveConnection = cn
      With Cmd
         .CommandText = "select * from authors where year_born = ?"
         .CommandType = adCmdText
         .CommandTimeout = 15
      End With
'
'   The following section of code is not required, 
'    but can make execution faster. It eliminates the need
'   for ADO to fetch the parameter metrics from the server.
'
      With Parm
         .Type = adInteger
         .Size = 4
         .Direction = adParamInput
         .Value = QueryParam.Text
         Cmd.Parameters.Append Parm
      End With
   End If

   Cmd.Parameters(0).Value = QueryParam.Text
   Set rs = Cmd.Execute()
   ADOGrid1.ShowData rs
   rs.Close
End Sub