• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

concuurancy violation error

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
davoman
Asked:
davoman
  • 8
  • 7
  • 3
  • +1
1 Solution
 
SanclerCommented:
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
 
davomanAuthor Commented:
'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
 
davomanAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
newyuppieCommented:
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
 
davomanAuthor Commented:
I do have a unique key which autoincrements in this case
0
 
SanclerCommented:
>>
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
 
davomanAuthor Commented:
when i save it i get 1 in the message box
0
 
newyuppieCommented:
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
 
newyuppieCommented:
<< 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
 
SanclerCommented:
>>
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
 
davomanAuthor Commented:
ok NY

trying to adapt the code

MySQLDataset.solicitantes

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

MainItemsDataSet.Tables("MainItems")
0
 
newyuppieCommented:
thats right, do you have a typed dataset set up for this table?
0
 
davomanAuthor Commented:
no its all coded as the first part of the post
0
 
riyazthadCommented:
Are you updating or changing PRIMARY KEY? If PRIMARY KEY violate, you may get same error. Check your update query or command.

Thad
0
 
riyazthadCommented:
Do you have any floating point field in your table?

Thad
0
 
davomanAuthor Commented:
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
 
newyuppieCommented:
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
 
newyuppieCommented:
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
 
davomanAuthor Commented:
cheers for that
0
 
newyuppieCommented:
cheers to you for the points!
NY
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now