Display autonumber  
Author Message
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

I have a main form with an add new button that calls another form called frmNewBooking. My booking table has a primary key named bookingID that I want to display to the user every time he/she adds a new booking.

Is there a way to display the generated bookingID in txtID on frmNewBooking when the form is 1st shown and everytime the user adds a new booking

Note: Access DB used.

Please do not refer me to: ms-help://MS.MSDN.vAug06.en/WD_ADONET/html/d6b7f9cb-81be-44e1-bb94-56137954876d.htm



.NET Development28  
 
 
mokeefe





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

Actually you could get a 'gestimate' on this by looking up the last Primary key and reviewing the increment value.

However, this would be a bad idea as with multiple users the information reported to a user regarding the "getsimated" primary key value could be different if another user submits first.


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top


See the following:

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET

The link you posted does not work for me so I don't know what it is about.



 
 
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

Clearly Paul P doesn't understand my question, all I wanted to do is display the autonumber value to the user for every new record he/she is trying to add. The solution to my problem is to get the last autonumber value, add 1 to it, and then display that to the user like so:

SELECT bookingID FROM (Booking) WHERE bookingID = (SELECT MAX(bookingID) FROM Booking)

Thereafter, add 1 for display purposes.


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top


I probably don't understand at least half of the initial posts here, which is why I typically have to ask follow-up questions or rely on the OP to provide more detailed information. It would have helped to know that you wanted to display the autonumber value to the user prior to the addition of a row.

In any event, something that you should know is that if you delete any rows from the table the Jet database engine may reuse old autonumber values. If I remember correctly, you will begin to notice this behavior after you compact the database. When this occurs, your solution (using MAX) will generate a value that is different than the actual autonumber value for a new row.



 
 
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

The way that my query works is that it should always retrieve the highest autonumber value in the database (at least that's how I understand it). I don't understand how the Jet would reuse old values given the fact that this is my primary key that always increments by one (1) and never goes back.
 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top


Well if you never delete any rows from the table then there is no issue.

However, lets say that you delete the last row in the table and the autonumber value is 100. Your highest autonumber value is now probably 99. What do you think the next autonumber value will be

Another potential issue is if you rollback a transaction the newly added row is discarded and your next autonumber value will be off by two (instead of one) with respect to your max value.

I don't know how these issues will affect your application, but you need to be aware of the behavior.



 
 
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

I just tested this, and it doesn't give me any problems. Just now, there was two records in my DB, with 73 and 76 respectively as my autonumber values. I deleted the last record (76) and re-run my app. and there was no error. My app. displayed 74 as the next value because 73 is now the highest in my DB.

Note: This value is only used for user-friendliness and display purposes; not to save into the DB.


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top


Your app may display 74 as the next autonumber value but in reality it will be 77 or higher for the next row added. Like I said, I don't know whether this is important to you but the MAX number (+1) you generate may not necessarily be the true "next" value.

 
 
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

Yes, you are right, and thank you for your suggestions. Now I see where this has been going all along. Now, how do I know what the last Access-generated autonumber was and display that to the user I would like this to be as user-friendly as possible, so I need a realistic value, not a guesstimate.

In essence, what I'm asking you is, is there a query to retrieve the last Access-generated autonumber value inspite of the fact that it might have been deleted What you have told and showed me is that Access always "remembers" the last autonumber value it generated. Now, I need a query to retrieve that value.


 
 
Paul P Clement IV





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top


I'm not aware of a method for determining the underlying "next" autonumber value.

Is there any significance to this value For example, is it used in any table relationships or does it represent something that would be important to the user

The reason that I ask is because what you could do is continue to use the SQL MAX method but generate your own value for the column and not rely on the autonumber mechanism.



 
 
Bandile





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

No, not at this stage, but I thought it would be helpful incase a user would like to use it for future references. Like searching for instance.
 
 
Matt Neerincx





PostPosted: .NET Framework Data Access and Storage, Display autonumber Top

You can guess the next number by running select max(autonumberfield)+1 from table.

This is how you guess the next autonumber, but it is only a guess of course.

There is no way to retrieve the actual real next autonumber value from Microsoft Access using the driver as far as I know.