Catastrophic failure (Urgent!!)

Posted on 2002-07-09
Last Modified: 2007-12-19
I never found this issue before... but since I start my developmennt yesterday in the office, I found this issue while I was trying to do so QA test on my project.
Here is the story :-

in VB, I open a recordset named "rs1" connected to the database for data view using rs1.MoveNext and rs1.MoveFirst. then I try to update one of the data using BeginTrans,Execute and CommitTrans.

At this point, the rs1 no longer work and I got an error message "Catastrophic failure". Why?

rs1.BOF and rs1.EOF both become "TRUE", how can it be happened?

Question by:cwteoh

Expert Comment

ID: 7142500
in some database lock strategy, you must do open and update between BeginTrans and CommitTrans.

Expert Comment

ID: 7142577

Depending on how you have opened the recordset and what the Execute updates, the data in the recordset might no longer be valid.
If you are using ADO, simply do rs1.Requery to recreate the recordset based on the same query criteria.
If you are updating a single record in the recordset, why not simply use rs("Field") = Value and the rs.Update ?


Author Comment

ID: 7142604
   ven_query = "SELECT distinct a.compid as vendor_id,b.comp_name as vendor_name" _
            & " from " & dbschema & "comp_type_rel a," _
            & dbschema & "addressbook b" _
            & " where b.compid=a.compid" _
            & " and a.typeid ='VEN'"

    ven_rs.Open ven_query, Conn, adOpenStatic, adLockReadOnly, adCmdText

Accepted Solution

gencross earned 100 total points
ID: 7143090
Here is a Microsoft article.  It may help.

PSS ID Number: Q187942
Article last modified on 11-26-2001
:2.0,2.1 SP2,2.5,2.6,2.7,7.01


The information in this article applies to:
 - ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2, 2.5, 2.6, 2.7
- Microsoft OLE DB Provider for SQL Server, version 7.01
Any operation following a rollback or a commit transaction on a recordset opened
as a serverside cursor, triggers one the following errors, depending on the
provider and operating system:
   Run-time error '-2147418113' Catastrophic failure
   Run-time error '-2147418113' Unexpected failure
Using ADO 2.6 and later, the error is Run-time error '-2147418113(8000ffff)':
ITransaction::Commit or ITransaction::Abort was called, and the object is in a
zombie state.
Preserving cursors, or in other words, not closing them, is not the SQL Server
or ANSI SQL default. The OLE DB specification does not specify a default value
for these properties because, this behavior can change from provider to
The Cursor Engine, however, does preserve cursors.
Use adUseClient or set the following RecordSet properties to preserve the
   rs.Properties("Preserve On Commit") = True
  rs.Properties("Preserve On Abort") = True
There are three requirements to have these two properties, or any other preset
properties, take effect on a recordset. The three requirements are:
 - The properties need to be set prior to opening the recordset.
 - Use the Open method to open the recordset. The Connection and Command Execute
  method opens a default Recordset, with all properties preset.
 - The OLE DB provider must support preserving cursors. The OLE DB Provider for
  SQL Server supports preserving cursors on Commit and Abort.
If you use adOpenForwardOnly as a cursor type and adLockReadOnly as a lock type,
setting "Preserve on Commit" to True will not have any effect. You will need to
call Recordset.Requery() after you commit the transaction if any further work
needs to be done on the Recordset.
This behavior is by design.
Steps to Reproduce Behavior
1. Start Visual Basic.
2. Add a reference to the Microsoft ActiveX Data Objects Library.
3. Add the following code to the default form in the project:
      Dim cn As New ADODB.Connection
     Dim rst As New ADODB.Recordset
      cn.Open "provider=SQLOLEDB;data source=<server>;initial " _
     & "catalog=pubs;user id=<user id>;password=<password>"
     ' error handling for non-existent Test1 table
     On Error Resume Next
     cn.Execute "drop table Test1"
     On Error GoTo 0
     cn.Execute "create table Test1(id int primary key, num int)"
      For i = 1 To 10
        cn.Execute "insert into Test1 values(" & i & ", " & i & ")"
     Next i
      Set rst.ActiveConnection = cn
     'Set these properties to True to prevent error.
     'rst.Properties("Preserve On Commit") = True
     'rst.Properties("Preserve On Abort") = True
     rst.Open "select * from Test1", , adOpenStatic, adLockOptimistic
     Debug.Print rst(0)
     ' If the preserve properties are not set, the following fails
     Debug.Print rst(0)
Additional query words:
Keywords          : kbADO200 kbDatabase kbOLEDB kbProvider kbSQLServ kbVBp kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbADO210sp2 kbADO250 kbADO260 kbmdac270 kbado270
Technology        : kbSQLServSearch kbAudDeveloper kbADOsearch kbADO200 kbADO210sp2 kbADO250 kbADO260 kbOLEDBSearch kbOLEDBProvSQLServ701 kbOLEDBProvSearch kbADO270
Version           : :2.0,2.1 SP2,2.5,2.6,2.7,7.01
Issue type        : kbprb
Copyright Microsoft Corporation 2001.

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.


Expert Comment

ID: 7143258
I'm not sure this will solve the problem, but I think this is relevant. The only time that .BOF and .EOF are both true is when there are no records in the Recordset.
At that time, a .MovePrevious or .MoveNext will definitely give you an error, becasue you can't move past those markers (respectively).
So to avoid that, for instance, after you execute command against a database to return rs1, you might do the following:
With rs1
    If Not (.BOF And .EOF) Then   'There is at least one
                                  'record in rs1
           Dim lRecCount As Long
           'proceed with business, eg
           lRecCount = .RecordCount
           ' etc,   ,  ,  ,
    Else        'rs1 has no records
           'msg to user and whatever
    End If
End With

Also, if you are navigating with control buttons of your own, it helps to do something like:

Private Sub cmdPrevious_Click
  If Not .BOF Then
     MsgBox "You Chose 'Previous', But" & vbCr + vbLf _
      & "You Are Presently Viewing the First Record", _
       vbOKOnly + vbInformation, "Information"
  End If
End Sub

Likewise for cmdNext with a check for the .EOF marker

If the problem comes after you update the recordset, you might want to debug that section and see what the status is to give you the (.BOF And .EOF) = True condition.

Hope this was helpful
Jim Maguire
LVL 18

Expert Comment

ID: 7143416
Well, the other relevent point here is that your recordset is actually a join of two tables.  In most cases, ADO/DAO (whatever you are using) will not be able to determine which table you are trying to update.  Your only hope is to ensure that you have the primary key of each table as one of the selected columns in the recordset.  Then, it's possible that you will be allowed to update the fields from one of the tables in the join.

A better approach is to have the keys in the selected recordset, and then to issue SQL Update statements using the Connection or Command objects rather than trying to update the recordset directly.  That way, you can issue separate update statements to the two tables.
LVL 16

Expert Comment

ID: 7144245

Author Comment

ID: 7144967
thanks a lot ... it helps

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

Suggested Solutions

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…

932 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

9 Experts available now in Live!

Get 1:1 Help Now