Solved

concuurancy violation error

Posted on 2006-11-13
20
250 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

12 Experts available now in Live!

Get 1:1 Help Now