Fetch autoincremented value to textbox from database

Question

I have created form empdetails where employee will enter his details like name,address etc the empid will be generated automatically in my case empid starts with 0001 i have created table with empid column made it as identity
when user clicks on the this form i have textbox empid this textbox should display this auto genereated id.

How can i do that i'm using sql server 2005 in asp.net using c#

Can any1 help me on this.

Hari Boopala Krishnan · Answer

Thanks

Satyapriya Nayak · Answer

Hi Sweety, Try this, Create table employee(empid varchar(50),empname varchar(50)) using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class Default2 : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string str; SqlCommand com; int count; protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); str = "select count(*) from employee"; com = new SqlCommand(str, con); con.Open(); count = Convert.ToInt16(com.ExecuteScalar()) + 1; TextBox1.Text = "000" + count; con.Close(); } protected void Btninsert_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); str = "insert into employee values('" + TextBox1.Text.Trim() + "','" + TextBox2.Text.Trim() + "')"; com = new SqlCommand(str, con); com.ExecuteNonQuery(); con.Close(); } } Thanks If this post helps you mark it as answer

Prabhu Raja · Answer

Because you already had a one row in database table. but you can solve this by using Identity Seed property in Table design. When setting A column to identity on, you can find this identity seed in identity specification. change this identity seed from 1 to 0 (Zero)

Satyapriya Nayak · Answer

Hi, Try this, Create table employee(empid varchar(50),empname varchar(50)) using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class Default2 : System.Web.UI.Page { string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string str; SqlCommand com; int count; protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); str = "select count(*) from employee"; com = new SqlCommand(str, con); con.Open(); count = Convert.ToInt16(com.ExecuteScalar()) + 1; TextBox1.Text = "000" + count; con.Close(); } protected void Btninsert_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); str = "insert into employee values('" + TextBox1.Text.Trim() + "','" + TextBox2.Text.Trim() + "')"; com = new SqlCommand(str, con); com.ExecuteNonQuery(); con.Close(); } } Thanks

Sweety · Answer

instead of showing 0001 for the first time it showing 0002..

Prabhu Raja · Answer

Try this, In TextBox_ClickEvent(), private void TextBox_Click(object sender, EventArgs e) { Sqlconnection con = new SqlConnection("Your Connection String"); con.open(); string que = "Select IDENT_CURRENT(' Your Employee Table Name ') "; //It will return Last Identity Value in your table sqlCommand com = new SqlCommand(que, con); SQlDataReader sqlReader = com.ExecuteReader(); while (sqlReader.Read()) { int value = int.parse( sqlReader[0].ToString()) +1; txtsecond.Text = String.Format("{0:0000}" , value ) ; //Display autogenrated id as 0000 format on textbox } con.Close(); }

Sweety · Answer

Here i'm getting the previous id number but i want the current id number.Means if user logins and click on the empdetails form textbox should show id num 0001 and for the next click it should show 0002 like that.

Prabhu Raja · Answer

Hi, Consider this table Structure, Table Name: emp columns: EmpID int Identity ON EmpName Varchar(50). then, private void button1_Click(object sender, EventArgs e) { Sqlconnection con = new SqlConnection("Your Connection String"); con.open(); string que = "Insert into emp(EmpName) values (@empname); select EmpID from emp Where EmpID = SCOPE_IDENTITY() "; sqlCommand com = new SqlCommand(que, con); com.parameters["@empname"].value = "Employee name"; //Assign your employee name SQlDataReader sqlReader = com.ExecuteReader(); while (sqlReader.Read()) { txtsecond.Text = String.Format("{0:0000}" , sqlReader[0].ToString() ) ; //Display autogenrated id as 0000 format on textbox } con.Close(); } And Also, do another thing, In TextBox_ClickEvent(), private void TextBox_Click(object sender, EventArgs e) { Sqlconnection con = new SqlConnection("Your Connection String"); con.open(); string que = "Select IDENT_CURRENT(' Your Employee Table Name ') "; //It will return Last Identity Value in your table sqlCommand com = new SqlCommand(que, con); SQlDataReader sqlReader = com.ExecuteReader(); while (sqlReader.Read()) { int value = int.parse( sqlReader[0].ToString()) +1; txtsecond.Text = String.Format("{0:0000}" , value ) ; //Display autogenrated id as 0000 format on textbox } con.Close(); } -------------------------------------------------------------------- Thank You

Pravin More · Answer

Hi Sweety, its more simple then........ select IDENT_CURRENT('empdetailsTable') just execute this query.........it will return you current maximum id .........just increment it by one (+1) and show in textbox. so that will be id for next record . thats it.... Thanx, Pravin.

Sweety · Answer

I need to fetch this id before saving as soon as employee click on this page employee id should be displayed in the textbox rest of the things he will enter and click on save button and i need my employee id to be start with 0001 how can i do that. How can i do that...

Priya Linge · Answer

Hi, On button click insert empdetails in database according to tht empid will generate,will display on textbox as follow. private void button1_Click(object sender, EventArgs e) { sqlConnection.Open(); //Here insert empdetails in database table string que = "Insert into getvalue(ename)" + " values ('" + txtfirst.Text + "') select id from getvalue"; sqlCommand = new SqlCommand(que, sqlConnection); sqlReader = sqlCommand.ExecuteReader(); while (sqlReader.Read()) { txtsecond.Text = sqlReader[0].ToString();//Display autogenrated id on textbox } sqlConnection.Close(); } Please check attached file. Hope this will help. Thanks.

Pravin More · Answer

Hi Sweety....... do it like this...... execute this below query after insert query................. select IDENT_CURRENT('empdetailsTable') this will give you id of currently inserted record. Thanx,Hope this will help you. Pravin.

Fetch autoincremented value to textbox from database

Answers (12)