how to use SSRS report with multiple parameters using C#

I am facing such a problem for passing multiple parameter to report viewer,,,

i m using parameter like @Category, @Year, @DisabilityCode etc.. inside our aspx page .

but an error occured "plz declare @Category Scalar variable"..

ssrs report bind with dataset "dsEmployee" in their no parameter assign like @Category,or other..

now i hv a query how to declare a parameter in dataset , datatable1

and where will i change my code.. can i use param tag n how can i put this in my code..

please help me friends...

dataset structure:-

report.rdlc:

code:

public void BindReport()

{

cs.Open();

string Query = string.Empty;

Query = @"SELECT ROW_NUMBER() OVER (ORDER BY D.RegDateTime DESC)as SNo,

(select case when D.Status='E' then D.TransactionID

when D.Status='R' then D.TransactionID

else

D.RegID end) as RegTID, D.RegID, D.FName + ' ' + D.LName AS Name, D.FatherName,

CONVERT(nvarchar(50), D.DOB, 105) AS DoB,S.StatusName as App_Status,d1.dist_nm1 as distName, C.CategoryNameEng AS Category, D.Gender,

D.UrbanRural, D.Mobile, DT.DisNameEng AS Disability, CONVERT(nvarchar(50),

D.RegDateTime, 105) AS RegDate

FROM DisableRegistration D,Category C,StatusMaster S,Dist d1,DisabledType DT

where D.Category=C.CategoryCode and D.Status = S.SCode and D.DistCode = d1.dist_code and D.DisabilityCode = DT.DisCode";

if (ddlCategory.SelectedValue.ToString().Trim() == "00" && ddlDisability.SelectedValue.ToString().Trim() == "00" && ddlYear.SelectedValue.ToString().Trim() == "00" && ddlUrbanRural.SelectedValue.ToString().Trim() == "00" && ddlCityBlock.SelectedValue.ToString().Trim() == "00")

{

Query += " order by D.RegDateTime desc";

}

else if (ddlCategory.SelectedValue.ToString().Trim() != "00")

{

Query += " AND C.CategoryCode=@Category";

}

else if (ddlDisability.SelectedValue.ToString().Trim() != "00")

{

Query += " AND DT.DisCode=@Disability";

}

else if (ddlYear.SelectedValue.ToString().Trim() != "00")

{

Query += " AND DATEPART(yyyy,RegDateTime)=@Year";

}

else if (ddlUrbanRural.SelectedValue.ToString().Trim() != "00")

{

Query += " AND D.UrbanRural=@UrbanRural";

if (ddlUrbanRural.SelectedValue.ToString().Trim() == "U" && ddlCityBlock.SelectedValue.ToString().Trim() != "00")

{

Query += " AND D.CityName=@CityName";

}

else if (ddlUrbanRural.SelectedValue.ToString().Trim() == "R" && ddlCityBlock.SelectedValue.ToString().Trim() != "00")

{

Query += " AND D.BlockName=@BlockName";

}

cmd = new SqlCommand(Query, cs);

try

{

cmd.Parameters.AddWithValue("@Category", ddlCategory.SelectedValue.ToString().Trim());

cmd.Parameters.AddWithValue("@Disability", ddlDisability.SelectedValue.ToString().Trim());

cmd.Parameters.AddWithValue("@Year", ddlYear.SelectedValue.ToString().Trim());

cmd.Parameters.AddWithValue("@UrbanRural", ddlUrbanRural.SelectedValue.ToString().Trim());

cmd.Parameters.AddWithValue("@CityName", ddlCityBlock.SelectedValue.ToString().Trim());

cmd.Parameters.AddWithValue("@BlockName", ddlCityBlock.SelectedValue.ToString().Trim());

//set Processing Mode of Report as Local

ReportViewer1.ProcessingMode = ProcessingMode.Local;

//set path of the Local report

ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/DistOfficer/Report.rdlc");

//creating object of DataSet dsEmployee and filling the DataSet using SQLDataAdapter

SqlDataAdapter adapt = new SqlDataAdapter(Query.ToString(), cs);

DataSet ds = new DataSet();

adapt.Fill(ds, "dsEmployeey");

//Providing DataSource for the Report

ReportDataSource rds = new ReportDataSource("dsEmployee", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Clear();

//Add ReportDataSource

ReportViewer1.LocalReport.DataSources.Add(rds);

}

catch

{

}

//}

//else

//{

// ReportViewer1.LocalReport.DataSources.Clear();

// ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "alert", "", false);

//}

cs.Close();

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
This might be happening with your if else condition query. In the cmd.Parameters.Add() function, you are passing all the parameters like @Category and so on but your query depends on your if else condition.

Add cmd.Parameters.Add() with the if else condition like
else if (ddlCategory.SelectedValue.ToString().Trim() != "00")
{
Query += " AND C.CategoryCode=@Category";
cmd.Parameters.AddWithValue("@Category", ddlCategory.SelectedValue.ToString().Trim());
}
Do this with the rest of your parameters and remove all the parameters that you had passed under the try block.

Hope this helps
Abhishek Chadha9y ago
use your parameters in a string query with concatenation method

else if (ddlCategory.SelectedValue.ToString().Trim() != "00")
{
Query += " AND C.CategoryCode="+@Category
}