# Visual Basic > Visual Basic .NET >  Reset the automatic numbering when deleting a specific record

## Tajaldeen

*Hello guys
I have the form shown in the picture where I can store some information to access db, it is very easy to use , the only problem I got is that when I delete certain record the order of numbers got  disturbed, for example when deleting record 3, number 4 replaces it, how can I solve this problem and make it reset the numbers automatically whenever I delete records.* 


*
Add code*



```
Try
                DatabaseConnect()
                Dim FileSize As UInt32
                Dim mstream As New System.IO.MemoryStream()
                pbImage.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
                Dim arrImage() As Byte = mstream.GetBuffer()
                FileSize = mstream.Length
                mstream.Close()
                Sql = "Insert into tbl_accounts ([ID],[Name],[Gender],[Location], [Tel],[Photo])Values(@ID,@Name,@Gender,@Location,@Tel,@aPhoto)"
                cmd = New OleDbCommand
                With cmd
                    .Connection = conn
                    .CommandText = Sql
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@ID", txtID.Text)
                    .Parameters.AddWithValue("@Name", txtName.Text)
                    .Parameters.AddWithValue("@Gender", Gender.Text)
                    .Parameters.AddWithValue("@Location", Location.Text)
                    .Parameters.AddWithValue("@Tel", txtTel.Text)
                    .Parameters.AddWithValue("@Photo", arrImage)
                    .ExecuteNonQuery()



                    MsgBox("Record saved successfully", MsgBoxStyle.Information

                    txtName.Clear()
                    Gender.Text = Nothing
                    Location.Text = Nothing
                    txtTel.Clear()
                    pbImage.Image = Nothing
                End With



            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
                cmd.Dispose()
            End Try
```

----------


## jmcilhinney

There is no problem so there's no need of a solution. This question has been asked and answered so many times it's not funny. The point of an AutoNumber in Access, like an identity in SQL Server, is to automatically generate unique, performant IDs for the records. That's it, that's all. The fact that they are sequential is irrelevant and is only the cases because doing so is the simplest option. You probably shouldn't even be displaying the IDs to the user but, even if that's useful, why is it a problem to have "holes" in the sequence? It almost certainly isn't, so you should stop trying to make it one.

----------


## Tajaldeen

> There is no problem so there's no need of a solution. This question has been asked and answered so many times it's not funny. The point of an AutoNumber in Access, like an identity in SQL Server, is to automatically generate unique, performant IDs for the records. That's it, that's all. The fact that they are sequential is irrelevant and is only the cases because doing so is the simplest option. You probably shouldn't even be displaying the IDs to the user but, even if that's useful, why is it a problem to have "holes" in the sequence? It almost certainly isn't, so you should stop trying to make it one.


When my boss asks me where is invoice number 3, what can I tell him, it just disappeard, he will accuse me of manipulation and not been profisstional.

----------


## schoemr

Hi Taljadeen,

Yes JMC is right. You should not display the auto generated ID. That ID is for the database to be know what is what (simply put)
Most people just hide it as the end user don't need to see it. 

You can add another column and have that column automatically numbered in sequence using code

Almost like excel. I also do that in one of my projects.

----------


## ChrisE

> When my boss asks me where is invoice number 3, what can I tell him, it just disappeard, he will accuse me of manipulation and not been profisstional.


well I don't delete records, there is a Field in the Table called "Strono" which indicates that this Record has been "canceled"
perhaps that is the Professional way IMO


p.s. Storno is Geman and means canceled

----------


## Tajaldeen

Okay nice, but how can i show that new column into my final CrystalReport ??

----------


## jmcilhinney

So your trying to hide the fact that you're deleting records from your boss? Sounds like he should be wary. Either you should be deleting records or you shouldn't. If you shouldn't, don't. We never delete records in our databases unless we're explicitly told to by the customer. Normally, we have an IsActive column that we set to False to indicate a "soft delete". If you should be deleting records then, again, what's the problem?

----------


## schoemr

That is a completely new question. Report questions you must ask in the Reports section...

I have never used Crystal reporting (I can help you with RDLC reports) but to answer your question - I imagine you will get it there exactly the same way you got the auto generated ID there...

----------


## jmcilhinney

Why exactly are you deleting records in the first place? This is not a rhetorical question. If you issue invoice number 3 to someone and your boss would consider it a bad thing if that record were missing them why are you deleting it? What is the real-world purpose of doing so?

----------


## Shaggy Hiker

If not having holes in the sequence matters, which it won't very quickly, then you can't use Autonumber or you can't delete. One or the other.

However, it isn't at all clear that the AutoNumber you are showing matters, so why show it? If it matters, then it shouldn't be an AutoNumber. If the value makes a difference, then AutoNumber won't be the right choice, since you can't set that value. Alternatively, if you were to use a GUID (I think that's called something like Replication ID in Access), they wouldn't be sequential and nobody would notice or care. GUIDs can have worse performance than integer keys for joining, but you won't notice the difference unless you have many tens of thousands of records. Of course, if you DO have tens of thousands of records, will anybody notice if a gap?

----------


## Tajaldeen

Guys, you are completely misunderstanding me. I am working on a database for school students. I use auto numbering to arrange students, and call their data according to the ID number for each one. i'm going to use that ID for many functions, search, arrange, call specific record, print drafts, export data ... etc, It is necessary not to have a hole in the record, when i add or delete it should keep resets numbring to maintain the whole body. 
i can attach the project if anyone could give me a hint of any shape or form.

----------


## Tajaldeen

Sorry about that , i'm new here , i do apologize

----------


## OptionBase1

> Guys, you are completely misunderstanding me. I am working on a database for school students. I use auto numbering to arrange students, and call their data according to the ID number for each one. i'm going to use that ID for many functions, search, arrange, call specific record, print drafts, export data ... etc, *It is necessary not to have a hole in the record*, when i add or delete it should keep resets numbring to maintain the whole body. 
> i can attach the project if anyone could give me a hint of any shape or form.


It isn't necessary at all.  All of the uses you've stated above work perfectly fine with ID "gaps".

If you insist that there aren't gaps, then as already stated by others, you can't use an AutoNumber field.  You will need to write code to manage the newly assigned ID numbers yourself, and then you need to write code that does a mass "re-ID" assignment when a record is deleted.  And then, of course, you will run into issues where if you are referencing a student by their ID in some other table, and suddenly their ID changes because of a deletion, then that reference in that other table will need to also be re-pointed to the new ID number or it will be pointing at the wrong student, etc...

Or you could just accept gaps and move on.

----------


## jmcilhinney

> Guys, you are completely misunderstanding me.


No we're not. We are understanding the information you're providing us. The problem is that you're failing to provide relevant information as well as providing irrelevant information. Try providing a FULL and CLEAR explanation of the problem if you want us to understand the problem.

Having said that, the specifics are pretty much irrelevant. You think you have some special problem but you don't. We've all seen this a hundred times before, as I've already said. People keep on asking for how to prevent holes in the sequence of IDs because they don't understand, not because we don't. There is no problem.



> I am working on a database for school students. I use auto numbering to arrange students, and call their data according to the ID number for each one. i'm going to use that ID for many functions, search, arrange, call specific record, print drafts, export data ... etc,


Fine. Go ahead and do it. You don't need a sequence with no gaps to do any of that. You don't even need a sequence. The IDs could be completely random and would still work perfectly well for all of that.



> It is necessary not to have a hole in the record, when i add or delete it should keep resets numbring to maintain the whole body.


Why? You keep saying it's necessary but, every time you're asked why, you refuse to provide a reason. I'm fairly certain that that's the case because you don't have one. The fact that a sequence with no gaps "feels better" to us as human beings is irrelevant to its utility in a computer system. If you can provide a valid reason then we can address that. If you don't have a reason, as appears to be the case, then it's a waste of our time and yours to try to solve a problem that doesn't exist. Worse still, you may actually be creating new problems by changing IDs of existing records. We're trying to protect you from yourself. If you can show that you don't need that protection, i.e. by providing an actual reason you need this functionality, then we can proceed on that basis. Of course, if you actually have a reason then you should have provided it long ago, then it would have saved everyone the wasted time.

----------


## schoemr

> I am working on a database for school students


Then why don't just use the student number as the ID?

----------


## vbdotnut

There are much questions . All you're showing is a command text, not anything else.
Sending an id as @param makes me wonder if you are setting this text manually or where this id is coming from, if it is being set how?, is there binding?, etc.
I figure if you're setting this manually then you need more help than I can offer.
If this value is coming from an adapter/reader/binding etc then the DB should be handling the identity, using a local autoincrement value/method here and expecting it to re-align back to a db is futile. If you need to handle the id locally then you should use a GUID. 
You have 25% of the solution stated which is around about the same percentage of advice I can offer.

----------


## ChrisE

> Guys, you are completely misunderstanding me. I am working on a database for school students. I use auto numbering to arrange students, and call their data according to the ID number for each one. i'm going to use that ID for many functions, search, arrange, call specific record, print drafts, export data ... etc, It is necessary not to have a hole in the record, when i add or delete it should keep resets numbring to maintain the whole body. 
> i can attach the project if anyone could give me a hint of any shape or form.


which Database are you using?
this is a sample with an Access Database and SQL to Re-Number the Table
in the Image you see left the Autoincrement on the right you see a new Sequence

here the Image


and the SQL for that is...


```
SELECT T1.EmployeeID, T1.LastName, (SELECT COUNT(*)+1
FROM Employees2 AS T2
WHERE T2.EmployeeID < T1.EmployeeID) AS NewSequenceNumber
FROM Employees2 AS T1
ORDER BY T1.EmployeeID;
```

but.... there is allways a but... 
this is all just causing more problems in the Future if you need more Tables
that will work with the "Student-Table" in your Database (foreign key(s) etc..)

but good luck

----------


## vbdotnut

Am I wrong in thinking this is not a good case for "where there's a will there's a way" Either your data integrity is 100% or 0% that is the bottom line in my rule book

----------

