• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

Database recordset error

Below is a function I am using to populate two Data controls recordset.
-----------------------------------------------------
Private Function populateCombos()

Dim sql1 As String
Dim sql2 As String

On Error GoTo ErrorHandler

    sql1 = "execute proc1"
    sql2 = "execute proc2"
   
    If ConnectToDB Then
        Set dat1.Recordset = dbMain.OpenRecordset(sql1, dbOpenDynaset, dbSQLPassThrough)
        Set dat2.Recordset = dbMain.OpenRecordset(sql2, dbOpenDynaset, dbSQLPassThrough)
        populateCombos = True
    Else
        msgbox "connection to database failed"
        populateCombos = False
    End If
   
    Exit Function

ErrorHandler:
    populateCombos = False

End Function
-------------------------------------------------

However, the second recordset open fails. The error msg says that the "Connection is busy with results for another htsmt"

I found somewhere that says this is due to the first recordset is still being worked on. However, I used this format of code before without problems. The difference this time is that I have not linked a grid to the Data Control.

I want to avoid opening multiple connections as I do not require ability to write.

Many thanks
0
2000bug
Asked:
2000bug
1 Solution
 
p_sieCommented:
MAybe place a doEvents between the to database-calls?

 Set dat1.Recordset = dbMain.OpenRecordset(sql1, dbOpenDynaset, dbSQLPassThrough)
 DoEvents
 Set dat2.Recordset = dbMain.OpenRecordset(sql2, dbOpenDynaset, dbSQLPassThrough)
0
 
EDDYKTCommented:
You may want to check the state bvefore


ie

dbMain.state


Constant Description
adStateClosed       Default. Indicates that the object is closed.
adStateOpen          Indicates that the object is open.
adStateConnecting Indicates that the Recordset object is connecting.
adStateExecuting    Indicates that the Recordset object is executing a command.
adStateFetching      Indicates that the rows of the Recordset object are being fetched.
0
 
Arthur_WoodCommented:
to clarify what EDDYKT suggested, change your code like this (from this:

    If ConnectToDB Then
        Set dat1.Recordset = dbMain.OpenRecordset(sql1, dbOpenDynaset, dbSQLPassThrough)
        Set dat2.Recordset = dbMain.OpenRecordset(sql2, dbOpenDynaset, dbSQLPassThrough)
        populateCombos = True
    Else
        msgbox "connection to database failed"
        populateCombos = False
    End If

to:

    If ConnectToDB Then
        Set dat1.Recordset = dbMain.OpenRecordset(sql1, dbOpenDynaset, dbSQLPassThrough)

        ' wait until the first recordset is filled, before proceeding to second recordset
        Do While dbMain.State = adStateFetching
            DoEvents
        Loop

        Set dat2.Recordset = dbMain.OpenRecordset(sql2, dbOpenDynaset, dbSQLPassThrough)

        ' wait until the sedond recordset is filled, before proceeding
        Do While dbMain.State = adStateFetching
            DoEvents
        Loop

        populateCombos = True
    Else
        msgbox "connection to database failed"
        populateCombos = False
    End If

the second 'wait loop' is possible un-necessary, but it is better to be safe than sorry.

AW
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
2000bugAuthor Commented:
theres no dbMain.state

dbMain is a Database object
0
 
2000bugAuthor Commented:
I have resolved the issue.

The data was not 'flushed' out of the recordset. That is why it worked when I had a grid attached to the Data Control as it did that for me.

I simply had to do a MoveFirst MoveLast manually.

Thanks for your comments.

I shall request for a delete.
0
 
PashaModCommented:
Closed, 250 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now