Catastrophic failure (Urgent!!)

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?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

gencrossConnect With a Mentor Commented:
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.

in some database lock strategy, you must do open and update between BeginTrans and CommitTrans.
Alon HirschSoftware Development ManagerCommented:

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 ?

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

cwteohAuthor Commented:
   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
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
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.
Richie_SimonettiIT OperationsCommented:
cwteohAuthor Commented:
thanks a lot ... it helps
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.