Create auto increment Student ID in ASP.Net using C# and VB.Net

Hi ernestpauld,

Refer below example and correct your code.

First you need to get the last generated id from the table. Then increment the id based on the current year by splitting with - character.

Then use the newly generated id in database.

SQL

CREATE TABLE Students (StudentId VARCHAR(50), Name VARCHAR(100))

HTML

<asp:TextBox runat="server" ID="txtName" />
<asp:Button Text="Submit" runat="server" OnClick="OnSubmit" />

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

protected void OnSubmit(object sender, EventArgs e)
{
    string lastId = this.GetLastStudentId();
    if (!string.IsNullOrEmpty(lastId))
    {
        string year = lastId.Split('-')[0];
        string id = lastId.Split('-')[1];
        if (DateTime.Now.ToString("yy") == year)
        {
            lastId = string.Format("{0}-{1}", DateTime.Now.ToString("yy"), (int.Parse(id) + 1).ToString().PadLeft(3, '0'));
        }
        else if (int.Parse(DateTime.Now.ToString("yy")) > int.Parse(year))
        {
            lastId = string.Format("{0}-{1}", DateTime.Now.ToString("yy"));
        }
    }
    else
    {
        lastId = string.Format("{0}-001", DateTime.Now.ToString("yy"));
    }
    string query = "INSERT INTO Students (StudentId, Name) VALUES (@Id, @Name)";
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Id", lastId);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

private string GetLastStudentId()
{
    string lastId = string.Empty;
    string query = "SELECT MAX(StudentId) FROM Students";
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Connection = con;
            con.Open();
            lastId = cmd.ExecuteScalar().ToString();
            con.Close();
        }
    }

    return lastId;
}

VB.Net

Protected Sub OnSubmit(sender As Object, e As EventArgs)
    Dim lastId As String = Me.GetLastStudentId()
    If Not String.IsNullOrEmpty(lastId) Then
        Dim year As String = lastId.Split("-"c)(0)
        Dim id As String = lastId.Split("-"c)(1)

        If DateTime.Now.ToString("yy") = year Then
            lastId = String.Format("{0}-{1}", DateTime.Now.ToString("yy"), (Integer.Parse(id) + 1).ToString().PadLeft(3, "0"c))
        ElseIf Integer.Parse(DateTime.Now.ToString("yy")) > Integer.Parse(year) Then
            lastId = String.Format("{0}-{1}", DateTime.Now.ToString("yy"))
        End If
    Else
        lastId = String.Format("{0}-001", DateTime.Now.ToString("yy"))
    End If

    Dim query As String = "INSERT INTO Students (StudentId, Name) VALUES (@Id, @Name)"
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Id", lastId)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Private Function GetLastStudentId() As String
    Dim lastId As String = String.Empty
    Dim query As String = "SELECT MAX(StudentId) FROM Students"
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Parameters.AddWithValue("@Name", txtName.Text)
            cmd.Connection = con
            con.Open()
            lastId = cmd.ExecuteScalar().ToString()
            con.Close()
        End Using
    End Using

    Return lastId
End Function

Screenshot