none
how to get sequence id in sql server with (DataSet) when add new Row ? RRS feed

  • Question

  • I'm use DataSet to connection with sql server , I want when click to button (+) add new row get sequence id from sql server And then click to Save Rows :

    select NEXT VALUE FOR Id_Sequence as Id_Sequence



    • Edited by srajmuneer Sunday, November 5, 2017 10:33 AM
    Sunday, November 5, 2017 10:30 AM

All replies

  • Hello,

    The next available primary key value is unreliable when working in a multi-user system so know this up front. Consider two users enter a new record. You get the new key then they post the new record, the value you have is not valid, it's one off.

    If you want to try the following with the above in mind.

    SELECT IDENT_CURRENT('dbo.YourTableName') +1;

    Here I create a new query

    Use it

    Dim nextIdentifier As Decimal? = CustomerTableAdapter.GetNextPrimaryKey
    MessageBox.Show(nextIdentifier.ToString)

    Now with that said I have not tried to "use" the new primary key for when pressing the "+" button. will let you work thru this.

    One last thing, when posting a new record, internally there is code directly after the insert to get that row's key via SELECT CAST(scope_identity() AS int) which you don't see but it's there.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Sunday, November 5, 2017 10:59 AM
    Moderator
  • thanks Kareninstructor, I want use id sequence instead of identity , I'm use (CREATE SEQUENCE) to get ID :

    CREATE SEQUENCE Id_Sequence
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 0
        NO MAXVALUE
       NO CACHE

    Sunday, November 5, 2017 12:35 PM
  • thanks Kareninstructor, I want use id sequence instead of identity , I'm use (CREATE SEQUENCE) to get ID :

    CREATE SEQUENCE Id_Sequence
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 0
        NO MAXVALUE
       NO CACHE

    This some kind of nonsense,  and one lets the DB engine do it by assigning an incremental identity primary-key.

    This just goes to show the limitation when one choose to use the Ford Model T.

    Sunday, November 5, 2017 2:32 PM
  • thanks Kareninstructor, I want use id sequence instead of identity , I'm use (CREATE SEQUENCE) to get ID :

    CREATE SEQUENCE Id_Sequence
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 0
        NO MAXVALUE
       NO CACHE

    If you look at this code sample, Operations.vb, function GenerateInvoice I use a sequence which is for demonstrating creation of a invoice alpha numeric invoice value. 

        Public Function GenerateInvoice(ByVal cn As SqlConnection) As String 
     
            Dim result As String = "" 
            Dim sql As String = "SELECT CONVERT(VARCHAR(4), GETDATE(), 12) + RIGHT('0000' + CAST( NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)),4)" 
     
            Using cmd As New SqlCommand With {.Connection = cn, .CommandText = sql} 
                result = cmd.ExecuteScalar.ToString 
            End Using 
     
            Return result 
     
        End Function 
    

    Sequence 

    USE [MasterDetailSimple]
    GO
    
    CREATE SEQUENCE [dbo].[GetInvoiceNumber] 
     AS [INT]
     START WITH 1
     INCREMENT BY 1
     MINVALUE 1
     MAXVALUE 999
     CYCLE 
     CACHE  10 
    GO
    

    You should be able to call the method using the same as I showed in my first reply.

    I will end with, this can be a bad idea so you have been warned. I've only used sequences less than three times in 25 years of coding, always had a better way starting from the database design and code design.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 5, 2017 2:49 PM
    Moderator
  • thanks Kareninstructor, When I need a group of people to insert data into a single table (DGV) , this can not be done because of the possibility of getting only one id when use (identity) while at (SEQUENCE) you can get more than one (id) at the time in (DGV) . Is there a solution to work on (DGV) at the same time adding restrictions and saving and deleting using (identity OR SEQUENCE) by more than one .

    Monday, November 6, 2017 6:03 AM
  • thanks Kareninstructor, When I need a group of people to insert data into a single table (DGV) , this can not be done because of the possibility of getting only one id when use (identity) while at (SEQUENCE) you can get more than one (id) at the time in (DGV) . Is there a solution to work on (DGV) at the same time adding restrictions and saving and deleting using (identity OR SEQUENCE) by more than one .

    I believe I already indicated that this is not a choice for multiple users performing inserts at the same time. I would had given you a solution if there was one.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 6, 2017 10:33 AM
    Moderator
  • If you look at this code sample, Operations.vb, function GenerateInvoice I use a sequence which is for demonstrating creation of a invoice alpha numeric invoice value. 

    thank you very much Kareninstructor for help  , but not found function in your link code sample
    Monday, November 6, 2017 10:56 AM
  • If you look at this code sample, Operations.vb, function GenerateInvoice I use a sequence which is for demonstrating creation of a invoice alpha numeric invoice value. 

    thank you very much Kareninstructor for help  , but not found function in your link code sample

    Karen wrote already there is no solution for the way you want to do it. 

    It is a cooking rice and than ask if you can color every cooked rice coral apart

    Not impossible, but a lot of work, better take another approach. 

    Be aware, you are not the first one who thinks the solution you want helps. It has been proven to be the wrong one. A sequence in a computer database is only a temporally number. 


    Success
    Cor

    Monday, November 6, 2017 11:18 AM
  • If you look at this code sample, Operations.vb, function GenerateInvoice I use a sequence which is for demonstrating creation of a invoice alpha numeric invoice value. 

    thank you very much Kareninstructor for help  , but not found function in your link code sample

    The link was wrong

    https://code.msdn.microsoft.com/Windows-forms-detail-view-89372f21?redir=0

    What is important to understand is the function was not designed to handle multiple users inserting at the same time yet even so most applications don't have conflicts with inserts as usually there is one person doing inserts at any given time so you with care if you decide to use it, otherwise don't use it.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 6, 2017 12:04 PM
    Moderator