C# Populate Report From SQL Query

Question

I want to select 1 row from DataGridView and then pass that value to my SQL query where clause as @CwAgencyKey. Then I will query the database for the data I want to populate my report's textboxes.

string queryString = "SELECT DISTINCT AGENCY_NAME  " +
                        "FROM CW_AGENCY CA WITH(NOLOCK) " +
                        "INNER JOIN CW_KEYS CK WITH(NOLOCK) ON CK.CW_AGENCY_KEY = CA.CW_AGENCY_KEY " +
                        "INNER JOIN CW_MAST CM WITH(NOLOCK) ON CM.CW_KEY = CK.CW_KEY " +
                        "INNER JOIN AGENCY A WITH(NOLOCK) ON A.AGENCY_KEY = CA.PAYEE_KEY " +
                        "WHERE CA.CW_AGENCY_KEY = @CwAgencyKey";

var dataSet = new DataSet("CheckWriter");

try
{
    var dataAdapter = new SqlDataAdapter(queryString, ConnectDB.connectionDB);
    dataAdapter.SelectCommand.Parameters.AddWithValue("@CwAgencyKey", cwAgencyKey);

    form2.reportViewer1.LocalReport.DataSources.Clear();
    DataTable dt = new DataTable();
    dataAdapter.Fill(dt);

    object agencyName = dt.Rows[0][0];

    Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource(dt.TableName, dt);

    form2.reportViewer1.LocalReport.ReportPath = "../../Report1.rdlc";

    form2.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);

    ReportParameter payeeReport = new ReportParameter("payeeReport");
    payeeReport.Values.Add(agencyName.ToString());

    form2.reportViewer1.LocalReport.SetParameters(payeeReport);
    form2.reportViewer1.RefreshReport();
}
catch (SqlException ex)
{
    MessageBox.Show("SQL Error: " + ex);
}

This code produces an error at form2.reportViewer1.LocalReport.SetParameters(payeeReport);

Microsoft.Reporting.WinForms.LocalProcessingException: 'An error occurred during local report processing.'

Can anyone help with the correct way to populate a report from query?

I set a parameter in Report Data named payeeReport and set textbox1 expression to that parameter. — Dale Wahl, Aug 15 '19 at 11:52
Putting the results of my query to list and then using the code you suggested appears to help. Report1.rdlc needs a DataSet to populate the textbox, so I put the query above in a stored procedure and call that stored procedure. How do I pass the parameter @CwAgencyKey to the DataSet? — Dale Wahl, Aug 16 '19 at 03:27
I got it buy creating a stored procedure that excepts a parameter for @CwAgencyKey. Then I created a DataSet in Report1.rdlc (Report Data). In Form1.DataGridView double click event I perform all the work. — Dale Wahl, Aug 16 '19 at 10:53

score 0 · Answer 1 · answered Aug 16 '19 at 11:11

I got it to work!!!

I created a stored procedure using the original posted query, it excepts 1 parameter (@CwAgencyKey). I created a DataSet in Report1.rdlc (Report Data). In Form1 DataGridView's double click event I pass the specified value to the DataSet, then refresh reportViewer1 and show Form2.

private void dataGridView1_DoubleClick(object sender, EventArgs e)
{
    int cwAgencyKey;
    Form2 form2 = new Form2();
    int rowindex = this.dataGridView1.CurrentRow.Index;
    string strCwAgencyKey = "";
    strCwAgencyKey = this.dataGridView1.Rows[rowindex].Cells[0].Value.ToString().Trim();
    cwAgencyKey = Int32.Parse(strCwAgencyKey);
    // TODO: This line of code loads data into the 'databaseDataSet.storedProc' table. You can move, or remove it, as needed.
    form2.storedProcTableAdapter.Fill(form2.databaseDataSet.storedProc, cwAgencyKey);
    form2.reportViewer1.RefreshReport();
    form2.Show();
}

C# Populate Report From SQL Query

1 Answers1