DataAdapter And Locking

[Skittle]

I am a little confused on the appropriate strategy for locking rows in a database when an application has read them in for a user to modify before updating.

If I use a DataAdapter the default isolation level is ReadOptimistic.
When the dataAdapter attempts to update the rows, some could fail because another application has updated them since the data was read.

How can I set the isolation level to pessimistic and prevent other applications updating the rows until my application releases them via MyAdapter.Update(MyDataSet). I guess I am asking how to lock records for exclusive access while a DataAdapter presents rows to a user to edit until the MyAdapter.Update(MYdataSet) is executed.





Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

I found the answer to this on a website posting from 2003.
So all resolved.

Dim txn As SqlTransaction

Private Sub cmdFill_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdFill.Click
   SqlConnection1.Open()
   txn = SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead)
   SqlDataAdapter1.SelectCommand.Transaction = txn
   SqlDataAdapter1.Fill(DsCustomers1)
End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdUpdate.Click
   SqlDataAdapter1.InsertCommand.Transaction = txn
   SqlDataAdapter1.UpdateCommand.Transaction = txn
   SqlDataAdapter1.DeleteCommand.Transaction = txn
   SqlDataAdapter1.Update(DsCustomers1)
   txn.Commit()
   SqlConnection1.Close()
End Sub

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.