SQL current ID check returns same value twice.
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: SQL current ID check returns same value twice.

  1. #1
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    109

    Post SQL current ID check returns same value twice.

    Hi, I'm having some weird behaviour with SQL. I have a method to check what the next ID will be (no multithreading or any chance of collisions there) but the problem is I'm getting the same ID twice the first time I add a new record.

    This is my method:
    Code:
    int result = -1;
    int current = 0;
    int increment = 0;
    string command;
    
    try
    {
        command = @"USE " + DBSettings.Name + Environment.NewLine;
        command += "SELECT IDENT_CURRENT('" + tableName + "') AS tinyint";
    
        using (SqlCommand cmd = new SqlCommand(command, DBConnection))
            current = (int)((decimal)cmd.ExecuteScalar());
    
        command = @"USE " + DBSettings.Name + Environment.NewLine;
        command += "SELECT IDENT_INCR('" + tableName + "') AS tinyint";
    
        using (SqlCommand cmd = new SqlCommand(command, DBConnection))
            increment = (int)((decimal)cmd.ExecuteScalar());
    
        result = current;
        //result = current + increment;
    
        exceptions = string.Empty;
    }
    catch (SqlException err)
    {
        exceptions = err.Message;
    }
    catch (InvalidOperationException err)
    {
        exceptions = err.Message;
    }
    
    Console.WriteLine("NextID for table '" + tableName + "': " + result + 
                       " (current: " + current + ", increment: " + increment + ")");
    return result;
    First, I find it strange that the actual ID inserted is the (current) ID and NOT the (current + increment) ID. I've checked and the actual record is created with ID with value (current).
    Second, I had the following results when adding records to a clean empty just created DB:
    Code:
    Console: NextID for table 'A': 1
    Console: Adding element to table 'A' with ID 1
    Console: NextID for table 'A': 1
    Console: Adding element to table 'A' with ID 1
    Console: NextID for table 'A': 2
    Console: Adding element to table 'A' with ID 2
    Console: NextID for table 'A': 3
    Console: Adding element to table 'A' with ID 3
    And this is not a matter of inserting records too fast or not allowing DB to update. I've done this "slow" to say it somehow and it happens anyway.

    Tables are created with the following (relevant) fields:
    Code:
    string Command =
        @"IF NOT (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product')) 
            BEGIN 
            CREATE TABLE Product
                ( 
                    ID        tinyint    NOT NULL PRIMARY KEY IDENTITY(1,1), 
                    IDPoint   tinyint    FOREIGN KEY REFERENCES Point(ID),
                );
            END";
    return RunCommand(Command);
    If I reload every entry from DB I get the IDs ok. That is, PKs for every record are created ok. But my internal tracking (within my app) is offsetting my entries. I have a collection of elements and the first two have ID 1, which then messes up a later DB update since the element with (theoretical) ID 2 will overwrite ID 1.

    Any ideas? Thanks!

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,253

    Re: SQL current ID check returns same value twice.

    Honestly, your approach of attempting to internally track id's within your app is going to be problematic. For one thing, if an insert error occurs, an id can be skipped (and throw off what your app thinks the next id will be).

    If I were you, I would not try to track the id in my app, I would find an approach that inserts the record and then returns the new id to me.

    One way to do this is to write a sproc that returns the Id as an OUT parameter.

    Another way is to do this within Entity Framework.

  3. #3
    Join Date
    Jun 2011
    Location
    Buenos Aires, Argentina
    Posts
    109

    Re: SQL current ID check returns same value twice.

    Point taken. I agree, nevertheless it does seem like a strange behaviour, having IDENT_CURRENT return the same value twice.

    If an insert error occurs the procedure will return with error and I will catch it, thus knowing that it went wrong and doing whatever I need to do. The ID will be skipped and the SQL engine will return the next ID when asked. An "empty" ID will then exist in the DB. The same would happen if I eventually delete a record, its ID will be left unassigned. In any of these cases IDENT_CURRENT would point the last entry and IDENT_CURRENT + IDENT_INCR should be the next to-be-assigned ID... right?

    I'm not very skilled with SQL so I really don't know how to write a procedure that will return the ID. I'm betting on a "return ID" line at the end lol and I'm guessing I'd have to change ExecuteNonQuery for ExecuteScalar to get a return value?

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: SQL current ID check returns same value twice.

    If you make that field a KEY, you can require a value, or automatically create one. The DB takes care of timestamps if you include that.

    Deleting your way also creates problems in the future.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center