How to create Alphanumeric Auto Increment in ASP.NET from SQL Server
In this Article, I am going to Explain about How to create Alphanumeric Auto Increment in ASP.NET from SQL Server. I have Seen too many members asking How to create Alphanumeric Auto Increment in ASP.NET from SQL Server. So I am deciding to write article about How to create Alphanumeric Auto Increment in ASP.NET from SQL Server.
Create Alphanumeric Auto Increment in ASP.NET from SQL Server
Today We are going to Learn about How to create Alphanumeric Auto Increment in ASP.NET with C# 2005 from SQL Server. Learn How to create Alphanumeric Auto Increment in ASP.NET from SQL Server
Write Below Code in Page Load Event
protected void Page_Load(object sender, EventArgs e)
{
ConnectionString = "Data Source=MY-PC;Initial Catalog=MyDB;Integrated Security=True";
AutoNumber();
}
Write AutoNumber Function to generate Auto increment in Alphanumeric format.
public void AutoNumber()
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(StaffNumber) as Tot FROM StaffDetails", con);
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.Read())
{
int i = Convert.ToInt32(dr["tot"]);
if (i > 0)
{
int j = i + 1;
txtEmpNumber.Text = "Staff0" +j.ToString();
}
else
{
txtEmpNumber.Text = "Staff01";
}
}
con.Close();
}
Write below code in Button Click event For Insert in SQL Server
DateTime DOB = Convert.ToDateTime(txtDOB.Text);
InsertStaffDetails(txtStaffNumber.Text, txtStaffName.Text, DOB, ddlDepartment.SelectedValue, ddlDesignation.SelectedValue, txtMobileNo.Text, txtEmailId.Text, txtAddress.Text);
Write Function InsertStaffDetails for Insert Staff Details
public void InsertStaffDetails(string EmpNumber, string EmpName, DateTime DOB, string Department, string Designation, string MobileNo, string EmailId, string Address)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "SpInsertStaffDetails";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StaffNumber", SqlDbType.VarChar, 50).Value = StaffNumber;
cmd.Parameters.Add("@StaffName", SqlDbType.VarChar, 50).Value = StaffName;
cmd.Parameters.Add("@DOB", SqlDbType.DateTime, 50).Value = DOB;
cmd.Parameters.Add("@Department", SqlDbType.VarChar, 50).Value = Department;
cmd.Parameters.Add("@Designation", SqlDbType.VarChar, 50).Value = Designation;
cmd.Parameters.Add("@MobileNo", SqlDbType.VarChar, 50).Value = MobileNo;
cmd.Parameters.Add("@EmailId", SqlDbType.VarChar, 50).Value = EmailId;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = Address;
cmd.ExecuteNonQuery();
con.Close();
}SQL Server Database
Here I am using Staff details table in SQL Server with Following Fields.
StaffNumber varchar(50)
StaffName varchar(50)
DOB datetime
Department varchar(50)
Designation varchar(50)
MobileNo varchar(50)
EmailId varchar(50)
Address varchar(100)
To Create Stored procedure for Insert Staff Details in SQL Server.
Create procedure [dbo].[SpInsertStaffDetails]
(
@StaffNumber varchar(50),
@StaffName varchar(50),
@DOB datetime,
@Department varchar(50),
@Designation varchar(50),
@MobileNo varchar(50),
@EmailId varchar(50),
@Address varchar(100)
)
AS
BEGIN
INSERT INTO StaffDetails(StaffNumber,StaffName,DOB,Department,Designation,MobileNo,EmailId,Address) VALUES
(@StaffNumber,@StaffName,@DOB,@Department,@Designation,@MobileNo,@EmailId,@Address)
End
GO
I think It will help you about How to create Alphanumeric Auto Increment in ASP.NET from SQL Server . Thanks for reading my Article How to create Alphanumeric Auto Increment in ASP.NET from SQL Server. if you have any query or you have any suggestion, let me know. I will appreciate you valuable feedback.
if you want to know about How to Create Login Page in ASP.Net Applications using Session. Please Refer Following Link.
How to Create Login Page in ASP.Net Applications using Session.
if you want to know about How to Upload Excel file in ASP.Net Applications. Please Refer Following Link.
Excel file upload in asp.net 2.0 display in grid view
Thanks
S.Suresh
Hi,
This Alphanumeric Auto Increment value is working.I want the auto value should be shown after clicking the submit button.This code is showing the auto value before clicking the button. Please help me to avoid it.
Thank you