Results 1 to 12 of 12

Thread: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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?

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    By the way, this line is pointless:
    vb.net Code:
    1. 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:
    1. Private adapter As New SqlDataAdapter("SQL query here", "connection string here") With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    2. Private builder As New SqlCommandBuilder(adapter)
    That's it, that's all. Now just call Fill and Update as required.

  4. #4

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    Quote Originally Posted by jmcilhinney View Post
    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.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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?

  6. #6

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    Quote Originally Posted by jmcilhinney View Post
    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.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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?

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    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.

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    Quote Originally Posted by techgnome View Post
    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.

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

    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
  •  



Click Here to Expand Forum to Full Width