Multiple Recordsets with One connction

[tfhwargt3]

Hi All,

I am getting a 3705 Error "Operation is not allowed when the object is open" when I run the below code

...
    conSQL.ConnectionString = "Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=C:\Program Files...\Data3.mdf; Database=Data3;Trusted_Connection=Yes;MARS_Connection=yes;"
    conSQL.CursorLocation = adUseClient
    conSQL.Open
    ...
    With cmdNewOrders
        .ActiveConnection = conSQL
        .CommandText = "SELECT All.* FROM All, New WHERE New.[order-id] = All.[order-id];"
        .CommandType = adCmdText
    End With
    
    With rsNewOrders
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdNewOrders
    End With

    If rsNewOrders.EOF = False Then
        rsNewOrders.MoveFirst
        Do
        ...
        With cmdSelBuyers
                .ActiveConnection = conSQL
                .CommandText = "SELECT Buyers.* FROM Buyers WHERE Buyers.Email = '" & buyerEmail & "';"
                .CommandType = adCmdText
            End With
            
            With rsSelBuyers
                [red].CursorType = adOpenStatic[/red]
                .CursorLocation = adUseClient
                .LockType = adLockOptimistic
                .Open cmdSelBuyers
            End With
            
            If rsSelBuyers.EOF = True Then
                ...

I have MARS enabled in my connection string, so I should supposedly be able to open multiple recordsets and compare data in them simultaneously, which is what I am trying to accomplish.

The red aread is where my code halts and throws the error.

I am using VB6 with a SQLSERVER Express Database. Please let me know if you see an error or know why this error is happening.

Thanks to all for any help.

 

[strongm]

I believe that MARS only works with ADO.NET 2.0, not with VB's ADO libraries

 

[strongm]

(but don't quote me on that; it isn't an area I am particularly familiar with)

 

[SBerthold]

your rsSelBuyers recordset appears to be already opened. It needs to be first closed prior to setting this property.

 

[fredericofonseca]

You have ommited part of the code, which means we cant be sure of the issue, but as you have most of your code on a loop, I would say you forgot to close/open the rsSelBuyers while looping on rsNewOrders

    If rsNewOrders.EOF = False Then
        rsNewOrders.MoveFirst
        loop rsNewOrders
        ...
        ...
            With rsSelBuyers
                .CursorType = adOpenStatic
                .CursorLocation = adUseClient
                .LockType = adLockOptimistic
                .Open cmdSelBuyers
            End With
            
            If rsSelBuyers.EOF = False Then
               loop rsSelBuyers
                    ....
               end loop rsSelBuyers
               ...
            end if
            [b]rsSelBuyers.close[/b]
       end loop rsNewOrders
   end if

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886