-
Aug 14th, 2020, 03:16 PM
#1
Thread Starter
Lively Member
Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
I cannot figure out why this keeps happening. I have four date time picker boxes, that I'm changing then updating the binding source
Error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Notes: the date fields in the SQL table are varchar field types, not sure if that is why this is happening. But it will save to the database fine the first couple of times and then it eventually throws the error above.
Code:
Public QcBs = New BindingSource
Public QcJobsDT As New DataTable
Public QcJobsDadapter = New SqlDataAdapter
Private Sub QCjobs_Load(sender As Object, e As EventArgs) Handles MyBase.Load
QcJobsDadapter = New SqlDataAdapter("SELECT DATE1, DATE2, DATE3, DATE4 From Database;", con)
QcJobsDadapter.Fill(QcJobsDT)
QcJobsDadapter.UpdateCommand = New SqlCommandBuilder(QcJobsDadapter).GetUpdateCommand
QcBs.DataSource = QcJobsDT
DateTimePicker1.DataBindings.Add(New Binding("Text", QcBs, "Date1", True))
DateTimePicker2.DataBindings.Add(New Binding("Text", QcBs, "Date2", True))
DateTimePicker3.DataBindings.Add(New Binding("Text", QcBs, "Date3", True))
DateTimePicker4.DataBindings.Add(New Binding("Text", QcBs, "Date4", True))
End Sub
Function NextRecord()
Validate()
QcBs.EndEdit()
QcJobsDadapter.update(QcJobsDT)
QcBs.MoveNext()
End Function
Function PreviousRecord()
Validate()
QcBs.EndEdit()
QcJobsDadapter.update(QcJobsDT)
QcBs.MovePrevious()
End Function
Private Sub NextButton_Click(sender As Object, e As EventArgs) Handles NextButton.Click
Call NextRecord()
End Sub
Private Sub PreviousButton_Click(sender As Object, e As EventArgs) Handles PreviousButton.Click
Call PreviousRecord()
End Sub
-
Aug 14th, 2020, 11:18 PM
#2
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Why are you storing dates as text in the first place? Is that something you can fix because it's very bad practice?
What does your actual query look like? What column(s) is the primary key?
-
Aug 14th, 2020, 11:22 PM
#3
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
By the way, this line is pointless:
vb.net Code:
QcJobsDadapter.UpdateCommand = New SqlCommandBuilder(QcJobsDadapter).GetUpdateCommand
All you need to do is create the command builder and you should do that where you create the data adapter. You also appear to be unaware that you are creating two data adapters in that code. Either create everything as a field or create everything on demand. Don't mix and match if you can avoid it. In this case. you just need two lines:
vb.net Code:
Private adapter As New SqlDataAdapter("SQL query here", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
Private builder As New SqlCommandBuilder(adapter)
That's it, that's all. Now just call Fill and Update as required.
-
Aug 17th, 2020, 01:09 PM
#4
Thread Starter
Lively Member
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Originally Posted by jmcilhinney
Why are you storing dates as text in the first place? Is that something you can fix because it's very bad practice?
What does your actual query look like? What column(s) is the primary key?
Previous employee created the date field as a Varchar... this was done quite a bit much to my dismay. Primary key is actually in the SQL query, I just narrowed it down in the example above for simplicity. It can read and write to the database, but after a couple of updates, I get that error.
-
Aug 17th, 2020, 01:21 PM
#5
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Are you inserting records that have an auto-generated ID? If so, are you retrieving that auto-generated value into your application?
-
Aug 17th, 2020, 02:04 PM
#6
Thread Starter
Lively Member
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Originally Posted by jmcilhinney
Are you inserting records that have an auto-generated ID? If so, are you retrieving that auto-generated value into your application?
Nope, just updating current records.
-
Aug 17th, 2020, 07:24 PM
#7
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
What is the format used for the dates in the database? Is the same format being used in your DateTimePickers?
-
Aug 17th, 2020, 09:03 PM
#8
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
I think that your code is a lot more verbose that it needs to be. I haven't tested to confirm but I would expect that you don't need to save and then navigate. I would think that you could just navigate and then handle the CurrentChanged event of the BindingSource and save there. That happens after the Current property changes, so the data has already been validated and the editing session ended.
-
Aug 18th, 2020, 01:16 AM
#9
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Previous employee created the date field as a Varchar...
how did the previous employee save Data Changes
this is just a guess, change the last parameter to false
Code:
DateTimePicker4.DataBindings.Add(New Binding("Text", QcBs, "Date4", False))
don't know if it will make any diffrence setting..FormattingEnabled....to False
Last edited by ChrisE; Aug 18th, 2020 at 01:25 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Aug 18th, 2020, 07:04 AM
#10
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Is this a multi-user application? Are there other users that are also updating records at the same time?
Concurrency errors usually come when two users load a record locally, one changes it, and saves it back, then the second user also modifies it with out first refreshing, then tries to save that record back again... by trying to save that out of date record, you're violating the concurrency of the data.
One of the reasons I'm not a fan of mass edit forms like this.
-tg
-
Aug 18th, 2020, 08:53 PM
#11
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Originally Posted by techgnome
Is this a multi-user application? Are there other users that are also updating records at the same time?
Concurrency errors usually come when two users load a record locally, one changes it, and saves it back, then the second user also modifies it with out first refreshing, then tries to save that record back again... by trying to save that out of date record, you're violating the concurrency of the data.
One of the reasons I'm not a fan of mass edit forms like this.
-tg
This is exactly why concurrency errors exist and are the legitimate reason that they occur: to prevent one user blindly overwriting the changes made by another user. In this particular case, I was assuming that this was happening during debugging, while there was only a single user active. Maybe that assumption was invalid though.
-
Aug 19th, 2020, 02:28 PM
#12
Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Previous employee created the date field as a Varchar... this was done quite a bit much to my dismay. Primary key is actually in the SQL query, I just narrowed it down in the example above for simplicity. It can read and write to the database, but after a couple of updates, I get that error.
After reading that, I wondered what else might be different in the actual code. I know people do this thinking they're making it easier for us but often I've seen it cause a lot of wasted time because we didn't have all the necessary information.
Tags for this Thread
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
|