rdoQuery Object, rdoQueries Collection Example

This example leverages RDO's ability to set the data type of individual arguments of a query. In this case, a CHARINDEX function argument is passed as a parameter. Since the ODBC driver does not recognize this data type correctly, we simply change it to CHAR before assigning a value to the parameter. The query itself uses TSQL syntax – it does not need to use the ODBC CALL syntax as it does not execute a parameter-based stored procedure. This example also creates a DSN-less connection to a Microsoft SQL Server and uses the sample Pubs database.

Private Sub Query1_Click()
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim qd As New rdoQuery
Dim cl As rdoColumn
Const None As String = ""

cn.Connect = "uid=;pwd=;server=SEQUEL;" _
    & "driver={SQL Server};database=pubs;" _
    & "DSN='';"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt

Set qd.ActiveConnection = cn
qd.SQL = "Select * From Titles" _
    & " Where CharIndex( ?, Title) > 0"
    
qd(0).Type = rdTypeCHAR
qd(0) = InputBox("Enter search string", , "C")

Set rs = qd.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

For Each cl In rs.rdoColumns
    Debug.Print cl.Name,
Next
Debug.Print

Do Until rs.EOF
    For Each cl In rs.rdoColumns
        Debug.Print cl.Value,
    Next
    rs.MoveNext
Debug.Print
Loop
End Sub