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

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.

0
BBrian
Asked:
BBrian
1 Solution
 
MarineCommented:
0
 
BBrianAuthor Commented:
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.
0
 
vndCommented:
Brian can you post some code how you connect to database and what you do with your recordset ? or is it too much ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BBrianAuthor Commented:

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


0
 
BBrianAuthor Commented:
OOPS, hit submit by mistake

Forget the code after loop...


RstDemoGrapics.Close
Set RstDemoGrapics = Nothing

That's the gist of the recordset

0
 
MarineCommented:
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.
0
 
MarineCommented:
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


       
0
 
BBrianAuthor Commented:
I thought the connection closed when opened with the recordset this way.  How do you close the connection?
0
 
MarineCommented:
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

0
 
MarineCommented:
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
0
 
BBrianAuthor Commented:
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?
0
 
MarineCommented:
to be honest i don't know how it closes on it's own. did my comment help?
0
 
BBrianAuthor Commented:
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.
0
 
MarineCommented:
if the comment that i posted with these changes worked i would think that the problem is gone. I use this method in my project and seem to have no problems. The way you open connection is correct i think also but i don't think that it gets killed after you close recordset. and i don't konw how to test if its killed or not unless i use the variable. You can check to see if your connectino is alive or not by using if cn.State = adStateOpen then do something. But i don't know how to test it how you have declared. ALl the best Brian.
0
 
BBrianAuthor Commented:
Thanks for your help
0
 
scooby6550Commented:
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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