0

I am working on an existing VB.NET + MS Access application. The VB.NET code queries the access db to get some data. For that -

  1. I retrieved the Maximum value of Autonumber field, using MAX.
  2. Generated a random value between 1 & MAX(Autonumber) field using Rnd function.

I coded the logic and executed the code successfully. I got the max Autonumber as 968, even though total number of records was only 501. Then I realized that some of the Autonumber rows are missing. Hence I dropped the table and created another table and populated it again. Now when I execute the SELECT MAX ... from VB.NET I still get the old value of 968 but if I execute the query from MS Access, I get the result as 501. I could think of nothing and closed the application, Visual Studio and started again but still I am getting the old value.

I am using ExecuteScalar to get the data from ms access.

What is happening, how can I fix it? Any ideas or pointers will be greatly appreciated.

djv
  • 15,168
  • 7
  • 48
  • 72
Tushar Saurabh
  • 687
  • 2
  • 13
  • 27
  • How did populate the new table after drop the old table? – Sankar Oct 12 '15 at 13:43
  • 1
    AutoIncrement IDs simply provide a unique ID for each row, they arent meant to be meaningful or sequential (they wont be once a record is deleted). You could select all the IDs and then pick a random ID from that set, then use a standard SELECT statement to get the related data. – Ňɏssa Pøngjǣrdenlarp Oct 12 '15 at 13:47
  • @Sankar Raj: New table got populated properly. I checked the autonumber field. – Tushar Saurabh Oct 12 '15 at 13:53
  • @Plutonix, yes thats one of the way and probably a foolproof way. Thanks for the suggesstion. – Tushar Saurabh Oct 12 '15 at 13:54
  • 1
    [How to get random record from MS Access database](http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database/19493100?s=1|0.0000#19493100) – Andre Oct 12 '15 at 14:07
  • Keep in mind that autonumber fields 1) Don't have to start from 1. 2) May have gaps if rows have been deleted. – theB Oct 12 '15 at 15:44
  • I agree that numbers in between might be missing. However, what I can't understand, why the query is returning the old autonumber data even though the table has been dropped, recreated and autonumber properly generated. – Tushar Saurabh Oct 12 '15 at 15:58

0 Answers0