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:
DataTable dtE = new DataTable();
dtE.Columns.Add("SALNAME");
dtE.Columns.Add("AMT");
DataTable dtD = new DataTable();
dtD.Columns.Add("SALNAME");
dtD.Columns.Add("AMT");
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"]));
foreach (DataRow dr in dsPYSalary.Tables[0].Rows)
{
if (dr["SALTYPE"].ToString() == "Earning")
{
DataRow drE = dtE.NewRow();
drE["SALNAME"] = dr["SALNAME"];
drE["AMT"] = dr["AMT"];
dtE.Rows.Add(drE);
}
else if (dr["SALTYPE"].ToString() == "Deduction")
{
DataRow drD = dtD.NewRow();
drD["SALNAME"] = dr["SALNAME"];
drD["AMT"] = dr["AMT"];
dtD.Rows.Add(drD);
}
}
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)
{
DataRow drO = dtE.NewRow();
drO["SALNAME"] = dr["OTNAME"];
drO["AMT"] = dr["OTAMT"];
dtE.Rows.Add(drO);
}
DataSet dsE = new DataSet();
dsE.Tables.Add(dtE);
DataSet dsD = new DataSet();
dsD.Tables.Add(dtD);
dsE.Tables[0].TableName = "dtEarnings";
this.dtEarningsBindingSource.DataSource = dsE;
dsD.Tables[0].TableName = "dtDeductions";
this.dtDeductionsBindingSource.DataSource = dsD;
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);
reportViewer1.LocalReport.SetParameters(new ReportParameter[]
{ _rpMonth, _rpName, _rpID, _rpDept, _rpRole, _rpPF, _rpESI, _rpBankAcNo });
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?
I am coding using WinForms in C# using VS2015 with SQL Server 2008 R2 Express and .Net 4.5.2
What I have tried:
I tried deleting the controls and adding them back..