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);
}

why dont you use identity to auto increment the ID? so you dont need to select MAX(ID) and add it by 1 manually — Mark, Jun 02 '17 at 05:57
Obviously it will work like this if your Id column is AutoIncremented, i guess you need to remove AutoIncrement property from Id field and manually input this which might help you — Vicky S, Jun 02 '17 at 05:57
by the way, maybe you are having a [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — Mark, Jun 02 '17 at 06:22
This is a **horribly bad idea** to use `select max(id)+1` .... in an system with multiple concurrent users (and which SQL Server database system isn't??), then you're **guaranteed** to get duplicates - really really horribly bad - ***DO NOT DO THIS!*** Seriously ..... this is what the `INT IDENTITY` column are for - then the **SQL Server database** handles all the nitty-gritty details, and it's **concurrency-safe** ! — marc_s, Jun 02 '17 at 06:32
Also, if you're retrieving only a single row, single value (like your `select max(id)` ) from a SQL Server database, you should use `.ExecuteScalar()` instead of `.ExecuteReader()` — marc_s, Jun 02 '17 at 06:33

Panagiotis Kanavos · Accepted Answer · 2017-06-02T06:48:44.947

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.

score 0 · Answer 2 · answered Jun 02 '17 at 05:58

0

Obviously it will work like this if your Id column is AutoIncremented, i guess you need to remove AutoIncrement property from Id field and manually input this which might help you

answered Jun 02 '17 at 05:58

Vicky S

762
4
16

The ID in the database has to be AutoIncremented – Viktor Jun 02 '17 at 05:59
then you will get incorrect Id if you try to remove an item, Ex: you have added 10 items, and removed last 3 then try to add an item to the table it will show the id as 11 which is correct, but i guess you are expecting this to be 8 am i right – Vicky S Jun 02 '17 at 06:03

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

2 Answers2