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

---

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?

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.

Quote:

---

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.

Are you inserting records that have an auto-generated ID? If so, are you retrieving that auto-generated value into your application?

Quote:

---

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.

What is the format used for the dates in the database? Is the same format being used in your DateTimePickers?

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.

Quote:

---

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

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

Quote:

---

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.

Quote:

---

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.