Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 2622

Jun 14th, 2007

Table Adapters

Expand Post »

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.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 
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
 
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!

Last edited by Mr.Wobbles; Jun 14th, 2007 at 11:51 am.

Similar Threads

Table Adapters in VB.NET

Mr.Wobbles

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

49 posts
since Jun 2007

Permalink

Jun 14th, 2007

Re: Table Adapters

Maybe you should try a VB.NET forum.

PVBert

Reputation Points: 10

Solved Threads: 5

Junior Poster in Training

Offline

60 posts
since Mar 2007

Permalink

Jun 14th, 2007

Re: Table Adapters

I will, thank you

Mr.Wobbles

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

49 posts
since Jun 2007

Permalink

Jun 15th, 2007

Re: Table Adapters

Click to Expand / Collapse Quote originally posted by Mr.Wobbles ...

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.

AV Manoharan

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

166 posts
since Jun 2007

Permalink

Jun 19th, 2007

Re: Table Adapters

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?

Mr.Wobbles

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

49 posts
since Jun 2007

Permalink

Jun 20th, 2007

Re: Table Adapters

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.

AV Manoharan

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

166 posts
since Jun 2007

Permalink

Jun 20th, 2007

Re: Table Adapters

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

QVeen72

Reputation Points: 84

Solved Threads: 140

Posting Shark

Offline

923 posts
since Nov 2006

Permalink

Jun 20th, 2007

Re: Table Adapters

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.

AV Manoharan

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

166 posts
since Jun 2007

Permalink

Jun 21st, 2007

Re: Table Adapters

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.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 
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
 
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.

Last edited by Mr.Wobbles; Jun 21st, 2007 at 12:53 pm.

Mr.Wobbles

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

49 posts
since Jun 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Relative path problems

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Toolbox Help

DaniWeb Message

Cancel Changes

User Name
Password