Databases
StackOverflow2013
Postsdbo
POASP.NET report with parameter
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POASP.NET report with parameter
    text
    copied!<p>I have a stored procedure named "Graph" that should get a value to the parameter @Material, and I created a report in ASP.NET that should show a chart using the data from the stored procedure.</p> <p>However, when I try to load the report I get:</p> <blockquote> <p>An error has occurred during report processing. Cannot create a connection to data source 'PhilipsMaterialsDataSet'. ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'GetData' that has no parameters.</p> </blockquote> <p>I tried different solutions but none of them worked. Also, I'm not sure if I should declare the parameter in the ASP code.</p> <p>( by the way, GetData is not recognized here because it has one parameter (@Material-from the stored procedure) and for some reason, it is called without any parameters )</p> <p>The code behind:</p> <pre><code>using System; using System.Collections.Generic; using System.Linq; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Microsoft.Reporting.WebForms; public partial class StatisticsPage : System.Web.UI.Page { string Connectionstring = "server=(local)\\SQLEXPRESS;database=PhilipsMaterials;Integrated Security=SSPI"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { } } protected void btndisplay_Click(object sender, EventArgs e) { BindReport(); } private void BindReport() { SSRSReport report = new SSRSReport(); SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@Material","453567068441") }; string ReportDataSource = "DataSet1"; bool bind = report.CreateReport(Connectionstring, "graph", sqlParams, ref ReportViewer1, ReportDataSource); if (bind) { ReportViewer1.Visible = true; } } } public class SSRSReport { public SSRSReport() { // // TODO: Add constructor logic here // } public bool CreateReport(String Connectionstring,string StoreProcedureName ,SqlParameter[] Parameter,ref Microsoft.Reporting.WebForms.ReportViewer ReportViewer,string ReportDataSource) { bool reportbind = false; using (SqlConnection con = new SqlConnection(Connectionstring)) { SqlCommand com = new SqlCommand(); com.Connection = con; com.CommandType = CommandType.StoredProcedure; com.CommandText = StoreProcedureName; com.Parameters.AddRange(Parameter); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(com); da.Fill(ds); ReportDataSource datasource = new ReportDataSource(ReportDataSource, ds.Tables[0]); if ( ds.Tables[0].Rows.Count &gt; 0) { ReportViewer.LocalReport.DataSources.Clear(); ReportViewer.LocalReport.DataSources.Add(datasource); //This is another solution I tried: //List&lt;ReportParameter&gt; lstReportParameters = new List&lt;ReportParameter&gt;(); //ReportParameter objReportParameter = new ReportParameter("Material", "453567068441"); //lstReportParameters.Add(objReportParameter); //ReportViewer.LocalReport.SetParameters(lstReportParameters); // ReportViewer.ServerReport.Refresh(); reportbind = true; } } return reportbind; } } </code></pre> <p>The ASP code:</p> <pre><code>&lt;%@ Page Language="C#" AutoEventWireup="true" CodeFile="StatisticsPage.aspx.cs" Inherits="StatisticsPage" %&gt; &lt;%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %&gt; &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head id="Head1" runat="server"&gt; &lt;title&gt;Test SSRS&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;form id="form1" runat="server"&gt; &lt;div&gt; &lt;/div&gt; &lt;rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="610px" Width="1179px" ShowParameterPrompts="true"&gt; &lt;LocalReport ReportPath="Report.rdlc" &gt; &lt;DataSources&gt; &lt;rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" /&gt; &lt;/DataSources&gt; &lt;/LocalReport&gt; &lt;/rsweb:ReportViewer&gt; &lt;asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="PhilipsMaterialsDataSetTableAdapters.GraphTableAdapter" &gt; &lt;/asp:ObjectDataSource&gt; &lt;asp:ScriptManager ID="ScriptManager1" runat="server"&gt; &lt;/asp:ScriptManager&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload