Solved

concuurancy violation error

Posted on 2006-11-13
20
249 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
  • 8
  • 7
  • 3
  • +1
20 Comments
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
Comment Utility
'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
Comment Utility
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
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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
Comment Utility
I do have a unique key which autoincrements in this case
0
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
>>
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
Comment Utility
when i save it i get 1 in the message box
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
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
Comment Utility
<< 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
Comment Utility
>>
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:davoman
Comment Utility
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
Comment Utility
thats right, do you have a typed dataset set up for this table?
0
 
LVL 1

Author Comment

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

Expert Comment

by:riyazthad
Comment Utility
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
Comment Utility
Do you have any floating point field in your table?

Thad
0
 
LVL 1

Author Comment

by:davoman
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
cheers for that
0
 
LVL 13

Expert Comment

by:newyuppie
Comment Utility
cheers to you for the points!
NY
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

13 Experts available now in Live!

Get 1:1 Help Now