locked
bindingsource and access autonumber RRS feed

  • Question

  • Hi,

    I am using Visual Studio 2010 and an acces 2003 databse.

    I had a look at one of the MSDN tutorial videos on adding data to related tables (http://msdn.microsoft.com/en-us/vbasic/bb725826.aspx) and it is exactly what I need.

    The problem I am having is related to the autonumber (the primary key) in my access database. When I add a new record on my form, the autonumber field is always set to -1. I only get the true value of the new record's autonumber after a save and a refill.

    What puzzles me is that in the tutorial video this problem does not occur and no code is added this deal with this behaviour. The only difference I can see between the tutorial and my sample project is that I am using Access instead of SQL server.

    Basically what I want is my new autonumber/primary key to be filled correctly as soon as I hit the Add New button, just as shown in the video ...

    Any ideas anyone?

    Thanks,

     

    Tom

     

     

     

     

    Friday, June 11, 2010 9:25 AM

Answers

  • Hi

    Go to the Dataset Designer, then on the DataTable for the Primary table, click the column that is the primary key. We want to change a property for this column. In the Properties Window, set the AutoIncrementStep property to 1 (instead of -1 for example, which might be the default). So if, for example, you have a DataSet with the Orders and OrderDetails DataTables, in your DataSet Designer, select the OderID column of the Orders DataTable and the in the Properties Window, set the AutoIncrementStep property to 1. Then rebuild and run your application.

    (Please mark this as the answer if it solves your problem. Thanks)

    • Marked as answer by Jing0 Saturday, June 19, 2010 9:07 PM
    Saturday, June 12, 2010 7:05 PM
  • Hi,

     

    Based on my understanding, it is not possible to know the next number of autoincreased number in database. There is no method or property to do this in database.

     

    Set the column AutoIncrementStep is a good method if all the action has been done on the client, not in the database.

     

    If you also want to control the data in database, the following may help you.

     

    1.Change the AutoIncrement column to unique column. When user adds a new item, use a method to generate a unique number and set this to ID value.  

     

    2.Change the AutoIncrement column to unique column. When user adds a new item, get the prior record ID number, prior record ID number +1 = this item ID number.

     

    3. If values in a record can be null except the ID, you can add a new item to the database. So the item ID number is 100 percent right. Then refresh the datatable on the client side to get the new updated record. Set the bindingsource current item to the last one – new added one.

     

    Hope this helps.

     

    Best regards,

    Ling Wang


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Jing0 Saturday, June 19, 2010 9:07 PM
    Friday, June 18, 2010 7:10 AM

All replies

  • Hi

    Go to the Dataset Designer, then on the DataTable for the Primary table, click the column that is the primary key. We want to change a property for this column. In the Properties Window, set the AutoIncrementStep property to 1 (instead of -1 for example, which might be the default). So if, for example, you have a DataSet with the Orders and OrderDetails DataTables, in your DataSet Designer, select the OderID column of the Orders DataTable and the in the Properties Window, set the AutoIncrementStep property to 1. Then rebuild and run your application.

    (Please mark this as the answer if it solves your problem. Thanks)

    • Marked as answer by Jing0 Saturday, June 19, 2010 9:07 PM
    Saturday, June 12, 2010 7:05 PM
  • Hi arlvin,

    Thanks for your reply. I already tried the solution you suggest but to no avail. Using the AutoIncrement property does increment the autonumner on the form but is not in sync with the database. For example, if the ID of the last record in the database =  #5, the added record on the form will be assigned #6. This is not ok if record #6  existed before in the database but has been deleted. In this case the new ID should be #7.

    This gets really anoying when you have a form that allows entering child records to a newly created parent. The form will show new the parent with ID#6 although it will be saved with ID#7 in the database. This means that the children cannot be saved since they use ID#6 as the foreign key.

    Ideas on this are more then welcome

    Sunday, June 13, 2010 8:22 PM
  • Hi,

     

    Based on my understanding, it is not possible to know the next number of autoincreased number in database. There is no method or property to do this in database.

     

    Set the column AutoIncrementStep is a good method if all the action has been done on the client, not in the database.

     

    If you also want to control the data in database, the following may help you.

     

    1.Change the AutoIncrement column to unique column. When user adds a new item, use a method to generate a unique number and set this to ID value.  

     

    2.Change the AutoIncrement column to unique column. When user adds a new item, get the prior record ID number, prior record ID number +1 = this item ID number.

     

    3. If values in a record can be null except the ID, you can add a new item to the database. So the item ID number is 100 percent right. Then refresh the datatable on the client side to get the new updated record. Set the bindingsource current item to the last one – new added one.

     

    Hope this helps.

     

    Best regards,

    Ling Wang


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Jing0 Saturday, June 19, 2010 9:07 PM
    Friday, June 18, 2010 7:10 AM
  • Tom Gz, Finally someone who has the same problem. How did you salve it and can you post it ? please.

    I did as arlvin suggested ..worked ok until people started deleting records and id's get scrambled...

    I search 2 days for this solution. !

    I can't believe  microsoft does not hav e a fix somewhere...?

     

    Thanks

    fordraiders

     

    Monday, June 28, 2010 4:12 PM