How to add prefix to auto generated ID based on some conditions:

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
AddPrefixAutoGeneratedID

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 below

AddPrefixAutoGeneratedID

Pages

Friday, 24 May 2013