How to add prefix to auto generated ID based on some conditions?
In this article I will explain about how to create auto increment value in the database column with some prefix text based on some conditions in C# code.
Description
In this code snippet I am explain about how to increase student table student ID field automatically every addition of record prefix with department code selected in the drop down list.
Initially I have create table like below
create table st_tst(student_id nvarchar(10),sname varchar(50))
For example I have load list of department in the drop down list like below
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="CS">Computer Science</asp:ListItem>
<asp:ListItem Value="EE">Electrical & Electronics</asp:ListItem>
<asp:ListItem Value="IT">IT</asp:ListItem>
<asp:ListItem Value="COM">Commerce</asp:ListItem>
</asp:DropDownList>
If user is select item as Computer Science then I need to insert student id with prefix CS like "CSXXXX" or If user is select item as "Electrical & Electronics" then I need to insert student id with prefix EE like "EEXXXX" etc.
I have get that Drop down list selected value and added that value as prefix for student id creation using like below code
//insert with prefix department
protected void Button1_Click(object sender, EventArgs e)
{
string prefix = string.Empty;
string query =string.Empty;
//Get prefix text based on selection of department
prefix = DropDownList1.SelectedValue.Trim();
//Get max value for selected department and add it ID
query="insert into st_tst values('" + prefix + "' + cast((select isnull(MAX(REPLACE(student_id,'" + prefix + "','')),0) + 1 from st_tst where LEFT(student_id,2)='" + prefix + "') as nvarchar(5)),'" + TextBox1.Text + "')";
try
{
sqlcon.Open();
sqlcmd = new SqlCommand(query, sqlcon);
sqlcmd.ExecuteNonQuery();
TextBox1.Text = "";
}
catch (Exception ex)
{
}
finally { sqlcon.Close(); }
LoadGrid();
}Full Source Code
I have design web page like below image
Client Side
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h3>
Insert Auto Generated New Student ID with Prefix Selected Department Code</h3>
Enter Student Name
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<br />
Select Department
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="CS">Computer Science</asp:ListItem>
<asp:ListItem Value="EE">Electrical & Electronics</asp:ListItem>
<asp:ListItem Value="IT">IT</asp:ListItem>
<asp:ListItem Value="COM">Commerce</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="3">
<RowStyle ForeColor="#000066" />
<FooterStyle BackColor="White" ForeColor="#000066" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Code Behind
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//Display records in gridview
LoadGrid();
}
}
void LoadGrid()
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("select * from st_tst order by student_id", sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{ }
finally { sqlcon.Close(); }
}
//insert with prfix department
protected void Button1_Click(object sender, EventArgs e)
{
string prefix = string.Empty;
string query = string.Empty;
//Get prefix text based on selection of department
prefix = DropDownList1.SelectedValue.Trim();
//Get max value for selected department and add it ID
query = "insert into st_tst select ('" + prefix + "' + cast((select isnull(MAX(REPLACE(student_id,'" + prefix + "','')),0) + 1 from st_tst where LEFT(student_id,2)='" + prefix + "') as varchar(5))), '" + TextBox1.Text + "'";
try
{
sqlcon.Open();
sqlcmd = new SqlCommand(query, sqlcon);
sqlcmd.ExecuteNonQuery();
TextBox1.Text = "";
}
catch (Exception ex)
{
}
finally { sqlcon.Close(); }
LoadGrid();
}
}Output
After you insert each record based on the selected department automatic prefix added in the auto generated id. Output seems like belowSource Code
Here I have attached full source code for the same download it and test it.
Client Side : ASP.NET
Code Behind : C#Conclusion
I hope this article is help you to add prefix text before auto generated ID based on user selected department.