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.
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.
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.
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 ?
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
strSourcePersonaCounter = "SELECT * From PersonaCounter"
RstPersonaCounter.CursorLo
stPersonaCounter.Open strSourcePersonaCounter, strConnect, adOpenKeyset, adLockOptimistic
RstPersonaCounter.MoveFirs
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
strConnect = "DSN=Falcon;UID=admin;PWD=
'DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE
RstDemoGrapics.CursorLocat
RstDemoGrapics.Open strSourceDemoGraphics, strConnect, adOpenKeyset, adLockOptimistic
RstDemoGrapics.MoveFirst
Do Until RstDemoGrapics.EOF
strUserID = RstDemoGrapics!
UserIDRstDemoGrapics.MoveN
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
ASKER
OOPS, hit submit by mistake
Forget the code after loop...
RstDemoGrapics.Close
Set RstDemoGrapics = Nothing
That's the gist of the recordset
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.
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;Que ryLog_On=Y es;StatsLo g_On=Yes;T rusted_str Connection =Yes
RstDemoGrapics.CursorLocat ion = adUseClient
RstDemoGrapics.Open strSourceDemoGraphics, strConnect, adOpenKeyset, adLockOptimistic
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
strConnect = "DSN=Falcon;UID=admin;PWD=
'DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE
RstDemoGrapics.CursorLocat
RstDemoGrapics.Open strSourceDemoGraphics, strConnect, adOpenKeyset, adLockOptimistic
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;Que ryLog_On=Y es;StatsLo g_On=Yes;T rusted_str Connection =Yes"
Cn.Open strConnect
RstDemoGrapics.Open "YourSQLQuery",cn,adOpenKe yset,adOpe nLockOptim istic
To close connection you would do
cn.close
Dim cn as new adodb.Connection
Dim strConnect as string
strConnect = "DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE
Cn.Open strConnect
RstDemoGrapics.Open "YourSQLQuery",cn,adOpenKe
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;Que ryLog_On=Y es;StatsLo g_On=Yes;T rusted_str Connection =Yes"
cn.Open strConnect
strSourcePersonaCounter = "SELECT * From PersonaCounter"
RstPersonaCounter.CursorLo cation = adUseClient
stPersonaCounter.Open strSourcePersonaCounter, cn, adOpenKeyset, adLockOptimistic
RstPersonaCounter.MoveFirs t
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.CursorLocat ion = adUseClient
RstDemoGrapics.Open strSourceDemoGraphics, cn, adOpenKeyset, adLockOptimistic
RstDemoGrapics.MoveFirst
Do Until RstDemoGrapics.EOF
strUserID = RstDemoGrapics!
UserIDRstDemoGrapics.MoveN ext
blnErrorUserData = False
Loop
Dim cn as new adodb.Connection
strConnect = "DSN=Falcon;APP=Visual Basic;WSID=BRIANM;DATABASE
cn.Open strConnect
strSourcePersonaCounter = "SELECT * From PersonaCounter"
RstPersonaCounter.CursorLo
stPersonaCounter.Open strSourcePersonaCounter, cn, adOpenKeyset, adLockOptimistic
RstPersonaCounter.MoveFirs
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
RstDemoGrapics.CursorLocat
RstDemoGrapics.Open strSourceDemoGraphics, cn, adOpenKeyset, adLockOptimistic
RstDemoGrapics.MoveFirst
Do Until RstDemoGrapics.EOF
strUserID = RstDemoGrapics!
UserIDRstDemoGrapics.MoveN
blnErrorUserData = False
Loop
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?
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
http://support.microsoft.com/support/kb/articles/Q187/9/42.ASP