get data from datatable to datagridview

[judithSampathwa]

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

[lolafuertes]

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

Is that right?

Hope this helps

[judithSampathwa]

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

Is that right?

Hope this helps

yes ,

thank you

[lolafuertes]

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

Thanks in advance

[judithSampathwa]

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??????