Auto increment a number for this functionality properly RRS feed

  • Question

  • User1182587605 posted

    Right now, I increment my auto value to 1 from the query

    select sum(invynum+1) as invy from invytable 

    I use this value in my page_load event to populate data. But as the business case, I realized that if a person starts the screen and closes in middle without uploading it, The invynum+1 will be added further without any look back. Is there ny good method to follow so that this autoincrement can be stopped if values are not entered or saved in any way so that it can be used again or put a control over it.

    Mine is an internal application and limited number of users work on it so, a control over the data entry would be welcome with a code in C#,.



    Wednesday, November 23, 2016 6:25 PM

All replies

  • User753101303 posted


    Not sure to get the point especially with sum rather than max... What is your db ? To me the problem is not that this value will be consumed even if the user doesn't do the final insert but rather that multiple users could generate the same value causing collisions.

    Usually you are using https://msdn.microsoft.com/en-us/library/ms186775.aspx or https://msdn.microsoft.com/en-us/library/ff878058.aspx to generate auto-incremented values. Also this value is generated on the server side just before the insert which allows to avoid collisions (compared with computing it client side maybe several minutes before actually using the value).

    Wednesday, November 23, 2016 6:46 PM
  • User-471420332 posted

    Dear acmedeepak,


    Below one of the query will help full for you.

    SET @id = (select isnull(sum(invynum), 0) + 1
    from invytable)


    use into your stored procedure

    Declare @id int,@finalid int

    SET @id = (select ISNULL(sum(invynum),0) from invytable)

    SET @finalid =@id+1

    //pass @finalid as output parameter in your stored procedure

    Thursday, November 24, 2016 6:25 AM