-
Nov 29th, 2022, 05:03 PM
#1
Thread Starter
Hyperactive Member
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.
-
Nov 29th, 2022, 06:24 PM
#2
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
-
Nov 29th, 2022, 07:38 PM
#3
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.
-
Nov 30th, 2022, 05:19 PM
#4
Thread Starter
Hyperactive Member
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 ?
-
Nov 30th, 2022, 05:32 PM
#5
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.
-
Nov 30th, 2022, 06:17 PM
#6
Thread Starter
Hyperactive Member
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.
-
Nov 30th, 2022, 07:13 PM
#7
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.
-
Nov 30th, 2022, 07:32 PM
#8
Thread Starter
Hyperactive Member
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 ?
-
Nov 30th, 2022, 08:21 PM
#9
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.
-
Nov 30th, 2022, 09:06 PM
#10
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
-
Nov 30th, 2022, 10:43 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|