Solved

Catastrophic failure (Urgent!!)

Posted on 2002-07-09
8
972 Views
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?

0
Comment
Question by:cwteoh
8 Comments
 
LVL 4

Expert Comment

by:TigerZhao
Comment Utility
in some database lock strategy, you must do open and update between BeginTrans and CommitTrans.
0
 
LVL 4

Expert Comment

by:AlonHirsch
Comment Utility
Hi,

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 ?

HTH,
Alon
0
 
LVL 1

Author Comment

by:cwteoh
Comment Utility
   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
0
 
LVL 4

Accepted Solution

by:
gencross earned 100 total points
Comment Utility
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
-------------------------------------------------------------------------------
 
SYMPTOMS
========
 
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
 
   -or-
 
   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.
 
CAUSE
=====
 
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
provider.
 
The Cursor Engine, however, does preserve cursors.
 
RESOLUTION
==========
 
Use adUseClient or set the following RecordSet properties to preserve the
cursor:
 
   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.
 
STATUS
======
 
This behavior is by design.
 
MORE INFORMATION
================
 
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
 
      cn.BeginTrans
     rst.Open "select * from Test1", , adOpenStatic, adLockOptimistic
     Debug.Print rst(0)
     cn.RollbackTrans
     ' 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.


 
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:JimMaguire
Comment Utility
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
           .MoveLast
           .MoveFirst
           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
     .MovePrevious
  Else
     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
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
listening...
0
 
LVL 1

Author Comment

by:cwteoh
Comment Utility
thanks a lot ... it helps
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

11 Experts available now in Live!

Get 1:1 Help Now