Is it possible that changes in my program sometimes does not apply because my SQL Server database is slower than my program?

Question

I use this code to determine the next ID of an article. So if I were to add a new article to my database then it would recieve that ID.

But sometimes when I delete an article then the ID number is wrong when sometimes it's not (it has nothing to do with the code from the delete function)

try
{
    string s = "select max(Id) as Id from dbo.Artikelen";

    SqlCommand cmd = new SqlCommand(s, con);
    con.Open();

    SqlDataReader dr = cmd.ExecuteReader();
    dr.Read();

    int i = Convert.ToInt32(dr["Id"].ToString());

    labelArtikelID.Text = (i + 1).ToString();
    con.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Answer 1

It seems this code is an attempt to retrieve the automatically generated ID. MAX +1 will only work if no-one else inserts or deletes rows between INSERT and SELECT. This is extremely unlikely in a real application.

The easiest way to retrieve the new ID is to add an OUTPUT clause in the INSERT statement itself, and retrieve the returned ID.

INSERT INTO Artikeln(...)
OUTPUT inserted.ID
VALUEs(...)

Executing the INSERT command with ExecuteScalar will return the new ID.

IDENT_CURRENT('tablename') should be used to retrieve a table's current identity value, eg:

SELECT IDENT_CURRENT('dbo.Artikeln') AS CurrentID;

Deleting new entries means that MAX(ID) will return the ID of one of the latest entry, not even the current identity value.