Visual Basic Concepts
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:
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
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