Generating Custom AutoNumber

[tashiDuks]

Hi EveryOne,

I have a following controls in my vb.net form:

txtID
txtName
btnAddNew
btnSave

This form will be storing a values to the SQL Database (SQL Server 2005). In the txtID textbox the auto id should be generated when ever user clicks on btnAddNew. The ID should be generated in for digit i.e. 0000, 0001,0002, .... and so on.

When the form is opened next time and when the user adds the new record, the ID should generate next value coz the ID will not be duplicated.

Can anyone Help me?
regards,

TashiDuks

[Oxiegen]

In the form opening event, call a method that reads the last id from the database into a variable, and increment that value by 1. SELECT TOP 1 <ID> FROM <table> ORDER BY <ID> DESC

[tashiDuks]

In the form opening event, call a method that reads the last id from the database into a variable, and increment that value by 1. SELECT TOP 1 <ID> FROM <table> ORDER BY <ID> DESC

Thanks for the code Oxiegen. But this code is in short. Can you please show me the codding as per my controls mentioned? i.e. how to generate custom auto number (0001,0002,0003 ... so on) and how to generate the auto number from the last ID generated previously when user adds the new records for the second time, and then so on.

I would really appreciate if you can share me the code with and example accordingly with my controls which i have mentioned.

Thanks,

TashiDuks.

[Oxiegen]

The form load event.

Private Sub Start_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    ' Call the method for a new ID
    txtID.Text = GenerateID()
End Sub

Method for generating an ID.

Private Function GenerateID() As String
    Dim con As New SqlConnection("your connectionstring")
    Dim dr As SqlDataReader = Nothing
    Dim com As SqlCommand = Nothing
    Dim value As String = "0000"
    Dim ID As String
    Try
        ' Fetch the latest ID from the database
        con.Open
        com = New SqlCommand("SELECT TOP 1 <ID> FROM <table> ORDER BY <ID>", con)
        dr = com.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.HasRows
            dr.Read()
            value = dr.Item("<ID>")
        End If
        dr.Close

        ' Increase the ID by 1
        value += 1

        ' Because incrementing a string with an integer removes 0's
        ' we need to replace them. If necessary.
        If value <= 9 Then                'Value is between 0 and 10
            value = "000" & value
        ElseIf value <= 99 Then        'Value is between 9 and 100
            value = "00" & value
        ElseIf value <= 999 Then        'Value is between 999 and 1000
            value = "0" & value
        End If
    Catch ex As Exception
        ' If an error occurs, check the connection state and close it if necessary.
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        value = "0000"
    End Try

    Return value
End Function

[tashiDuks]

Thanks you very much. It solved my problem. THERE IS ONE PROBLEM... THE ID IS GENERATING BUT WHEN I CLOSE THE FORM AND THEN WHEN I OPEN THE FORM... IT GENERATES ID FROM BEGINING, WHICH MAKES MY ID DUPLICATE. SO CAN U RECTIFY IT PLEASE. YOU HAVE CALLED THE GENERATEID() FUNCTION ON FORM LOAD.. I HAVE USED THIS IN "btnAddNew" click event.

Your Function which i have changed the field name:

Private Function GenerateID() As String

        'Dim con As New SqlConnection("your connectionstring")

        Dim dr As OleDbDataReader = Nothing

        Dim com As OleDbCommand = Nothing

        Dim value As String = "0000"

        'Dim ID As String

        Try

            ' Fetch the latest ID from the database

            conn.Open()

            com = New OleDbCommand("SELECT TOP 1 DesID FROM dbo.hrEmpDesignation ORDER BY DesID", conn)

            dr = com.ExecuteReader(CommandBehavior.CloseConnection)

            If dr.HasRows Then

                dr.Read()

                value = dr.Item("DesID")

            End If

            dr.Close()



            ' Increase the ID by 1

            value += 1



            ' Because incrementing a string with an integer removes 0's

            ' we need to replace them. If necessary.

            If value <= 9 Then 'Value is between 0 and 10

                value = "000" & value

            ElseIf value <= 99 Then 'Value is between 9 and 100

                value = "00" & value

            ElseIf value <= 999 Then 'Value is between 999 and 1000

                value = "0" & value

            End If

        Catch ex As Exception

            ' If an error occurs, check the connection state and close it if necessary.

            If conn.State = ConnectionState.Open Then

                conn.Close()

            End If

            value = "0000"

        End Try



        Return value

    End Function

Here is my code :

Private Sub btnAddNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Try
            bNewData = True
            ShowBlankControls()
            EnableControlsAddMode(True)
            txtDesignationID.Focus()
            txtDesignationID.Text = GenerateID()

            'grvDesignationList.Enabled = False
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Add Record", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Please help me...!!!!!

TashiDuks.

The form load event.

Private Sub Start_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    ' Call the method for a new ID
    txtID.Text = GenerateID()
End Sub

Method for generating an ID.

Private Function GenerateID() As String
    Dim con As New SqlConnection("your connectionstring")
    Dim dr As SqlDataReader = Nothing
    Dim com As SqlCommand = Nothing
    Dim value As String = "0000"
    Dim ID As String
    Try
        ' Fetch the latest ID from the database
        con.Open
        com = New SqlCommand("SELECT TOP 1 <ID> FROM <table> ORDER BY <ID>", con)
        dr = com.ExecuteReader(CommandBehavior.CloseConnection)
        If dr.HasRows
            dr.Read()
            value = dr.Item("<ID>")
        End If
        dr.Close

        ' Increase the ID by 1
        value += 1

        ' Because incrementing a string with an integer removes 0's
        ' we need to replace them. If necessary.
        If value <= 9 Then                'Value is between 0 and 10
            value = "000" & value
        ElseIf value <= 99 Then        'Value is between 9 and 100
            value = "00" & value
        ElseIf value <= 999 Then        'Value is between 999 and 1000
            value = "0" & value
        End If
    Catch ex As Exception
        ' If an error occurs, check the connection state and close it if necessary.
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        value = "0000"
    End Try

    Return value
End Function

[Oxiegen]

Change this
SELECT TOP 1 DesID FROM dbo.hrEmpDesignation ORDER BY DesID
Into this
SELECT TOP 1 DesID FROM dbo.hrEmpDesignation ORDER BY DesID DESC

This will change the order so that the latest/greatest id will be the one selected.

[tashiDuks]

Thanks, it solved my problem. You are genius.

TashiDuks

[Elkhayyat]

What If I want it more unique with format "PRO0001""PRO0002" ......

[Syed Hassan Abbas]

How this number sequence can be generated in a network environment where one table is being used by many people at the same time?