Link to home
Start Free TrialLog in
Avatar of BBrian
BBrian

asked on

Ado Catastrophic Failure

I'm using ado to open SQL Server tables.
I open the recordset, spin throught the records, close the recordset and set it to nothing.

But when a do this action a few times is a row, I get Run-Time error '-2147418113(8000ffff)'.

I thought may be it is creating multiple recordset objects.  But I am closing them and setting them to nothing.

Avatar of Marine
Marine

Avatar of BBrian

ASKER

Thanks, it seems like the same thing.
But I'm not using commit or rollback transaction.  I tried setting the cursor location to adUseClient but I still get the same error.
Brian can you post some code how you connect to database and what you do with your recordset ? or is it too much ?
Avatar of BBrian

ASKER


Sorry, posting the code here is not pretty.  But Here it is.  I might add that the problem seems to happen only sometimes.



Static intDetailCount As Integer
strConnect = "DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE=lease;QueryLog_On=Yes;StatsLog_On=Yes;Trusted_strConnection=Yes"

strSourcePersonaCounter = "SELECT * From PersonaCounter"

RstPersonaCounter.CursorLocation = adUseClient

 stPersonaCounter.Open strSourcePersonaCounter, strConnect, adOpenKeyset, adLockOptimistic


RstPersonaCounter.MoveFirst

If RstPersonaCounter!Counter = 9999 Then

    RstPersonaCounter!Counter = 0
   
End If

RstPersonaCounter!Counter = RstPersonaCounter!Counter + 1
 RstPersonaCounter.Update
   
strFileSequenceNum = RstPersonaCounter!Counter

RstPersonaCounter.Close

Set RstPersonaCounter = Nothing



strSourceDemoGraphics = "SELECT * " _
& " FROM ExpressUsers LEFT OUTER JOIN" _
& " Persona ON" _
& " ExpressUsers.User_identity = Persona.ExpressUserId WHERE (ExpressUsers.access_level = 30)"
strConnect = "DSN=Falcon;UID=admin;PWD=;"
'DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE=lease;QueryLog_On=Yes;StatsLog_On=Yes;Trusted_strConnection=Yes
RstDemoGrapics.CursorLocation = adUseClient
RstDemoGrapics.Open strSourceDemoGraphics, strConnect, adOpenKeyset, adLockOptimistic


       
RstDemoGrapics.MoveFirst

    Do Until RstDemoGrapics.EOF
   


           
               
  strUserID = RstDemoGrapics!
UserIDRstDemoGrapics.MoveNext
       
        blnErrorUserData = False
   
    Loop
   
Print #f, "0001"; ","; strFileSequenceNum; ","; strBatchSequenceNum; ","; strDetailCounter; ","; "0"; ","; "0"; ","; "0"; ","; "PTS"; ","  'BATCH TRAILER RECORD FORMAT`
Print #f, "2000"; ","; strFileSequenceNum; ","; "1"; ","; strDetailCounter; ","; "0" 'FILE HEADER RECORD FORMAT`

Close #f


Avatar of BBrian

ASKER

OOPS, hit submit by mistake

Forget the code after loop...


RstDemoGrapics.Close
Set RstDemoGrapics = Nothing

That's the gist of the recordset

ok i think the problem is here
when the first time you create a recordset you open connection. Then you close connection. But then you reopen recordset and you open connection again. But you haven't closed the connection so that casuses the error. After you close the recordset the first time Close the connection as well. Then al lshould work.
see where you close recordset ?

RstPersonaCounter.Close

but you haven't closed connection. Then you reopen the recordset here. And connection again. I think that will cause error there. Close connection wher eyou close recordset or simply just indicate same connectoin.

strSourceDemoGraphics = "SELECT * " _
& " FROM ExpressUsers LEFT OUTER JOIN" _
& " Persona ON" _
& " ExpressUsers.User_identity = Persona.ExpressUserId WHERE (ExpressUsers.access_level = 30)"
strConnect = "DSN=Falcon;UID=admin;PWD=;"
'DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE=lease;QueryLog_On=Yes;StatsLog_On=Yes;Trusted_strConnection=Yes
RstDemoGrapics.CursorLocation = adUseClient
RstDemoGrapics.Open strSourceDemoGraphics, strConnect, adOpenKeyset, adLockOptimistic


       
Avatar of BBrian

ASKER

I thought the connection closed when opened with the recordset this way.  How do you close the connection?
Brian can you declare it like this ?
Dim cn as new adodb.Connection
Dim strConnect as string
strConnect = "DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE=lease;QueryLog_On=Yes;StatsLog_On=Yes;Trusted_strConnection=Yes"
Cn.Open strConnect

RstDemoGrapics.Open "YourSQLQuery",cn,adOpenKeyset,adOpenLockOptimistic

To close connection you would do
cn.close

Static intDetailCount As Integer
Dim cn as new adodb.Connection
strConnect = "DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE=lease;QueryLog_On=Yes;StatsLog_On=Yes;Trusted_strConnection=Yes"
cn.Open strConnect
strSourcePersonaCounter = "SELECT * From PersonaCounter"

RstPersonaCounter.CursorLocation = adUseClient

 stPersonaCounter.Open strSourcePersonaCounter, cn, adOpenKeyset, adLockOptimistic


RstPersonaCounter.MoveFirst

If RstPersonaCounter!Counter = 9999 Then

    RstPersonaCounter!Counter = 0
     
End If

RstPersonaCounter!Counter = RstPersonaCounter!Counter + 1
 RstPersonaCounter.Update
     
strFileSequenceNum = RstPersonaCounter!Counter

RstPersonaCounter.Close
Cn.Close
Set RstPersonaCounter = Nothing



strSourceDemoGraphics = "SELECT * " _
& " FROM ExpressUsers LEFT OUTER JOIN" _
& " Persona ON" _
& " ExpressUsers.User_identity = Persona.ExpressUserId WHERE (ExpressUsers.access_level = 30)"
RstDemoGrapics.CursorLocation = adUseClient
RstDemoGrapics.Open strSourceDemoGraphics, cn, adOpenKeyset, adLockOptimistic


       
RstDemoGrapics.MoveFirst

    Do Until RstDemoGrapics.EOF
     


             
                 
  strUserID = RstDemoGrapics!
UserIDRstDemoGrapics.MoveNext
         
        blnErrorUserData = False
     
    Loop
Avatar of BBrian

ASKER

OK
But according to documentation
you don't need to declare a connection object when you use the open recordset method to connect.  I think it should close on default.  Shouldn't it?
to be honest i don't know how it closes on it's own. did my comment help?
Avatar of BBrian

ASKER

Yes thank you very much for your feed back.  But the problem is intermittent so I'm not sure it has gone.  it's hard to trouble shoot or know when the problem is fixed when you can't reproduct the error at will.
ASKER CERTIFIED SOLUTION
Avatar of Marine
Marine

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BBrian

ASKER

Thanks for your help
I had an error like that and it was caused by having a null value in the db.  If you can put a dummy value in there instead of a null it should fix the problem.  It's noted as a gliche w/ADO.