Solved

Ado Catastrophic Failure

Posted on 2000-05-05
16
324 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

896 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

16 Experts available now in Live!

Get 1:1 Help Now