locked
Issue using MSAccess AutoNumber ID and BindingSource.AddNew / CancelEdit RRS feed

  • Question

  • Hi, I have a strange issue involving a MS Access DB with tables using an AutoNumber pkid and the Visual Basic BindingSource.AddNew command. Please keep in mind that I am an experienced Access and VB6 user, but this is my first major .NET project.

    I have a database with a similar structure to the MS Access Northwind database, in that it has a customers tables, a table similar to the orders table, and similar relationships throughout.

    In my application, my customer Add/Edit/Delete/View screen is comprised of many text boxes and few option buttons etc. These are all databound. I used the Visual Studio.NET Datasource wizard to create a datasource from my Access DB, and I dragged the appropriate fields from the datasource window over to the controls. When I load the application, the fields are populated with the first record, and i can search through them, so this all works fine. I have no problem with adding new records, deleting them, or editing them. My problem is when I have to Cancel and AddNew.

    In my app, the user clicks the add button, which among other things, issues CustomerBindingSource.Addnew() command. This blanks out the textboxes and other controls, which the user can then fill in, then when validated, can save when they click the save button.

    In the in-memory version of the database that the application uses, it creates a new record with the next id available to it using the autonumber format. It seems to save that blank record somehow, because if the user clicks cancel instead of save after clicking add record, then the ID that was chosen for that record is not able to be re-used.

    For example, if the next auto-number should be 100, and the user clicks cancel, then the next time the user goes to add a new record, 100 should be the autonumber ID that is chosen. Instead, VB.NET would want to use 101. This is causing problems because when the data goes to be committed to the database, the ID's dont match up to what Access wants the ID to be.

    In other words, after i cancel and addnew, i want my dataset to completely roll back to the state it was in before the user clicked addnew. This is what i would expect, but it doesnt seem to be the case. Here is basically what I do in my code:

    Add Button:

    CustomerBindingSource.AddNew()

    Save Button:

    CustomerBindingSource.EndEdit()

    CustomerTableAdapter.Update(myDataSet.Customer)

    myDataSet.Customer.AcceptChanges()

    Cancel Button:

    CustomerBindingSource.CancelEdit()

     

    I have been trying to modify the way it adds and/or cancels the records but I can't seem to come up with a solution that doesn't give me an error of some sort.

    I recently thought that maybe if I change the NewValues property of the Customer ID from Increment to Random if that might help. If not, I thought about not using .Addnew or .CancelEdit at all, and just clearing out the textboxes manually for the user to add the new data. Then, when the user clicks save, the .AddNew command could be issued, followed directly after by the EndEdit, Update and AcceptChanges commands. If the user Cancels the Add, they would've actually cancelled and actual Addnew, so i just to a Move to some record in the database after the cancel.

    But i would prefer to use the .AddNew and .Cancel commands as they were meant to be used, and it would save alot of work that i don't think should be necessary. I searched as much as I could on these forums and several other .NET sites and I can't seem to find anyone with a similar problem, or an article that relates to my issue. So if anyone else has had this problem before and has found a solution, or if anyone reading this knows if I am doing something wrong and/or has some advice, I'd love to hear it!

    Thanks in advance,

    Dan

    Friday, February 9, 2007 11:58 PM

Answers

  • I solved it finally after about 3 days of trying.

    I finally found something at the following post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65251&SiteID=1

    on these forums that pointed me in the right direction.

    First, I set

    Me.myDataSet.Customer.idColumn.AutoIncrement

    to false when the Cancel button is clicked, and I store the ID that the addnew method generated before cancel was pressed.

    When I found that forum post, I saw how I could manipulate the row before it was added to the BindingSource, so i detect if AutoIncrement is true. If so, I let the id get handled normally, but if not, then i know that Cancel was pressed, and I populate it myself. 

    Then when the Save New Record button is pressed, I set idColumn.AutoIncrement back to true.

    Saturday, February 10, 2007 6:19 AM

All replies

  • Some extra info:

    The error I most commonly generate is: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

    The error gets generated when I try to cancel an addnew, and it gets generated in this way:

    Click Add Customer button.

    Click Cancel.

    Click Add Customer button.

    Enter some data, then click the Save button. (for some reason this record gets saved properly to the database).

    Click Add Customer button again.

    Enter some data, then click the Save button.

    Then the "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." error appears when "CustomerTableAdapter.Update(myDataSet.Customer)" tries to execute in the Save button code.

     

    Saturday, February 10, 2007 5:09 AM
  • I solved it finally after about 3 days of trying.

    I finally found something at the following post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65251&SiteID=1

    on these forums that pointed me in the right direction.

    First, I set

    Me.myDataSet.Customer.idColumn.AutoIncrement

    to false when the Cancel button is clicked, and I store the ID that the addnew method generated before cancel was pressed.

    When I found that forum post, I saw how I could manipulate the row before it was added to the BindingSource, so i detect if AutoIncrement is true. If so, I let the id get handled normally, but if not, then i know that Cancel was pressed, and I populate it myself. 

    Then when the Save New Record button is pressed, I set idColumn.AutoIncrement back to true.

    Saturday, February 10, 2007 6:19 AM
  • Hi
    I have this problem too.

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

    please clear for me about : how store Id from addnew method.

    and when do I use 'If command'? (
    if AutoIncrement is true)

    when I make a new record, fill it and  save it,   then I edit some fields and press save button again, I get this error.

    ThANK for any help
    Monday, April 6, 2009 8:00 AM
  • First, I set

    Me .myDataSet.Customer.idColumn.AutoIncrement

    to false when the Cancel button is clicked, and I store the ID that the addnew method generated before cancel was pressed.

    When I found that forum post, I saw how I could manipulate the row before it was added to the BindingSource, so i detect if AutoIncrement is true. If so, I let the id get handled normally , but if not, then i know that Cancel was pressed, and I populate it myself. 

    Then when the Save New Record button is pressed, I set idColumn.AutoIncrement back to true.

    Is there any body to explain this Quote for me and show some code sample?
    Please Help!!

    Thanks
    Monday, April 13, 2009 7:23 AM