none
DBConcurrency Exception RRS feed

  • Question

  • Hello,

    i am using VB2008 and Access 2007

    i have a datagridview that was populated through designer. my table have Primary Key and on one of the column, i  have a combobox which was also populated thru tableadapter-bindingsource.

    now my problem is the table were usually updated using that combobox. i have one particular data in which i cannot update the combobox. i can update thru batch updating but during saving, i got a DbConCurrency Exception on that particular item, all else were saved. i am using bindingnavigator to save my items.

        Private Sub MasterSaveTS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MasterSaveTS.Click

            Try

                Me.Validate()

                Me.MListBindingSource.EndEdit()

                Me.MListTA.Update(Me.Rec_DS.MList)

                MsgBox("Data Saved")

            Catch ex As Exception

                MessageBox.Show(ex.Message.ToString)

                Me.MListTA.Update(Me.Rec_DS.MList)

            End Try

        End Sub

     

    here is the message

                           

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

     

    any ideas please?

    thanks

    Monday, October 25, 2010 8:43 AM

Answers

  • When you are updating datagridview's data source, you need to insert values from como column without fail. Rule is, both column value should match ( combo's data column and datagridview's datacolumn ) in its original datasource. Otherwise formatting exception will be thrown from datagridview when it tries to repaint that particular column.

    • Marked as answer by liurong luo Monday, November 1, 2010 1:57 AM
    Wednesday, October 27, 2010 6:54 AM

All replies

  • any help please? suggestion?

     

     

    Tuesday, October 26, 2010 10:18 AM
  • You are getting concurrency error because; there is a difference in row values (added/deleted) in Data source and cached data. Make sure your offline data tables match with actual data source. Otherwise you can handle concurrency errors.

    Walkthrough: Handling a Concurrency Exception

     

    Tuesday, October 26, 2010 10:48 AM
  • Mike,

    thank you for your reply, but just to feed my curiousity, the application is not running (yet) on the network. i have a primary key, but why is it from around 450 records, only that particular record, with that particular cell encounters an error? from what i have seen on the link you provided,

    "Concurrency exceptions (DBConcurrencyException) are raised when two users attempt to change the same data in a database at the same time. "

    is there any additional explanation for this?

    thanks again.

     

    Tuesday, October 26, 2010 12:46 PM
  • Mike,

    thank you for your reply, but just to feed my curiousity, the application is not running (yet) on the network. i have a primary key, but why is it from around 450 records, only that particular record, with that particular cell encounters an error? from what i have seen on the link you provided,

    "Concurrency exceptions (DBConcurrencyException) are raised when two users attempt to change the same data in a database at the same time. "

    is there any additional explanation for this?

    thanks again.

     

    same can be applicable if, two instance of data(offline) which are dissimilar, trying write it back to Original data source. This could be your issue here. When you call update, there will be an integrity check just before writing the result , Con. exceptions are thrown this level.
    Tuesday, October 26, 2010 12:55 PM
  • thank you for your explanation, i'll try the link you provided and get back here for whatever happens.

     

     

    Tuesday, October 26, 2010 1:05 PM
  • Hi,

    I've followed the procedure at the link and at first i received the Concurrency Exception Dialog Box asking me to update, and i click yes. but when i check the datagridview, it is still the same. i rebuild the project, perform the same update procedure, then i got the message Update Successful, but inside the datagridview, i still have the same data that i need to update.

    any more possible solution please?

    here below is my code.

        Private Sub UpdateDatabase()

     

            Try

                Me.MListTA.Update(Me.Rec_DS.MList)

                MsgBox("Update successful")

            Catch dbcx As Data.DBConcurrencyException

                Dim response As Windows.Forms.DialogResult

                response = MessageBox.Show(CreateMessage(CType(dbcx.Row, Rec_DS.MListRow)), "Concurrency Exception", MessageBoxButtons.YesNo)

                ProcessDialogResult(response)

            Catch ex As Exception

                MsgBox("An error was thrown while attempting to update the database.")

            End Try

        End Sub

     

     

        Private Function CreateMessage(ByVal cr As Rec_DS.MListRow) As String

            Return ("Database: " _

                        + (GetRowData(GetCurrentRowInDB(cr), Data.DataRowVersion.Default) + ("" & vbCrLf + ("Original: " _

                        + (GetRowData(cr, Data.DataRowVersion.Original) + ("" & vbCrLf + ("Proposed: " _

                        + (GetRowData(cr, Data.DataRowVersion.Current) + ("" & vbCrLf + "Do you still want to update the database with the proposed value?")))))))))

        End Function

     

        Private TempCustomersDataTable As New Rec_DS.MListDataTable

        Private Function GetCurrentRowInDB(ByVal RowWithError As Rec_DS.MListRow) As Rec_DS.MListRow

            Me.MListTA.Fill(TempCustomersDataTable)

            Dim currentRowInDb As Rec_DS.MListRow = TempCustomersDataTable.FindBySlNo(RowWithError.SlNo)

            Return currentRowInDb

        End Function

     

        Private Function GetRowData(ByVal custRow As Rec_DS.MListRow, ByVal RowVersion As Data.DataRowVersion) As String

            Dim rowData As String = ""

            For i As Integer = 0 To custRow.ItemArray.Length - 1

                rowData &= custRow.Item(i, RowVersion).ToString() & " "

            Next

            Return rowData

        End Function

     

        Private Sub ProcessDialogResult(ByVal response As Windows.Forms.DialogResult)

            Select Case response

                Case Windows.Forms.DialogResult.Yes

                    Rec_DS.MList.Merge(TempCustomersDataTable, True)

                    UpdateDatabase()

                Case Windows.Forms.DialogResult.No

                    Rec_DS.MList.Merge(TempCustomersDataTable)

                    MsgBox("Update cancelled")

            End Select

        End Sub

     

        Private Sub MasterSaveTS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MasterSaveTS.Click

            UpdateDatabase()

        End Sub

    or maybe i have some mistake with changing some from the original value to my own value at the code

    Tuesday, October 26, 2010 1:30 PM
  • i see  you are merging the data tables, was that merge went fine? , see is there any exception thrown at that point. Once update is successful, fill the dataset again.
    Wednesday, October 27, 2010 5:52 AM
  • Hello,

    Thank you for your reply, the only exception i received is asking me if i want to update the data, which I click yes and after that, nothing else. i played around a little bit but still i cant update that particular data but if i update another data, the update works fine.

    any more ideas please?

     

    thanks again.

    Wednesday, October 27, 2010 6:05 AM
  • As per your initial description, you have a combobox within datagridview, and both these controls data bound from different sources?. If yes, i assume you have a column (comobox column)with same name in both data source. If you try to update a value which is not in datagridview's bound source, you will end up with error. Do you have any such scenario here?

    Wednesday, October 27, 2010 6:16 AM
  • Yes i have a datagridview with a column that was bounded from another table, as well as other column also bounded from another table. I don't know, there is something fishy with the Access behavior recently on this project. i load the project, update one particular data, and it was update then i close the project. then, i again load the project, update that particular data i am having problem, and i receive that error message. as the error was trapped, i update another record and it was saved. i update again that same record i am having problem and voila, same error message. and to top it all, from my 437 records, i edited 436 (the remaining 1 is the problem data) record, after editing all, i click save, and all of my changes were saved. i return to edit that one record, and again error message. i know this is really wierd. i convert my access DB to Sql Server, run the same test and i can make whatever changes on that particular record. this is really frustrating.
    Wednesday, October 27, 2010 6:37 AM
  • When you are updating datagridview's data source, you need to insert values from como column without fail. Rule is, both column value should match ( combo's data column and datagridview's datacolumn ) in its original datasource. Otherwise formatting exception will be thrown from datagridview when it tries to repaint that particular column.

    • Marked as answer by liurong luo Monday, November 1, 2010 1:57 AM
    Wednesday, October 27, 2010 6:54 AM
  • I got your point, but I am curious why is it that I cannot update that particular data only, while i can update on the other? then on my SQL server, i can update with no problem, only in Access.

     

     

    Wednesday, October 27, 2010 7:04 AM
  • Hello,

    Any other suggestion please?

     

    Thanks.

    Saturday, October 30, 2010 7:14 AM