cwteoh
asked on
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?
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?
in some database lock strategy, you must do open and update between BeginTrans and CommitTrans.
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
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
ASKER
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
& " 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
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.
listening...
ASKER
thanks a lot ... it helps