Solved

Ado Catastrophic Failure

Posted on 2000-05-05
16
322 Views
Last Modified: 2013-11-23
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
Comment
Question by:BBrian
16 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2782432
0
 

Author Comment

by:BBrian
ID: 2782680
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
 

Expert Comment

by:vnd
ID: 2782699
Brian can you post some code how you connect to database and what you do with your recordset ? or is it too much ?
0
 

Author Comment

by:BBrian
ID: 2782783

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
 

Author Comment

by:BBrian
ID: 2782789
OOPS, hit submit by mistake

Forget the code after loop...


RstDemoGrapics.Close
Set RstDemoGrapics = Nothing

That's the gist of the recordset

0
 
LVL 6

Expert Comment

by:Marine
ID: 2782796
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
 
LVL 6

Expert Comment

by:Marine
ID: 2782804
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
 

Author Comment

by:BBrian
ID: 2782806
I thought the connection closed when opened with the recordset this way.  How do you close the connection?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 6

Expert Comment

by:Marine
ID: 2782825
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
 
LVL 6

Expert Comment

by:Marine
ID: 2782830
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
 

Author Comment

by:BBrian
ID: 2782869
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
 
LVL 6

Expert Comment

by:Marine
ID: 2782882
to be honest i don't know how it closes on it's own. did my comment help?
0
 

Author Comment

by:BBrian
ID: 2782899
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
 
LVL 6

Accepted Solution

by:
Marine earned 200 total points
ID: 2782911
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
 

Author Comment

by:BBrian
ID: 2793567
Thanks for your help
0
 

Expert Comment

by:scooby6550
ID: 4266113
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now