I have followed this blog and I have been able to successfully bind a dynamic dataset with a Tablix in a ReportViewer - https://blogs.msdn.microsoft.com/sqlforum/2011/04/27/walkthrough-assign-dataset-dynamically-created-in-code-to-your-local-report-with-reportviewer/
Code:
private void button1_Click(object sender, EventArgs e)
{
//Check if both selection criteria are present
if (comboBox1.Text == "" || comboBox2.Text == "")
{
MessageBox.Show("Please select employee", "No Selection", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
/* Generate payslip */
//Get dept name and role
DataSet dsDept = PanktiLa("select r.roledesc, d.depname from emprole r INNER JOIN deptmaster d on r.DEPSRNO = d.srno where r.empsrno = " + Convert.ToInt32(ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["SRNO"]));
//Get PF no and ESI no
//We assume 1 = PF & 2 = ESI in DocMaster (I would like to keep these two values permanent)
string pf = "-";
string esi = "-";
DataSet dsDocs = PanktiLa("select * from empdocs where empsrno = " + Convert.ToInt32(ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["SRNO"]));
if (dsDocs.Tables[0].Rows.Count > 0) //If there are records
foreach (DataRow dr in dsDocs.Tables[0].Rows) //Cycle through dataset to find doc numbers
{
if (dr["DOCSRNO"].ToString() == "1") //This is PF#
pf = dr["DOCDESC"].ToString();
if (dr["DOCSRNO"].ToString() == "2") //This is ESI#
esi = dr["DOCDESC"].ToString();
}
//Get bank ac no
string khatano = "-";
DataSet dsBank = PanktiLa("select ACNO from empbank where empsrno = " + Convert.ToInt32(ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["SRNO"]));
if (dsBank.Tables[0].Rows.Count > 0) //If there are records
khatano = dsBank.Tables[0].Rows[0]["ACNO"].ToString();
/*===========================================================================*/
//Populate earning & deduction datasets
//To hold earnings
DataTable dtE = new DataTable();
dtE.Columns.Add("SALNAME");
dtE.Columns.Add("AMT");
//To hold deductions
DataTable dtD = new DataTable();
dtD.Columns.Add("SALNAME");
dtD.Columns.Add("AMT");
//Get all salary heads for the employee-month
DataSet dsPYSalary = PanktiLa("select p.FKSRNO, p.SALCODE, p.AMT, s.SALNAME, s.SALTYPE from PYSalary p INNER JOIN SalaryMaster s ON s.SALCODE = p.SALCODE where FKSRNO = " + Convert.ToInt32(ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["SRNO"]));
//Segregate and label them based on earnings and deductions
foreach (DataRow dr in dsPYSalary.Tables[0].Rows)
{
if (dr["SALTYPE"].ToString() == "Earning") //Head is of earning type
{
//Populate earning dataset 'dsE'
DataRow drE = dtE.NewRow();
drE["SALNAME"] = dr["SALNAME"];
drE["AMT"] = dr["AMT"];
//dsE.Tables[0].Rows.Add(drE);
dtE.Rows.Add(drE);
}
else if (dr["SALTYPE"].ToString() == "Deduction") //Head is of deduction type
{
//Populate deduction dataset 'dsD'
DataRow drD = dtD.NewRow();
drD["SALNAME"] = dr["SALNAME"];
drD["AMT"] = dr["AMT"];
//dsD.Tables[0].Rows.Add(drD);
dtD.Rows.Add(drD);
}
}
//Populate OT
//Get data
DataSet dsPYOT = PanktiLa("select p.FKSRNO, p.OTSRNO, p.OQTY, p.OTAMT, m.OTID, m.OTNAME from PYOT p INNER JOIN OTMaster m ON p.OTSRNO = m.SRNO where FKSRNO = " + Convert.ToInt32(ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["SRNO"]));
foreach (DataRow dr in dsPYOT.Tables[0].Rows)
{
//Populate earning dataset 'dsE'
DataRow drO = dtE.NewRow();
drO["SALNAME"] = dr["OTNAME"];
drO["AMT"] = dr["OTAMT"];
dtE.Rows.Add(drO);
}
//Add table to dataset
DataSet dsE = new DataSet();
dsE.Tables.Add(dtE);
DataSet dsD = new DataSet();
dsD.Tables.Add(dtD);
//Bind datasources
dsE.Tables[0].TableName = "dtEarnings";
this.dtEarningsBindingSource.DataSource = dsE;
dsD.Tables[0].TableName = "dtDeductions";
this.dtDeductionsBindingSource.DataSource = dsD;
/*===========================================================================*/
//Define parameters
ReportParameter _rpMonth = new ReportParameter("rpMonth", comboBox2.Text);
ReportParameter _rpName = new ReportParameter("rpName", ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["EMPNAME"].ToString());
ReportParameter _rpID = new ReportParameter("rpEmpID", ds_combo1.Tables[0].Rows[comboBox1.SelectedIndex]["EMPID"].ToString());
ReportParameter _rpDept = new ReportParameter("rpDept", dsDept.Tables[0].Rows[0]["DEPNAME"].ToString());
ReportParameter _rpRole = new ReportParameter("rpRole", dsDept.Tables[0].Rows[0]["ROLEDESC"].ToString());
ReportParameter _rpPF = new ReportParameter("rpPFNO", pf);
ReportParameter _rpESI = new ReportParameter("rpESINO", esi);
ReportParameter _rpBankAcNo = new ReportParameter("rpBankAcNo", khatano);
//Set parameters
reportViewer1.LocalReport.SetParameters(new ReportParameter[]
{ _rpMonth, _rpName, _rpID, _rpDept, _rpRole, _rpPF, _rpESI, _rpBankAcNo });
//Refresh report
reportViewer1.RefreshReport();
}
private DataSet PanktiLa(string queryle)
{
string connString = System.IO.File.ReadAllText("D:\\Payroll\\Payroll.txt") + " User ID = sa; Password = DEMO";
DataSet ds_local = new DataSet();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(queryle, conn);
SqlDataAdapter adapter = new SqlDataAdapter();
conn.Open();
adapter.SelectCommand = cmd;
adapter.Fill(ds_local);
return (ds_local);
}
}
The tablix even generates the number of rows present in the dataset. But it does not populate the rows with data in the dataset.
What am I missing?
Note: I noticed that although the DataSet binds to the dataTable and in turn to the ReportViewer, there is no 1-to-1 binding between the DataSet Columns of the ReportViewer and that of the RDLC Report. Is there anything to be done there?
I am coding using WinForms in C# using VS2015 with SQL Server 2008 R2 Express and .Net 4.5.2
Problem solved. The fields of the datatable must have the same names as the columns defined for the dataset in the code.