Hi kankon,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespaces
C#
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
VB.Net
Imports System.Configuration
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Code
C#
private void BindReport(string query)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
Customers dsCustomers = GetData(query);
ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
}
protected void Submit(object sender, EventArgs e)
{
string query = "SELECT CustomerID,ContactName,City,Country FROM Customers";
string condition = string.Empty;
foreach (ListItem item in ddlCountries.Items)
{
condition += item.Selected ? string.Format("'{0}',", item.Value) : "";
}
if (!string.IsNullOrEmpty(condition))
{
condition = string.Format(" WHERE Country IN ({0})", condition.Substring(0, condition.Length - 1));
}
this.BindReport(query + condition);
}
private Customers GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["NORTHWINDConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (Customers dsCustomers = new Customers())
{
sda.Fill(dsCustomers, "DataTable1");
return dsCustomers;
}
}
}
}
}
VB.Net
Private Sub BindReport(ByVal query As String)
ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
Dim dsCustomers As Customers = GetData(query)
Dim datasource As ReportDataSource = New ReportDataSource("Customers", dsCustomers.Tables(0))
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(datasource)
End Sub
Protected Sub Submit(ByVal sender As Object, ByVal e As EventArgs)
Dim query As String = "SELECT CustomerID,ContactName,City,Country FROM Customers"
Dim condition As String = String.Empty
For Each item As ListItem In ddlCountries.Items
condition += If(item.Selected, String.Format("'{0}',", item.Value), "")
Next
If Not String.IsNullOrEmpty(condition) Then
condition = String.Format(" WHERE Country IN ({0})", condition.Substring(0, condition.Length - 1))
End If
Me.BindReport(query & condition)
End Sub
Private Function GetData(ByVal query As String) As Customers
Dim conString As String = ConfigurationManager.ConnectionStrings("NORTHWINDConnectionString").ConnectionString
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand(query, con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Using dsCustomers As Customers = New Customers()
sda.Fill(dsCustomers, "DataTable1")
Return dsCustomers
End Using
End Using
End Using
End Using
End Function
Screenshot
