PRB: RDO: Hstmt Error with Asynchronous SQL Server Queries (143032)

MORE INFORMATION

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add two command buttons to Form1.
3. Paste the following code into the General Declarations section of form1.

      Private Sub Command1_Click()
        'The following code will cause the error:
        Dim en As rdoEnvironment
        Dim cn As rdoConnection
        Dim rs1 As rdoResultset
        Dim rs2 As rdoResultset
        Dim sql1 As String
        Dim sql2 As String
   
        'establish connection
        Set en = rdoEngine.rdoEnvironments(0)
        en.CursorDriver = rdUseOdbc
        'this should be modified to connect to your database
        Dim cnStr As String
        cnStr = "driver={SQL Server};server=myserver;" & _
          "database=pubs;uid=myuid;pwd=mypwd"
        Set cn = en.OpenConnection(dsname:="", Prompt:=rdDriverNoPrompt, _
          Connect:=cnStr)
   
        'create SQL statements that take at least a few seconds to finish
        sql1 = "Select title From Titles"
        Set rs1 = cn.OpenResultset(Name:=sql1, Type:=rdOpenStatic, _
          Option:=rdAsyncEnable)
   
        sql2 = "Select au_id From Authors"
        ' The next line will cause the error:
        ' "Connection is busy with results for another hstmt"
        Set rs2 = cn.OpenResultset(Name:=sql2, Type:=rdOpenStatic, _
          Option:=rdAsyncEnable)
   
        While rs1.StillExecuting Or rs2.StillExecuting
          DoEvents
        Wend
        MsgBox "rs1 and rs1 have both completed"
      End Sub
   
      Private Sub Command2_Click()
        'The following code will not cause the error:
        Dim en As rdoEnvironment
        Dim cn1 As rdoConnection
        Dim cn2 As rdoConnection  'a second connection has been added
        Dim rs1 As rdoResultset
        Dim rs2 As rdoResultset
        Dim sql1 As String
        Dim sql2 As String
   
        'establish connection
        Set en = rdoEngine.rdoEnvironments(0)
        en.CursorDriver = rdUseOdbc
        'this should be modified to connect to your database
        Dim cnStr As String
        cnStr = "driver={SQL Server};server=myserver;" & _
          "database=pubs;uid=myuid;pwd=mypwd"
        Set cn1 = en.OpenConnection(dsname:="", Prompt:=rdDriverNoPrompt, _
          Connect:=cnStr)
        Set cn2 = en.OpenConnection(dsname:="", Prompt:=rdDriverNoPrompt, _
          Connect:=cnStr)
   
        'create SQL statements that take at least a few seconds to finish
        sql1 = "Select title From Titles"
        Set rs1 = cn1.OpenResultset(Name:=sql1, Type:=rdOpenStatic, _
          Option:=rdAsyncEnable)
   
        sql2 = "Select au_id From Authors"
        Set rs2 = cn2.OpenResultset(Name:=sql2, Type:=rdOpenStatic, _
          Option:=rdAsyncEnable)
   
        While rs1.StillExecuting Or rs2.StillExecuting
          DoEvents
        Wend
        MsgBox "rs1 and rs1 have both completed"
      End Sub
   
   						

4. Note that you will need to change your DRIVER, SERVER, DATABASE, UID, and PWD in the OpenConnection method. You will also need to modify the SQL statement contained in the Command1_Click event to match your own SQL data source.
5. Clicking on Command1 will cause the error: "Connection is busy with results for another hstmt." This is because you are trying to simultaneously execute a second query on the same rdoConnection. Clicking on Command2 will successfully execute each query simultaneously because they are each on a separate rdoConnection.