Using sqlcommand to fill reportviewer but only selected fields get filled in from one table how do i add another table?

Question

I am working on an etime history application, the data is brought into sql from adp application.

Requirement

User wants to see old time sheet information for a particular employee

Process

User clicks on 1) paygroup from drop down 2) the employees drop down get grouped by the paygroup 3) Selects (From Date) and (To Date) 4) Clicks the Run Report and Data is filled in

Filling the drop down's work flawlessly, it is when i click the run report only columns paygroup, empid, and date get filled but columns DeptNo, WorkCenterID, JobCode, InPunch, OutPunch are not getting filled. Oh and the columns being filled in are in the table EtimePunchDetail, the others are in EtimeEmployees, how do i use EtimeEmployees and the fields in my SqlCommand is my question? These fields are not parameters so thats where i got confused because the others are parameters...

The code i used to fill the columns

private void button1_Click(object sender, EventArgs e) //run report button loads all the pieces onto the report.
        {

            if (FromDate.Value > ToDate.Value)
            {
                MessageBox.Show("From Date Must be Less Than To Date");
            }
            else
                //fileexport
                using (MSSQL.SqlConnection connection = new MSSQL.SqlConnection(constr))
                {
                    timepunchnew = new EtimeHistoryDataSet();
                    connection.Open();
                    MSSQL.SqlCommand command = new MSSQL.SqlCommand("SELECT * From EtimePunchDetail WHERE (EmpID = @empid) And  (Paygroup = @paygroup) And (TransDate >= @fromdate) And (TransDate <= @todate)", connection);
                    {

                        MSSQL.SqlParameter parmEmp = new MSSQL.SqlParameter();
                        parmEmp.ParameterName = "@empid";
                        parmEmp.Value = Employee.SelectedValue;
                        command.Parameters.Add(parmEmp);

                        MSSQL.SqlParameter paramPayGroup = new MSSQL.SqlParameter();
                        paramPayGroup.ParameterName = "@paygroup";
                        paramPayGroup.Value = Paygroup.SelectedValue;
                        command.Parameters.Add(paramPayGroup);

                        MSSQL.SqlParameter paramFromDate = new MSSQL.SqlParameter();
                        paramFromDate.ParameterName = "@fromdate";
                        paramFromDate.DbType = DbType.DateTime;
                        paramFromDate.SqlDbType = SqlDbType.DateTime;
                        paramFromDate.Value = FromDate.Value.ToString("");
                        command.Parameters.Add(paramFromDate);

                        MSSQL.SqlParameter paramToDate = new MSSQL.SqlParameter();
                        paramToDate.ParameterName = "@todate";
                        paramToDate.DbType = DbType.DateTime;
                        paramToDate.SqlDbType = SqlDbType.DateTime;
                        paramToDate.Value = ToDate.Value;;
                        command.Parameters.Add(paramToDate);

                        MSSQL.SqlDataAdapter EtimePunchDetailTableAdapter = new System.Data.SqlClient.SqlDataAdapter();
                        EtimePunchDetailTableAdapter.SelectCommand = command;
                        EtimePunchDetailTableAdapter.Fill(timepunchnew, "Etime");

                        string exeFolder = Path.GetDirectoryName(Application.ExecutablePath);
                        string reportPath = exeFolder + @"\Report1.rdlc";

                        reportViewer1.LocalReport.DisplayName = MakeValidFileName(Employee.Text) + "-" + FromDate.Value.ToString("MM") + "!" + (FromDate.Value.ToString("dd")) + "!" + FromDate.Value.ToString("yyyy") + "-" + ToDate.Value.ToString("MM") + "!" + ToDate.Value.ToString("dd") + "!" + ToDate.Value.ToString("yyyy");

                        reportViewer1.LocalReport.ReportPath = reportPath;

                        reportViewer1.LocalReport.DataSources.Clear();

                        reportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WinForms.ReportDataSource("EtimeHistoryDataSet_Etime", timepunchnew.Etime));

                        reportViewer1.RefreshReport();
                        reportViewer1.Refresh();
                        reportViewer1.Visible = true;

                    }
                }
        }

Answer 1

You can add a join in your SQL statement to get the records from the other table.

In your case...

SELECT pd.*, e.EmployeeNameOrWhateverFieldYouWant 
FROM EtimePunchDetail pd  INNER JOIN EtimeEmployee e ON e.EmpID = pd.EmpID   
WHERE (pd.EmpID = @empid) And  (pd.Paygroup = @paygroup) 
And (pd.TransDate >= @fromdate) And (pd.TransDate <= @todate)

Note the addition of the table aliases which make referencing the table more convenient, since you'll need to qualify any fields that exist in both tables (otherwise SQL can't tell which table you're trying to get data from and throws an error).

You only need to add the fields from the employee table as parameters if you need to use them to filter your results. They'll work just fine in the select list.