DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Table Adapters

•

Join Date: Jun 2007

Posts: 49

Reputation: Mr.Wobbles is an unknown quantity at this point

Solved Threads: 2

Mr.Wobbles Mr.Wobbles is offline

Offline

Light Poster

Table Adapters

Jun 14th, 2007

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.

 Help with Code Tags 
 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.

Mr.Wobbles~

•

Join Date: Mar 2007

Posts: 59

Reputation: PVBert is an unknown quantity at this point

Solved Threads: 5

PVBert

Offline

Junior Poster in Training

Re: Table Adapters

Jun 14th, 2007

Maybe you should try a VB.NET forum.

•

Join Date: Jun 2007

Posts: 49

Reputation: Mr.Wobbles is an unknown quantity at this point

Solved Threads: 2

Mr.Wobbles Mr.Wobbles is offline

Offline

Light Poster

Re: Table Adapters

Jun 14th, 2007

I will, thank you

Mr.Wobbles~

•

Join Date: Jun 2007

Posts: 166

Reputation: AV Manoharan is an unknown quantity at this point

Solved Threads: 9

AV Manoharan AV Manoharan is offline

Offline

Junior Poster

Re: Table Adapters

Jun 15th, 2007

•

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.

•

Join Date: Jun 2007

Posts: 49

Reputation: Mr.Wobbles is an unknown quantity at this point

Solved Threads: 2

Mr.Wobbles Mr.Wobbles is offline

Offline

Light Poster

Re: Table Adapters

Jun 19th, 2007

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~

•

Join Date: Jun 2007

Posts: 166

Reputation: AV Manoharan is an unknown quantity at this point

Solved Threads: 9

AV Manoharan AV Manoharan is offline

Offline

Junior Poster

Re: Table Adapters

Jun 20th, 2007

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.

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: Table Adapters

Jun 20th, 2007

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

•

Join Date: Jun 2007

Posts: 166

Reputation: AV Manoharan is an unknown quantity at this point

Solved Threads: 9

AV Manoharan AV Manoharan is offline

Offline

Junior Poster

Re: Table Adapters

Jun 20th, 2007

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.

•

Join Date: Jun 2007

Posts: 49

Reputation: Mr.Wobbles is an unknown quantity at this point

Solved Threads: 2

Mr.Wobbles Mr.Wobbles is offline

Offline

Light Poster

Re: Table Adapters

Jun 21st, 2007

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.

 Help with Code Tags 
 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~