?
Solved

concuurancy violation error

Posted on 2006-11-13
20
Medium Priority
?
256 Views
Last Modified: 2012-08-13
Hi there i have the below code which generates the infor needed for a grid

Private Sub PopulateMainItemsGrid()

        Dim Sql As String = "SELECT ini_InvoiceItemID, ini_InvoiceNo, ini_Description, ini_ChargeableDays, ini_DailyRate, ini_Taxable, ini_Price, ini_Group,ini_vat FROM InvoiceItems WHERE ini_Group = 'MA' AND ini_InvoiceNo = " & Me.InvoiceNo

        Try
            ' make a new dataset
            MainItemsDataSet = New DataSet
            MainItemsDataTable = New DataTable
            ' retrieve costs data and add to DataSet
            MainItemsAdapter = New SqlClient.SqlDataAdapter
           
            With MainItemsAdapter
                ' configure select command
                Dim dataSelectCommand As New SqlClient.SqlCommand()
                dataSelectCommand.CommandType = CommandType.Text
                dataSelectCommand.CommandText = Sql
                dataSelectCommand.Connection = c_dataConnection
                .SelectCommand = dataSelectCommand

                ' create insert, delete and update commands
                Dim dataCommandBuilder As New _
                SqlClient.SqlCommandBuilder(MainItemsAdapter)
                .InsertCommand = dataCommandBuilder.GetInsertCommand
                .DeleteCommand = dataCommandBuilder.GetDeleteCommand
                .UpdateCommand = dataCommandBuilder.GetUpdateCommand


                ' create DataTable and add to DataSet
                .Fill(MainItemsDataTable)
                MainItemsDataTable.TableName = "MainItems"
                MainItemsDataSet.Tables.Add(MainItemsDataTable)
                Me.uxMainItemsGrid.DataSource = MainItemsDataSet
                Me.uxMainItemsGrid.SetDataBinding(MainItemsDataTable, "")
            End With

        Catch ex As Exception
            Dim HeroMsg As New msgbox
            HeroMsg.MessageBoxStyle = 2
            HeroMsg.ButtonCode = 0
            HeroMsg.MessageBoxText = "Error loading invoice items."
            HeroMsg.MessageBoxCaption = "Error"
            HeroMsg.ShowDialog()
            EH.LogError(ex.Source, ex.StackTrace, ex.Message, "InvoiceDetails", "PopulateMainItemsGrid")
        End Try

        Me.DetailsChanged = False

    End Sub


Now below is the code which is run on the pressing of the save button

Private Sub SaveMainItemsGrid()
        Try

            'save data in grid

            MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))
            MainItemsDataSet.AcceptChanges()
            Me.DetailsChanged = False
            Me.MainItemsGridChanged = False

        Catch ex As Exception
                        EH.LogError(ex.Source, ex.StackTrace, ex.Message, "InvoiceDetails", "SaveMainItemsGrid")
        End Try
    End Sub


No this works on all but one case.
If the users edits or enters a new row, then saves, then goes back to change the row i get the

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

please help
If
0
Comment
Question by:davoman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +1
20 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17929474
It looks like the new row entered by the user is not actually being saved because, at the stage the SaveMainItemsGrid sub is first called on it, it hasn't been "committed" from the datagrid to the datatable.  Try putting this

    BindingContext(MainItemsDataTable).EndCurrentEdit

as the first line in your SaveMainItemsGrid sub.  That will overcome that problem where it is necessary, and do no harm where it is not.  

Roger
0
 
LVL 1

Author Comment

by:davoman
ID: 17929503
'save data in grid
            BindingContext(MainItemsDataTable).EndCurrentEdit()

            MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))
            MainItemsDataSet.AcceptChanges()
            Me.DetailsChanged = False
            Me.MainItemsGridChanged = False

pu this but it did not work
0
 
LVL 1

Author Comment

by:davoman
ID: 17929511
the row is initialy being saved because when i save it, go out of the form, go back in  and edit the row then it works


but if i save it then go and edit the row i get the error

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:newyuppie
ID: 17929625
i dealt with this situation before and took literally weeks to find how to fix it. it happens because you are not having a private key in your datatable that autoincrements. it also may happen in other situations, but mainly what i said.

adapting some code from microsoft and other sources, i came up with the following code that helped me save without getting this error. try it to see if it fits and works for you. of course you need to adapt it, change the names of datasets and datatables, etc:

Try
            SolicitantesMySqlDataAdapter.Update(MySQLDataset.solicitantes)
Catch ex As DBConcurrencyException
            For Each dr As DataRow In MySQLDataset.solicitantes.GetErrors
                'Console.WriteLine(CreateMessage(dr))
                MySQLDataset.solicitantes.UniqueIDColumn.ReadOnly = False
                dr("UniqueID") = GetCurrentRowInDB(dr)("UniqueID")
                MySQLDataset.solicitantes.Merge(TempCustomersDataTable, True)
            Next
            Try
                SolicitantesMySqlDataAdapter.Update(MySQLDataset.solicitantes)
            Catch ex2 As DBConcurrencyException
                Return False
            End Try
End Try


now an additional function you need to copy&paste&adapt

'--------------------------------------------------------------------------
    ' This method loads a temporary table with current records from the database
    ' and returns the current values from the row that caused the exception.
    '--------------------------------------------------------------------------
    Private TempCustomersDataTable As New cscm_mastertemplateDataSet.solicitantesDataTable

    Private Function GetCurrentRowInDB(ByVal RowWithError As cscm_mastertemplateDataSet.solicitantesRow) _
        As cscm_mastertemplateDataSet.solicitantesRow

        SolicitantesMySqlDataAdapter.Fill(TempCustomersDataTable)
        'Me.CustomersTableAdapter.Fill(TempCustomersDataTable)

        Dim currentRowInDb As cscm_mastertemplateDataSet.solicitantesRow = _
            TempCustomersDataTable.FindByCustomerID(RowWithError.CustomerID)

        Return currentRowInDb
    End Function
0
 
LVL 1

Author Comment

by:davoman
ID: 17929651
I do have a unique key which autoincrements in this case
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17929655
>>
the row is initialy being saved
<<

You may well be right but, to check, temporarily replace this

            MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))

with this

            MsgBox(MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems")))

and see what the message box reports on the save when "i save it then go and edit the row".

I say this because, looking at your code again, I think I used the wrong reference in my BindingContext suggestion.  But, if you are correct and the row is indeed being saved without any such line, it's not worth investigating that further.

Roger
0
 
LVL 1

Author Comment

by:davoman
ID: 17929671
when i save it i get 1 in the message box
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17929695
what this does is basically try to update and if it has a concurrency exception, it enters a loop in which it takes all the rows that have errors and retrieves from the database its "UniqueID" value (in my case, this column is set as AutoIncrement on the database only, but i dont have it as a AutoIncrement nor as a Private Key on my VB datatable. the reason being is that i had decided to use a GUID as the private key on the datatable and didnt need the easy autoincrement column as a private key. furthermore, the easy autoincrement private key on my datatable was always conflicting with the autoincrement numbers my MySQL database was already told to assign to the column on insert of record. basically VB created an autoincrement number of 0,1,2 etc, but on my table i already had about 200 records so the autoincrement should have been 201,202, etc.

if you understood what i wrote before (its 8 am here, just woke up, sorry if im not very clear), you will understand that there are basically 2 options to avoid concurrency errors in this case:
1) if you have a database with an autonumbered column as a private key, keep that same column as an autonumbered private key in the VB datatable AND ALSO you first retrieve ALL THE RECORDS IN A FILL STATEMENT BEFORE you make the entry of data to the database, you'll be fine. this is because after retrieving all records, VB datatable will autonumber correclty the added one. BUT, if you just popped up the data entry form in VB and dont want to pull from the database every record because you only want to add 1 record and dont need to navigate through the rest, you need option 2)

2) you have a data entry form in VB in which you dont need to pull every record from the database beforehand. you only want to update a new record back to the database. but VB datatable doesn't know what autonumber your database is into right now.  i had this case, so i "turned off" the autonumber private key in VB datatable and instead used another column as the private key, in my case, a column i defaulted to a new GUID number to avoid duplicates every time i added a record.
in this case, if you update for the first time back to the database, the record in the database will contain all your info, plus an autonumber for the autonumber column that is generated ON THE DATABASE, not on the datatable. if you now try to update again, you will get the Concurrency Violation, because the datatable in VB does not know the autonumber the database assigned to the record. in this case, you must use a loop similar to what i posted, in order to loop through every row in the datatable, and pull from the database the values for this autonumber columns. store all this info in a temporary datatable, and after the loop merge all that back to the original datatable, and perform the update again.

NY
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17929698
<< I do have a unique key which autoincrements in this case>>
yes but the problem is that is not autoincrementing the same number as the database is autoincrementing. check it out.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17929745
>>
when i save it i get 1 in the message box
<<

OK, EndCurrentEdit is not the problem.  I'll leave you with newyuppie ;-)

Roger
0
 
LVL 1

Author Comment

by:davoman
ID: 17929768
ok NY

trying to adapt the code

MySQLDataset.solicitantes

don't quite understand this bit i'm using this

MainItemsDataSet.Tables("MainItems")
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17929884
thats right, do you have a typed dataset set up for this table?
0
 
LVL 1

Author Comment

by:davoman
ID: 17929891
no its all coded as the first part of the post
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 17929912
Are you updating or changing PRIMARY KEY? If PRIMARY KEY violate, you may get same error. Check your update query or command.

Thad
0
 
LVL 6

Expert Comment

by:riyazthad
ID: 17930044
Do you have any floating point field in your table?

Thad
0
 
LVL 1

Author Comment

by:davoman
ID: 17930050
hi Thad i think NY is on the right lines

i am not trying to update the primary key at all and there is no floating point
0
 
LVL 13

Accepted Solution

by:
newyuppie earned 2000 total points
ID: 17930080
keep in mind your datatable autonumber column name (mine is "UniqueID") and try this:

MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))

Try
            MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))
Catch ex As DBConcurrencyException
            For Each dr As DataRow In MainItemsDataSet.Tables("MainItems").GetErrors
                MainItemsDataSet.Tables("MainItems").Columns("UniqueID").ReadOnly = False
                dr("UniqueID") = GetCurrentRowInDB(dr)("UniqueID")
                MainItemsDataSet.Tables("MainItems").Merge(TempCustomersDataTable, True)
            Next
            Try
                MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems"))
            Catch ex2 As DBConcurrencyException
                Return False
            End Try
End Try


now an additional function you need to copy&paste&adapt

'--------------------------------------------------------------------------
    ' This method loads a temporary table with current records from the database
    ' and returns the current values from the row that caused the exception.
    '--------------------------------------------------------------------------
    Private TempCustomersDataTable As New DataTable

    Private Function GetCurrentRowInDB(ByVal RowWithError As DataRow) _
        As DataRow

        MainItemsAdapter.Fill(TempCustomersDataTable)
   
        Dim currentRowInDb As DataRow = _
            TempCustomersDataTable.Rows.Find(RowWithError("CustomerID"))

        Return currentRowInDb
    End Function


replace "CustomerID" with a column from your database which most likely is unique (apart from the autonumber column). in my case, "CustomerID" points to a GUID column.
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17930093
on the code i posted there is a repeated MainItemsAdapter.Update(MainItemsDataSet.Tables("MainItems")) on the first 2 lines, remove the one outside the TRY statement (i just pasted it for reference while i wrote my comment)
0
 
LVL 1

Author Comment

by:davoman
ID: 17930137
cheers for that
0
 
LVL 13

Expert Comment

by:newyuppie
ID: 17930151
cheers to you for the points!
NY
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 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