Table Adapters

[Mr.Wobbles]

I have code that opens and closes the connection to the database via table adapters every time I update because it was throwing concurrency and other database exceptions left and right. Now, if I try to navigate through the records it throws concurrency exceptions? I can add, edit, delete records just fine now but just navigating is a problem.

To give an idea what I have done to correct concurrency errors I have pasted a bit of code below.

Try
   Me.TableAdapter.Connection.Open()
   Me.TableAdapter.Update(Me.PortfolioDBDataSet.Table)
   Catch dbcx As Data.DBConcurrencyException
     Dim response As Windows.Forms.DialogResult
     response = MessageBox.Show("Concurrency Exception")
   Catch ex As Exception
     MsgBox(ex)
   Finally
     Me.TableAdapter.Connection.Close()
End Try

Using VB 2005 Express and Microsoft Access 2003. Any other information that you need to know just ask. Thanks for any help!

[PVBert]

Maybe you should try a VB.NET forum.

[Mr.Wobbles]

I will, thank you

[AV Manoharan]

I will, thank you

Cocurrancy control is a major issue in Database programming. Are you in a multuser environment?. If not your own adding and deleting is throwing this excepition.

You have to declare your cursor to be optimistic so that it take care of a dynamic update.

see to it and get back to me.

[Mr.Wobbles]

How would I declare the cursor to be optimistic? I have basically taught myself a lot of the stuff I have done, using forums and such, but I can't seem to find anything on making the cursor optimistic. Any ideas?

[AV Manoharan]

A cursor has four types of lock properties.

adLockReadonly -------- for read only you cannot update the rows

adLockPessimistic........ This lock is the most strict form of concurrency. The row is locked. Other users can read but can't edit. The lock is to be explictly released by the application.

adLockOptimistic......... This is the most common way of locking. The database automatically place a lock when it updates the concerned row. It assumes that two users very seldom update a row instantly.

adLockBatchOptimistic... When a bunch of records are down loaded for a particular user, the server disconect the user. The user in his own time update it and establish a new connection to the database to make it committed.

choose the best out of this.

[QVeen72]

Hello Manoharan,

Mr. Wobbles is Using VB2005 and Table Adapters(ADO.NET).. And u r Telling him about VB6/ADO..

TableAdapters dont support "Cursors"

REgards
Veena

[AV Manoharan]

See, I have used the common word as cursor. It need not be actually a cursor. Any table in any database is associated with theses properties, Otherwise, it will be chaos Veena, when you try to access it and modify.

Along with the particular table adapter connection open

Me.TableAdapter.Connection.Open()

you have to set what type of adapter you are going to use on the table.

then only you can use

Me.TableAdapter.Update(Me.PortfolioDBDataSet.Table)

Otherwise concurrency exception it will throw out.

[Mr.Wobbles]

THis is an example of what happens when a user hits the save button. Similar things happen for add and the delete button as well.

Private Sub CRS_AccomplishBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CRS_AccomplishBindingNavigatorSaveItem.Click
  Try
    If (Not (ConnectionState.Executing)) Then
      Me.CRS_AccomplishTableAdapter.Connection.Open()
      Me.CRS_AccomplishTableAdapter.Update
      Me.PortfolioDBDataSet.CRS_Accomplish)
      MsgBox("Update Successful")
    Else
      MsgBox("The Connection is currently in use, please wait a 
      moment and try again")
    End If
  Catch dbcx As Data.DBConcurrencyException
    Dim response As Windows.Forms.DialogResult
    response = MsgBox("Concurrency Exception", 
    MessageBoxButtons.OK)
  Catch ex As Exception
    MsgBox("An error was thrown while trying to update the 
    database")
  Finally
    Me.CRS_AccomplishTableAdapter.Connection.Close()
  End Try
End Sub

Now, I added the open stuff and it seems to only throw an exception when I rapidly add and then delete a lot of records. I don't think that should be a major problem, but I don't know if it is the rapidly or the a lot part that is screwing it up.