Generate auto incremented number based on last record in ASP.Net using C# and VB.Net

Hi vrindavani,

Refer below code.

HTML

<asp:Button Text="Save" runat="server" OnClick="OnSave" />

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void OnSave(object sender, EventArgs e)
{
    string previousIdQuery = "SELECT TOP 1 UniqueId FROM Table ORDER BY UniqueId DESC";
    string billNumber = GenerateNewId(previousIdQuery);
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO Table VALUES(@BillNumber)", con);
        cmd.Parameters.AddWithValue("@BillNumber", billNumber);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

private string GenerateNewId(string query)
{
    string newId = string.Empty;
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand(query, con);
        con.Open();
        string lastId = Convert.ToString(cmd.ExecuteScalar());
        con.Close();
        if (string.IsNullOrEmpty(lastId))
        {
            newId = txtNumber.Text.Trim();
        }
        else
        {
            string prefix = lastId.Substring(0, lastId.Length - 3);
            string number = lastId.Substring(lastId.Length - 3, 3);
            int j = Convert.ToInt32(number);
            j = j + 1;
            newId = prefix + j.ToString().PadLeft(3, '0');
        }
    }

    return newId;
}

VB.Net

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    Dim previousIdQuery As String = "SELECT TOP 1 UniqueId FROM Table ORDER BY UniqueId DESC"
    Dim billNumber As String = GenerateNewId(previousIdQuery)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim cmd As SqlCommand = New SqlCommand("INSERT INTO Table VALUES(@BillNumber)", con)
        cmd.Parameters.AddWithValue("@BillNumber", billNumber)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub

Private Function GenerateNewId(ByVal query As String) As String
    Dim newId As String = String.Empty
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        con.Open()
        Dim lastId As String = Convert.ToString(cmd.ExecuteScalar())
        con.Close()
        If String.IsNullOrEmpty(lastId) Then
            newId = txtNumber.Text.Trim()
        Else
            Dim prefix As String = lastId.Substring(0, lastId.Length - 3)
            Dim number As String = lastId.Substring(lastId.Length - 3, 3)
            Dim j As Integer = Convert.ToInt32(number)
            j = j + 1
            newId = prefix & j.ToString().PadLeft(3, "0"c)
        End If
    End Using

    Return newId
End Function