how to use SSRS report with multiple parameters using C#

Question

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", "<!-- Inject Script Filtered -->", false);

//}

cs.Close();

}