Testing for deleted record

VBDudley
10-15-2004, 07:39 AM
Hi,

I'm building an app that will run in a multiuser environment.

A possibility is that a user might delete a record while another user is in the process of updating that particular record.

Here's my question.

Is there a way of checking if the record has been deleted other than checking the ERR object for error number -2147217885?

Thanks.

rufen101
10-15-2004, 07:43 AM
Instead, you could lock the record when updating, so another user won't be able to delete the record. This is done with the LockType property of the open method of a Recordset. Check MSDN for LockType.

Demon Cleaner
10-15-2004, 07:47 AM
There are a lot of parameters concerning your question. Which database provider are you going to use (MS Access, MS SQL Server, Oracle, MySQL, etc.), what kind of VB programming (ADO, DAO - I surely suggest ADO), the architecture of your database, etc.

However the general idea is to "lock" one record if a user does some work with it, so that no other user can perform any changes.

Edit:
Sorry, didn't show previous post... :huh:

Norenca
10-15-2004, 07:59 AM
Why don't you just use a select-query that would select the concerning record. If the recordcount = 0 then the record doesn't exist anymore => it has been deleted.

Tip: write a function for this. This function would have an sql-string as incoming parameter ("select * from Table where ID = 12"). Catch this string in your function, execute the statement and return the recordcount. That way, you can use this function several times in your program like this:


strSQL = "select * from table where ID = 12"
NumberOfRecords = CountRecords(strSQL)


It really made my work a lot easier. ;-)

VBDudley
10-15-2004, 08:02 AM
Let me try to explain myself a little better

Here's my situation

The first user accesses record for modification (I display record information in controls on a form for editing purposes).

Before performing my Update in the recordset, I actually verify that no changes whatsoever were made to the record since the user started is editing. If changes were made, a message is displayed and the update is cancelled. This works well except when a second user deletes that record while the first user is in the editing process.

I'm not crazy about the idea of locking the record before starting my editing, since the process could take an indefinite amount of time (gone to lunch or something) before an update is actually made and the record gets released.

rufen101
10-15-2004, 09:38 AM
If you don't want to lock the record during the edition, you could lock it only when it's updating with adLockOptimistic.

Here's how it works.

adLockOptimistic Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.

VBDudley
10-15-2004, 11:41 AM
That is how recordset is setup.

I little bit more info. I use ADVANTAGE OLEDB as my provider to open a FOXPRO table in an ADO recordset. Since it's a FOXPRO table, delete record are actually marked as deleted in the table, but are not physically removed until the table is packed.

The error generated by the deleted record actually happens before the UPDATE method. The error is generated by my function that creates an image of my record.


Public Function GetRecordImage(rsRecordset As Recordset) As String
Dim fldField As Field

GetRecordImage = ""
With rsRecordset
.Resync adAffectCurrent
For Each fldField In .Fields
Select Case fldField.Type
Case adVarChar
If Not IsNull(varField.Value) Then
GetRecordImage = GetRecordImage & varField.Value
End If
Case adNumeric, adInteger, adDouble, adCurrency, adDate, adBoolean
If Not IsNull(varField.Value) Then
GetRecordImage = GetRecordImage & CStr(varField.Value)
End If
End Select
Next
End With

End Function


At the beginning of my editing process, I assign the result of this funtion call to a string variable. The user does his editting taking all the time he needs. When the user clicks the SAVE button, I compare the string variable to the result of a second call to this function.

If both images are identical, the update happens. If not, a message box is displayed warning the user that someone else has made a change, the update is cancelled and the record his displayed with the new information (that's how the logic was before I started modifying for the possibility of the record being deleted).

Now, since the deleted record still physically exists in the table. The resync doesn't generate an error. So accessing the value of a field and generating an error is the only way I could figure to test deletion.

I would like, if possible, to verify this condition without actually generating an error.

VBDudley
10-15-2004, 01:21 PM
Thanks for the help everyone.

I figured out a way to do this without generating an error.

Let's say that when I began my editing process, my AbsolutePosition was 3.
Upon calling my function the second time (and the record is deleted) my AbsolutePosition will be -1 (aka adPosUnkown).

So my function now looks like this (Note: all the varField should have been fldField in the previous post of this function).


Public Function GetRecordImage(rsRecordset As Recordset) As String
Dim fldField As Field

GetRecordImage = ""
With rsRecordset
.Resync adAffectCurrent
If Not .AbsolutePosition = adPosUnknown Then
For Each fldField In .Fields
Select Case fldField.Type
Case adVarChar
If Not IsNull(fldField.Value) Then
GetRecordImage = GetRecordImage & fldField.Value
End If
Case adNumeric, adInteger, adDouble, adCurrency, adDate, adBoolean
If Not IsNull(fldField.Value) Then
GetRecordImage = GetRecordImage & CStr(fldField.Value)
End If
End Select
Next
End If
End With

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum