rdoResultset Object, rdoResultsets Collection Example

The following example illustrates execution of a multiple result set query. While this query uses three SELECT statements, only two return rows to your application. The subquery used instead of a join does not pass rows outside the scope of the query itself. This is also an example of a simple parameter query that concatenates the arguments instead of using an rdoQuery to manage the query. The OpenResultset also runs asynchronously – the code checks for completion of the operation by polling the StillExecuting property.

Private Sub ShowResultset_Click()
Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String
Const None As String = ""

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

SQL = "Select Au_Lname, Au_Fname" _
    & " From Authors A" _
    & " Where Au_ID in " _
    & " (Select Au_ID" _
    & "     from TitleAuthor TA, Titles T" _
    & "     Where TA.Au_ID = A.Au_ID" _
    & "     And TA.Title_ID = T.Title_ID " _
    & "     And T.Title Like '" _
    & InputBox("Enter search string", , "C") & "%')" _
    & "Select * From Titles Where price > 10"

Set rs = cn.OpenResultset(SQL, rdOpenKeyset, _
    rdConcurReadOnly, rdAsyncEnable + rdExecDirect)
    
Debug.Print "Executing ";
While rs.StillExecuting
    Debug.Print ".";
    DoEvents
Wend

Do
    Debug.Print String(50, "-")  _
   & "Processing Result Set " & String(50, "-")
    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
    Debug.Print "Row count="; rs.RowCount
    
Loop Until rs.MoreResults = False
End Sub