Dear expert
Trying to populate text boxes in a form by joining 3 fields.
However I am not getting the return values.
public void LoadEmployee()
{
string str;
str = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnection sqlcon = new SqlConnection(str);
if (sqlcon.State == ConnectionState.Open)
{
sqlcon.Close();
}
sqlcon.Open();
lblstatus.Text = "CONNECTED";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
cmd.Connection = sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GET_ALL_EMPLOYEE";
da.SelectCommand = cmd;
da.Fill(ds);
DataTable dt = new DataTable();
dt = ds.Tables[0];
txt_Empname.Items.Clear();
for (int i = 0; i < dt.Rows.Count; i++)
{
txt_Empname.Items.Add(dt.Rows[i]["STAFFNAME"].ToString());
}
txt_Empname.SelectedIndex = 0;
}
protected void txt_Empname_SelectedIndexChanged(object sender, EventArgs e)
{
Session["txt_Empname"] = txt_Empname.SelectedValue;
txt_Empname.SelectedIndex = txt_Empname.Items.IndexOf(txt_Empname.Items.FindByValue(Convert.ToString(Session["txt_Empname"])));
GetEmpDetails(Session["txt_Empname"].ToString());
}
public void GetEmpDetails(string PosCode)
{
string str;
str = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
SqlConnection sqlcon = new SqlConnection(str);
if (sqlcon.State == ConnectionState.Open)
{
sqlcon.Close();
}
sqlcon.Open();
lblstatus.Text = "CONNECTED";
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
cmd.Connection = sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GET_EMPDETAILS";
SqlParameter param1 = new SqlParameter();
param1.ParameterName = "STAFFNAME";
param1.Direction = ParameterDirection.Input;
param1.SqlDbType = SqlDbType.VarChar;
param1.Value = PosCode;
param1.Value = Session["txt_Empname"];
cmd.Parameters.Add(param1);
da.SelectCommand = cmd;
da.Fill(ds);
DataTable dt = ds.Tables[0];
txt_Empno.Text = dt.Rows[0]["EMPNO"].ToString();
txt_Dept_No.Text = dt.Rows[0]["DEPT_NO"].ToString();
txt_Dept_Name.Text = dt.Rows[0]["DEPT_MAME"].ToString();
txt_Br_No.Text = dt.Rows[0]["BRN_NO"].ToString();
txt_Brn_Name.Text = dt.Rows[0]["BRN_NAME"].ToString();
}
}
The stored procedures are as follows ::
ALTER PROCEDURE [dbo].[GET_ALL_EMPLOYEE]
AS
BEGIN
SET NOCOUNT ON;
SELECT FNAME+' '+LNAME+' '+MID_NAME AS STAFFNAME,
EMPNO,BRANAME,BRANCH,DEPT,DEPTNAME
FROM PAYMAST ORDER BY FNAME
END
---------------------------------------------------------------
ALTER PROCEDURE [dbo].[GET_EMPDETAILS]
@STAFFNAME VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT EMPNO,BRANAME,BRANCH,DEPT,DEPTNAME
FROM PAYMAST
WHERE FNAME+' '+LNAME+' '+MID_NAME= @STAFFNAME
END
Please assist
Thanks