Results 1 to 11 of 11

Thread: Concurrency Violation

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    387

    Concurrency Violation

    I currently have a piece of code that reads a database into a data set, which contains a datatable. Once the data has been read into the datatable, it is displayed in a datagridview. The data table can then be updated and a button clicked to save the data back to the database.

    Now if I insert or delete a record, when save is pressed, the changes are saved to the database. However if I edit an existing record (not the primary key), when I save the changes, it generates the folloiwng error:-

    Data Update Failed System.Data.DBConcurrencyException Concurreny Violation. The update command affected 0 of the expected 1 records.


    Now the strange thing is that there is no one else exiting the data at the same time, so i can't understand why the error is being generated.

    The procedure that reads and populates the datatable is:

    Code:
     Sub buildTestTableDataGrid()
            Dim conn As String = "Data Source=xxx\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=True"
            Dim dbConnection As New SqlConnection(conn)
            Dim strSQL As String = "SELECT * FROM TestTable"
            Dim cmd As New SqlCommand(strSQL, dbConnection)
            Dim da As New SqlDataAdapter(cmd)
            Dim builder As New SqlCommandBuilder(da)
    
            Try
                da.Fill(dsTest.TestTable)
            Catch sqlExc As Exception
    
                MessageBox.Show(sqlExc.ToString, "SQL Exception Error!",
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
            '
            BindingSource1.DataSource = dsTest.TestTable
    
            Me.DataGridViewCutPaste1.DataSource = BindingSource1
            Me.DataGridViewCutPaste1.BindingSource = BindingSource1
    
    #Region "Columns defined"
            DataGridViewCutPaste1.Columns.Clear()
            TestTableKeyTextBoxColumn = New DataGridViewAutoFilter.DataGridViewAutoFilterTextBoxColumn()
    
            DataGridViewCutPaste1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.TestTableKeyTextBoxColumn})
            TestTableKeyTextBoxColumn.DataPropertyName = "TestTableKey"
            TestTableKeyTextBoxColumn.HeaderText = "TestTableKey"
            TestTableKeyTextBoxColumn.Name = "TestTableKeyDataGridViewTextBoxColumn"
            TestTableKeyTextBoxColumn.ReadOnly = True    
            TestTableKeyTextBoxColumn.Resizable = System.Windows.Forms.DataGridViewTriState.[True]
            TestTableKeyTextBoxColumn.Width = 100
            TestTableKeyTextBoxColumn.FilteringEnabled = True
    
            SurnameTextBoxColumn = New DataGridViewAutoFilter.DataGridViewAutoFilterTextBoxColumn()
    
            DataGridViewCutPaste1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.SurnameTextBoxColumn})
            SurnameTextBoxColumn.DataPropertyName = "Surname"
            SurnameTextBoxColumn.HeaderText = "Surname"
            SurnameTextBoxColumn.Name = "SurnameDataGridViewTextBoxColumn"
            SurnameTextBoxColumn.ReadOnly = _ReadOnly1      
            SurnameTextBoxColumn.Resizable = System.Windows.Forms.DataGridViewTriState.[True]
            SurnameTextBoxColumn.Width = 100
            SurnameTextBoxColumn.FilteringEnabled = True 
    #End Region
    
        End Sub
    The procedure that is executed when the save button is pressed is :-

    Code:
    Private Sub SaveToolStripButton_Click(sender As Object, e As EventArgs) Handles SaveToolStripButton.Click
    
                TestTableTableAdapter1.Update(dsTest.TestTable)
             Catch ex As Exception
                MsgBox("Data Update Failed " & ex.ToString)
    
            End Try
        End Sub
    Now despite trying numerous variations, the concurrency error continues to be generated. Any assistance in correcting the error would be appreciated.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    37,863

    Re: Concurrency Violation

    This may be more of a cautionary tale, but there is this thread:

    https://www.vbforums.com/showthread....ion-in-Dataset

    There is a standard way that this goes wrong, which JMC talks about in his reply to my thread, but if you follow the link in the first post, you'll also see a single (though lengthy) post thread about a different instance of this.
    My usual boring signature: Nothing

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,268

    Re: Concurrency Violation

    This may or may not be part of your issue but you obviously have a typed DataSet so why are you creating a SqlConnection, etc? You don;t need a SqlConnection, SqlCommand, SqlDataAdapter or SqlCommandBuilder because all that functionality is in your table adapter. The same table adapter you're calling Update on to save the changes, you should be calling Fill on to retrieve the data in the first place.

    Also, why are you binding data to the grid first and then adding columns? Binding data should be the last thing you do. Unless there's a reason to binding the data should be the last thing you do. If you're creating the columns yourself, that should be done in the designer if possible. If the grid is auto-generating the columns when you bind then there's no need for any code.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    387

    Re: Concurrency Violation

    JMC thats really helpful, i now appreciate the real benefits of a typed dataset. it has simplified the code considerably and also removed the violation. The code now reads

    Code:
        Sub buildTestTableDataGrid()
            Try
                TestTableTableAdapter1.Fill(dsTest.TestTable)
            Catch sqlExc As Exception
    
                MessageBox.Show(sqlExc.ToString, "SQL Exception Error!",
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
            '
            BindingSource1.DataSource = dsTest.TestTable
    
            Me.DataGridViewCutPaste1.DataSource = BindingSource1
            Me.DataGridViewCutPaste1.BindingSource = BindingSource1
    It also populates the datagrid view without specifying the columns. This is good however it also shows the datakey column, which is an auto incremented field and i dont want this to be displayed. Now i can't delete this from the datatable in the dataset, as it generates an error on update (as there is no reference key).

    Is there a easy way of removing it from the datagridview. Otherwise i will have to add all the columns in manually. In addition is it possible to over ride certain configuration items for each column on an individual basis, whist not adding each column in individually ?

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Concurrency Violation

    You could set the dgv column's Visible property to false.

    Also, a TableAdapter can have multiple Fill queries. Open the dataset, right click on the tableadapter, Add query. Then create a query that retrieves only the fields you want, then you can name it something like FillByNoId. Or what ever describes the query.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    387

    Re: Concurrency Violation

    Thanks for the suggestions, however I did try these as a possibility.
    You could set the dgv column's Visible property to false.
    Now adding

    Code:
    Me.datagridviewcutpaste1.columns(“TestTableKey”).visible = false
    Works, however it does not validate the field name.
    Is there a way to specify the field name where the system will validate it ?

    Also, a TableAdapter can have multiple Fill queries. Open the dataset, right click on the tableadapter, Add query. Then create a query that retrieves only the fields you want, then you can name it something like FillByNoId. Or what ever describes the query.
    Now this would work, however I can’t run the update command as there is no primary key so it’s ok for a simple display, but not for the update I require.
    Last edited by Signalman; Nov 30th, 2022 at 06:29 PM.

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,268

    Re: Concurrency Violation

    You've made this harder for yourself than it needs to be. As you have a typed DataSet, you could have just dragged a table from the Data Sources window onto your form and it would have automatically created a DataGridView, a BindingSource, a DataSet and a table adapter with the appropriate bindings, as well as generated the code to populate the DataTable. This is generally frowned upon by more experienced developers, as the data access code shouldn't be in the forms, but if you're going to do that anyway, you may as well take advantage of the automation. You can then remove or hide columns in the grid in the designer.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    387

    Re: Concurrency Violation

    Umm, I did not know you could do that, and I have just tried that and it works, and it’s very quick and easy, however it only adds a standard data grid view. My data grid view has the auto filters added to the top of the columns, so I assume I have to create them manually. The auto filter is currently working 😀 each column is based on DataGridViewAutoFilter.DataGridViewAutoFilterTextBoxColumn

    Is there a better way of specifying

    Code:
     Code:
    Me.datagridviewcutpaste1.columns(“TestTableKey”).visible = false
    So errors are picked up at the compile stage and Not at runtime ?

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,268

    Re: Concurrency Violation

    If you're using a control derived from DataGridView, after dragging a table from the Data Sources window, you can open the designer code file and change the type of the control from the standard DataGridView to your custom type. One way to open the designer code file to click the Show All Files button in the Solution Explorer, expand the node for your form and open it from there. There will be two places to make the change: where the field is declared and where the object is created. Save that change and open the designer and presto! Make sure that your project is backed up before editing a designer code file, as you might make your form unusable. Best to use source control so that you can simply revert to the last good state.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,040

    Re: Concurrency Violation

    Works, however it does not validate the field name.
    Is there a way to specify the field name where the system will validate it ?
    I really don't understand why you need to validate the field name. Or do you mean the column name? Even so, why?

    Also, you don't need a column name, you can use the index instead.

    Code:
    Me.datagridviewcutpaste1.columns(0).visible = false

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    109,268

    Re: Concurrency Violation

    You should find that a field is generated for each column in the grid when it's done in the designer, just as is done for controls added in the designer. You can access grid columns via those fields directly, rather than through the grid. The grid doesn't have a dedicated member for each column - how could it - so you can only access them via the Columns collection, which requires indexing with name or ordinal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width