[Last Call] Learn how to a build a cloud-first strategyRegister Now


Concurrency Violation... seems common question!!

Posted on 2011-10-28
Medium Priority
Last Modified: 2012-05-12
Although this is a common question, I cannot see one solution that helps me...
So here's my specific problem:

I have a module which I use to setup global parameters and load my data - key bits shown here:

    Public dti As New DataTable("dbItems")
    Public dai As New SqlCeDataAdapter
    Public bsi As New BindingSource

Public Sub dbFillData()
        Dim cb As SqlCeCommandBuilder

        Dim connString As String = "Data Source='bmdata.sdf'; LCID=1033;          
Password=12345; Encrypt = TRUE;"

        Dim en As New SqlCeEngine(connString)
        Dim con As New SqlCeConnection
        con.ConnectionString = connString
        Dim cmd As New SqlCeCommand

        dai = New SqlCeDataAdapter("Select * From dbItems", con)
        cb = New SqlCeCommandBuilder(dai)
        dti.PrimaryKey = New DataColumn() {dti.Columns("EAN")}
        dti.TableName = "dbItems"
        bsi.DataSource = dti

End Sub

Now I have two forms.  The first one is used to add a series of rows to this table something like this:

        For each blah in list-of-blahs
                    dr = dti.NewRow
                    dr("Author") = storeAuthor
                    dr("Title") = storeTitle

        Dim dt As DataTable = dti.GetChanges

The second form allows for individual modification of individual records (and to add new individual records).  So something like this:

            dr = dti.Rows.Find(txtAuthor.Text)
            If dr Is Nothing Then
              dr = dti.NewRow
              dr("Author") = Me.txtAuthor.Text
              dr("Title") = Me.txtTitle.Text
              dr("Author") = Me.txtAuthor.Text
              dr("Title") = Me.txtTitle.Text
            end if
        Dim dt As DataTable = dti.GetChanges

Firstly, I've started using bindingsource.endedit because it seems to mark the row as changed ... I think this is right but there does not seem to be a beginedit for bindingsources???

Secondly, I got this all to work by refilling the table with dai.fill(dti) after both acceptchanges but that means that after every update I have to reload the database????   Well, I don't think that can be right!!

I've read a little about using .merge to merge the changes into the original datatable.  I tried it but it did not help.

This is a single user application so there is no chance of the data being mucked up by some-one else.  ... I'm doing a pretty good job all on my own!

Should I be sharing datatables across multiple forms?  What's best practice??

My platform is VB.NET 2005 with SQL CE 2005

Thanks in advance for your help!

Question by:MikeDFarrant
  • 2

Accepted Solution

djon2003 earned 2000 total points
ID: 37048593
A better practice would be to remove the dataset/datatable from the form's code and to put them into your own class(es). Then, you use the(se) class(es) to change and transfer data from/between forms.

Author Comment

ID: 37049385
Sorry, if I'm being thick but I'm not sure that I understand.

The datatables are created in a module (ok not a class).  When I reference the dti table in the above example I am referencing the one that is setup in the module - not one defined in any of the forms...  I thought that this would mean all my forms were looking at the same detatched instance of my tables thus no concurrency issues.

Perhaps my reply shows that I am a bit of a novice in this area.

Author Comment

ID: 37049967
Well Thanks Djon2003 for your suggestion of working with classes.

I googled how to create a data class and found this very useful link which gives nice step-by-step help:


It is a lot of work to set up each of my tables like this, but it does work very well and as the author says, you only have to do this once for each table, once it's done you can close the class and never look at it again.

I'm still not sure why my original code doesn't work and so I would like to leave the question open to see if anyone can shed light on this.


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question