Need to Renumber an Attribute

So I have a program that uses a DB to store information about various A/V equipment on campus. Each record houses the name, model, location, etc about the item, as well as an arbitrary ID number. The ID number is used by the program to allow it to step through each record, and populate things like combo-boxes and a display that allows me to input data for each item in the database. All of this works lovely. The problem arises when I use the program to delete a record, and the ID numbers are no longer 0,1,2, ... n. Say I delete whichever record is number 2. Then I'm stuck with 0, 1, 3, etc; I can't have numbers being skipped, so this breaks my implementations.

To me at least, the easy fix is to renumber the ID after a record is removed, so that once again I have a situation where it is 0,1,2, ... n with no numbers being skipped. The problem I'm facing is how that is accomplished in Java. I was under the impression that I could run a query, get a result set, make my change to the result set, and then update the result set so that it would change the source too. Nothing that I've tried, however, works. I either get null pointers or error in row. I did have minor success one time, where it renumbered every ID to the same thing. Unfortunately I can't remember what I did, because I could have tweaked that to make it work. Anyone have any thoughts, or could possibly point out a different approach? This is using an Access database, by the way.

So I have a program that uses a DB to store information about various A/V equipment on campus. Each record houses the name, model, location, etc about the item, as well as an arbitrary ID number. The ID number is used by the program to allow it to step through each record, and populate things like combo-boxes and a display that allows me to input data for each item in the database. All of this works lovely. The problem arises when I use the program to delete a record, and the ID numbers are no longer 0,1,2, ... n. Say I delete whichever record is number 2. Then I'm stuck with 0, 1, 3, etc; I can't have numbers being skipped, so this breaks my implementations.

Why?
Why do they have to be consecutive?
That there seems to be a bug in your system.

I can think of no system that I've worked on that requires the IDs to be consecutive. The only ones I've heard of are accounting ones to deal with some odd Italian law around invoice numbers, and that involved creating dummy (void) invoices to fill the gaps.

I realize it's probably not the best way to do it, however it's just so easy to use a for loop in this circumstance. That's what I'm doing -- using the incremental number in a for loop to be passed to my query as the ID number. From there, it gets the record, and puts its data into the fields of the GUI. When I don't have a nice set of consecutive numbers, that doesn't quite work.

I could rework how it populates fields in the GUI, but it seems like that would take a bit of time and require more code; hence why making it renumber itself is the simplest solution that I could think of. It seemed like a good idea. Is there a better way to do that sort of thing?

Renumbering the IDs on a database is extremely bad idea. Relational databases go to great lengths to preserve the integrity of the data, and the they depend on the unique id's to achieve that. In your case it looks like you have a simple table, but in the future you may have other tables, whose data are referenced through the ID's of this table. Even if you have a simple table, it is important to learn the right way of doing these kind of things.

.....I was under the impression that I could run a query, get a result set, make my change to the result set,....

It sounds like you are using a SELECT statement. Can't you just step through the result set, ignoring the Id's?

Renumbering the IDs on a database is extremely bad idea. Relational databases go to great lengths to preserve the integrity of the data, and the they depend on the unique id's to achieve that. In your case it looks like you have a simple table, but in the future you may have other tables, whose data are referenced through the ID's of this table. Even if you have a simple table, it is important to learn the right way of doing these kind of things.

Yes, I am aware that renumbering a unique ID would be bad. However the ID's are not actually unique identifiers for the records. The name is, when I actually have a need for it. If it ever does grow to incorporate multiple tables, I definitely won't be relying on an arbitrary surrogate key. I do appreciate the insight though ;)

It sounds like you are using a SELECT statement. Can't you just step through the result set, ignoring the Id's?

That is correct, and is what I had tried to do. I reviewed a few examples, and it just never quite worked out. Code like this:

I realize it's probably not the best way to do it, however it's just so easy to use a for loop in this circumstance. That's what I'm doing -- using the incremental number in a for loop to be passed to my query as the ID number. From there, it gets the record, and puts its data into the fields of the GUI. When I don't have a nice set of consecutive numbers, that doesn't quite work.

Why not simply get all the data in one go?
That's the way it's usually done, then populate your GUI while looping over the resultset (or better, a List of some class that represents that data).

So the better way would be to start the program, grab all necessary data from the database, and then populate things when necessary using the result set? I'll give that a shot; I think it might clean up the code a bit too.

You do not hold onto a result set in any case. You use it to populate something (usually a List of some object class that is your model), and it's that something that you use in the rest of your system.