hi,

i have a question in data tables. i am coding in C#. my scenario is employees have many benefits in a company such as life, health, dental, 401k and such...
i have a tabel in MSSQL database to represent the employee data and a emp benefit table to hold the employee benefit data. i want to display all the employee benefits in a datagriview. i an using the 3 tier architecture.

in the form change textbox..i have written the below code

private void cmbEmpName_TextChanged(object sender, EventArgs e)
        {
            EmpName = cmbEmpName.Text;
            string EmpIDBen = empbenefitsBus.GetEmpID(EmpName);
            
            string[,] benefit = empbenefitsBus.GetEmpBenefits(EmpIDBen);

            for (int r = 0; r < benefit.Length; r++)
            {
                dgvBenefit.Rows[r].Cells[0].Value = benefit[r, 0].ToString();
               
            }
        }

in the business layer i have the below code.

public string GetEmpID(string EmpName)
        {
            string SysEmpID;
            DataTable dataTable = new DataTable();
            empbenefitDal = new EmpBenefitDAL();

            dataTable = empbenefitDal.GetEmpID(EmpName);                
            
            SysEmpID = dataTable.Rows[0]["EmpID"].ToString();

            return SysEmpID;           
        }

        public string[,] GetEmpBenefits(string SysEmpID)
        {
            DataTable dataTable = new DataTable();
            empbenefitDal = new EmpBenefitDAL();

            dataTable = empbenefitDal.getEmpBenefit(SysEmpID);
            empbenefit = new EmpBenefit();

            string[,] benefit = new string[dataTable.Rows.Count, dataTable.Columns.Count];
            
            for (int r = 0; r < dataTable.Rows.Count; r++)
            {
                for (int c = 0; c < dataTable.Columns.Count; c++)
                {
                    benefit[r, c] = dataTable.Rows[r][c].ToString();                   
                }
            }
            return benefit;
        }

and in the data access layer i have the below code...

public DataTable GetEmpID(string EmpName)
        {
            string query = string.Format("Select EmpID From EmpDetails Where (FirstName + ' ' + LastName)=@Empname");
            SqlParameter[] sqlParameter = new SqlParameter[1];

            sqlParameter[0] = new SqlParameter("@Empname",SqlDbType.VarChar);
            sqlParameter[0].Value = Convert.ToString(EmpName);

            return conn.executeSelectQuery(query,sqlParameter);
        }

        public DataTable getEmpBenefit(string SysEmpID)
        {
           int ID = Int32.Parse(SysEmpID);
            string query = string.Format("Select * Where EmpID=@sysEID");
            //,EligibalDate,BStartDate,BEndDate,BID,Emp From EmpBenefit

            SqlParameter[] sqlParameter = new SqlParameter[1];

            sqlParameter[0] = new SqlParameter("@sysEID",SqlDbType.Int);
            sqlParameter[0].Value = Convert.ToString(ID);

            return conn.executeSelectQuery(query,sqlParameter);        

        }

what i want to is to retrieve all the benefits that the employee is in, but how do i get it to a datatable and display it in a datagridview. and employee may have many benefits
how can i do this, the code i wrote dosen't execute?

thank you

It seems that you miss the From EmpBenefit clause in yor DAL code line 15.

Is that right?

Hope this helps

It seems that you miss the From EmpBenefit clause in yor DAL code line 15.

Is that right?

Hope this helps

yes ,

thank you

If this solved your question, please be so kind to mark the thread as solved.

Thanks in advance

If this solved your question, please be so kind to mark the thread as solved.

Thanks in advance

the same thing happened again?

string BenefitName = empbenefitsBus.ValidateBenefitForEmp(cmbBenefit.Text, EmployeeID);

 public string ValidateBenefitForEmp(string BName, string EmpID)
        {
            DataTable dataTable = new DataTable();
            empbenefitDal = new EmpBenefitDAL();

            dataTable = empbenefitDal.ValidateBenefitForEmp(BName, EmpID);

            string number = dataTable.Rows[0]["Number"].ToString();

            return number;           
        }


public DataTable ValidateBenefitForEmp(string BName, string EmpID)
        {
            string query = string.Format("Select COUNT(BName) as Number From EmpBenefit Where EmpID=@epid and BName =@bname");
            SqlParameter[] sqlParameter = new SqlParameter[2];

            sqlParameter[0] = new SqlParameter("@epid", SqlDbType.VarChar);
            sqlParameter[0].Value = Convert.ToString(BName);
            sqlParameter[1] = new SqlParameter("@bname", SqlDbType.VarChar);
            sqlParameter[1].Value = Convert.ToString(EmpID);
            
            return conn.executeSelectQuery(query,sqlParameter);
        }


public DataTable executeSelectQuery(string query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();

            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                MessageBox.Show("Error - Connection.executeSelectQuery - Query: " + query + "\nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            { 
            }
            return dataTable;
        }

what is wrong in this code the same error came again??????