Concurrency Violation... seems common question!!

Posted on 2011-10-28
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
    LVL 9

    Accepted Solution

    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

    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

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    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.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now