sql server and vb.net Reorder / reset auto increment rearrange and no duplicate value RRS feed

  • Question

  • hi

    invoice          product     Quantity

    1                       a             10

    2                       b               5

    3                       c               5

    4                       a              20

    5                       d             30

    i need 2 solutions.

    1.  when product a invoice 1 and 4,  not enter invoice 4 cursor move quantity to re enter the quantity, where i make it 30.

    problem -2

    when i delete invoice no 4,  it rearrange the data and assign 4 no to invoice no 5  output look given below

    invoice          product     Quantity

    1                       a             30

    2                       b               5

    3                       c               5

    4                       d             30

    vb.net code is required


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Wednesday, April 17, 2019 11:46 AM


  • Hello,

    The correct way is to have a primary key and a invoice key where the primary key is auto-incrementing, this way nothing is out of place.

    Now to reseed a auto-incrementing primary key (which would not be your auto incrementing invoice field you would use the following pattern. Which in this case the primary key name is not important as this code will work off your primary key e.g. let's call the primary key "id"/

    ''' <summary>
    ''' reseed a table
    ''' </summary>
    ''' <param name="pTableName">Existing table name</param>
    ''' <param name="pIdentifierSeed">Set auto-incrementing primary key to this for next inser</param>
    Public Sub ReseedTable(pTableName As String, pIdentifierSeed As Integer)
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText = $"DBCC CHECKIDENT ({pTableName},RESEED, {pIdentifierSeed})"
            End Using
        End Using
    End Sub

    Going this route will stop the current issues. 

    Note if there are related child tables such as say order details you would need to adjust the relationship to work off the id field rather than the invoice field.

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    • Marked as answer by muhammadanzar Thursday, April 18, 2019 4:33 PM
    Wednesday, April 17, 2019 12:35 PM