Sequence Number with Prefix based on Database record using C# and VB.Net in Windows Application

Hey AnkitPal,

Please take reference to below code and change your code.

Namespaces

C#

using System.Data.SqlClient;

VB.Net

Imports System.Data.SqlClient

Code

C#

private void GenearetUniqueNumber_click(object sender, EventArgs e)
{
    string connStr = @"Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
    string previousIdQuery = "SELECT TOP 1 GlobalNo FROM GenerateUniqueId ORDER BY ID DESC";
    string newId = GenerateNewId(connStr, previousIdQuery);
    lblId.Text = newId;
    string insertQuery = "INSERT INTO GenerateUniqueId(UniqueId,GlobalNo) VALUES(" + newId.Substring(4) + ",'" + newId + "')";
    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(insertQuery, con);
        cmd.ExecuteNonQuery();
        con.Close();
    }
}
private string GenerateNewId(string connection, string query)
{
    string newId = string.Empty;
    using (SqlConnection con = new SqlConnection(connection))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            string i = dr[0].ToString();
            int j = Convert.ToInt32(i.Substring(4));
            j = j + 1;
            newId = "NGRN" + j.ToString();
        }
        else
        {
            newId = "NGRN" + "1";
        }
        con.Close();
    }
    return string.Concat(newId);
}

VB.Net

Private Sub GenearetUniqueNumber_click(ByVal sender As Object, ByVal e As EventArgs) Handles lblId.Click, button1.Click
    Dim connStr As String = "Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"
    Dim previousIdQuery As String = "SELECT TOP 1 GlobalNo FROM GenerateUniqueId ORDER BY ID DESC"
    Dim newId As String = GenerateNewId(connStr, previousIdQuery)
    lblId.Text = newId
    Dim insertQuery As String = "INSERT INTO GenerateUniqueId(UniqueId,GlobalNo) VALUES(" & newId.Substring(4) & ",'" & newId & "')"
    Using con As SqlConnection = New SqlConnection(connStr)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(insertQuery, con)
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub

Private Function GenerateNewId(ByVal connection As String, ByVal query As String) As String
    Dim newId As String = String.Empty
    Using con As SqlConnection = New SqlConnection(connection)
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand(query, con)
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        If dr.Read() Then
            Dim i As String = dr(0).ToString()
            Dim j As Integer = Convert.ToInt32(i.Substring(4))
            j = j + 1
            newId = "NGRN" & j.ToString()
        Else
            newId = "NGRN" & "1"
        End If

        con.Close()
    End Using

    Return String.Concat(newId)
End Function

Screenshot

Inserted record in database