Member Avatar for Mitja Bonca

I have this code now for inserting new ID into database:
IDs are all primary key.

MaxID = 0;
string GetMaxID = "SELECT MAX(IDPerson) AS IDPerson FROM Persons";
SqlCommand cmd = new SqlCommand(GetMaxID, sqlConn);
sqlConn.Open(); 
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
    MaxID = reader.GetInt32(0);
}
MaxID++;
reader.Close();
sqlConn.Close();

This is the code which looks for the highes ID in the database and addes up 1 (+1) and this is then the newest ID which I can use.

But if I delete this ID out of the database, that number will never be used again.

I would like to know how can I fulfil all those deleted IDs again.

----------------------------------------
If you do not understand what I`m talking about:

1. I create a table with some IDs inside: 1,2,3,4,5,6,7,8,9,10
2. then I delete IDs: 2,3,7
3. In db I have now IDs(1,4,5,6,8,9,10)
4. With code above I can never ever reach IDs 2,3 and 7
5. My goal is and I would like to do exactly this - to reach these IDs 2,3,7 again

Or if I want to get just one free ID - the 1st unoccupied ID from Zero up (in my examle, this would be number 2).

Can someone hel me out?

Edited by Mitja Bonca because: n/a
  • 2 Contributors
  • 1 Reply
  • 161 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by DdoubleD
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.