Dear Sir,
I have two table "emp_mast1" and "TATable".
CREATE TABLE [dbo].[emp_mast1] (
[empid] NCHAR (6) NOT NULL,
[firstname] NVARCHAR (50) NULL,
[lastname] NVARCHAR (50) NULL,
[designation] NVARCHAR (50) NULL,
[department] NVARCHAR (50) NULL,
[dept_code] NCHAR (2) NULL,
[dob] DATETIME NULL,
[mobile] NCHAR (10) NULL,
[level] NCHAR (2) NULL,
[email_id] NCHAR (50) NULL,
[photo] IMAGE NULL,
[status] NCHAR (1) NULL,
[Password] VARCHAR (50) NULL,
[ Basic_Pay] VARCHAR (50) NULL,
[Tour_Comm_Dt] VARCHAR (50) NULL,
[ Tour_No] VARCHAR (50) NULL,
[Advance_from_Proj] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([empid] ASC)
);
CREATE TABLE [dbo].[TATable] (
[taId] INT IDENTITY (1, 1) NOT NULL,
[Emp_ID] VARCHAR (50) NULL,
[First_name] VARCHAR (50) NULL,
[Last_name] VARCHAR (50) NULL,
[Designation] VARCHAR (50) NULL,
[Department] VARCHAR (50) NULL,
[Mobile] NCHAR (10) NULL,
[Level] NCHAR (10) NULL,
[Basic_pay] NCHAR (10) NULL,
[Tour_comm_Dt] NCHAR (10) NULL,
[Tour_no] NCHAR (10) NULL,
[Advance_from_proj] NCHAR (10) NULL,
PRIMARY KEY CLUSTERED ([taId] ASC)
);
I have fachdata from "emp_mast1" with "empid" in text boxes. Same text boxes value not come from "emp_mast1" table remaining text boxes value fill by user.
When user fill all details and click to save button then "taId" should be generate and same is display in textbox.
i have fill next value with same employee then aoutincreament not working.
namespace ProjectTour
{
public partial class General : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand com;
string str;
SqlConnection con = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=Tour;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;");
protected void Button1_Click(object sender, EventArgs e)
{
clear();
idcheck();
SqlConnection con = new SqlConnection(connStr);
con.Open();
str = "select * from emp_mast1 where empid='" + TextBox1.Text.Trim() + "'";
com = new SqlCommand(str, con);
SqlDataReader reader = com.ExecuteReader();
if (reader.Read())
{
TextBox2.Text = reader["firstname"].ToString();
TextBox3.Text = reader["lastname"].ToString();
TextBox4.Text = reader["designation"].ToString();
TextBox6.Text = reader["department"].ToString();
TextBox8.Text = reader["mobile"].ToString();
reader.Close();
con.Close();
}
}
public void idcheck()
{
SqlConnection con = new SqlConnection(connStr);
con.Open();
str = "select count(*)from emp_mast1 where empid='" + TextBox1.Text + "'";
com = new SqlCommand(str, con);
int count = Convert.ToInt32(com.ExecuteScalar());
if (count > 0)
{
lbl1.Text = "Id Does Exist";
}
else
{
lbl1.Text = "Id Does not Exist";
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd1 = new SqlCommand("INSERT INTO TATable VALUES(@Emp_ID,@First_name,@Last_name,@Designation,@Department,@Mobile,@Level,@Basic_pay,@Tour_comm_Dt,@Tour_no,@Advance_from_proj)", con))
{
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.AddWithValue("@Emp_ID", TextBox1.Text.Trim());
cmd1.Parameters.AddWithValue("@First_name", TextBox2.Text.Trim());
cmd1.Parameters.AddWithValue("@Last_name", TextBox3.Text.Trim());
cmd1.Parameters.AddWithValue("@Designation", TextBox4.Text.Trim());
cmd1.Parameters.AddWithValue("@Department", TextBox5.Text.Trim());
cmd1.Parameters.AddWithValue("@Mobile", TextBox6.Text.Trim());
cmd1.Parameters.AddWithValue("@Level", TextBox7.Text.Trim());
cmd1.Parameters.AddWithValue("@Basic_pay", TextBox8.Text.Trim());
cmd1.Parameters.AddWithValue("@Tour_comm_Dt", TextBox9.Text.Trim());
cmd1.Parameters.AddWithValue("@Tour_no", TextBox10.Text.Trim());
cmd1.Parameters.AddWithValue("@Advance_from_proj", TextBox11.Text.Trim());
con.Open();
cmd1.ExecuteNonQuery();
con.Close();
string userID = this.GenerateID("SELECT (CONVERT(VARCHAR(10),Empid)+CONVERT(VARCHAR(10),taID)) AS 'UserID',FirstName,LastName,Designation FROM tblEmployees WHERE EmpID=" + TextBox1.Text.Trim() + "");
DataTable dt = new DataTable();
txtID.Text = " " + userID;
TextBox1.Text = string.Empty;
TextBox2.Text = string.Empty;
TextBox3.Text = string.Empty;
TextBox4.Text = string.Empty;
TextBox5.Text = string.Empty;
TextBox6.Text = string.Empty;
TextBox7.Text = string.Empty;
TextBox8.Text = string.Empty;
TextBox9.Text = string.Empty;
TextBox10.Text = string.Empty;
TextBox11.Text = string.Empty;
}
}
}
private string GenerateID(string query)
{
string userID = "";
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
userID = sdr["UserID"].ToString();
}
con.Close();
}
}
return userID;
}
}
}
