Hi SaChie,
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
HTML
<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlCountries" runat="server">
<asp:ListItem Text="USA" Value="USA"></asp:ListItem>
<asp:ListItem Text="UK" Value="UK"></asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlCities" runat="server">
<asp:ListItem Text="Seattle" Value="Seattle"></asp:ListItem>
<asp:ListItem Text="Tacoma" Value="Tacoma"></asp:ListItem>
<asp:ListItem Text="Kirkland" Value="Kirkland"></asp:ListItem>
<asp:ListItem Text="Redmond" Value="Redmond"></asp:ListItem>
<asp:ListItem Text="London" Value="London"></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnFilter" runat="server" Text="Search" OnClick="Filter" />
<br />
<CR:CrystalReportViewer ID="crPersonInformation" runat="server" AutoDataBind="true"
EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" />
</div>
</form>
</body>
</html>
Namespaces
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
VB.Net
Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data.SqlClient
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
BingReport("", "");
}
protected void Filter(object sender, EventArgs e)
{
BingReport(ddlCountries.SelectedValue, ddlCities.SelectedValue);
}
private void BingReport(string country, string city)
{
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("~/EmployeesDetail.rpt"));
Employees dsPersonInformations = GetEmployees(country, city);
crystalReport.SetDataSource(dsPersonInformations);
crPersonInformation.ReportSource = crystalReport;
}
private Employees GetEmployees(string country, string city)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand("SELECT EmployeeID,LastName,FirstName,BirthDate FROM Employees WHERE (Country = @Country OR @Country IS NULL) AND (City = @City OR @City IS NULL)");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Country", !string.IsNullOrEmpty(country) ? country : (object)DBNull.Value);
cmd.Parameters.AddWithValue("@City", !string.IsNullOrEmpty(city) ? city : (object)DBNull.Value);
sda.SelectCommand = cmd;
using (Employees dsEmployees = new Employees())
{
sda.Fill(dsEmployees, "Employee");
return dsEmployees;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
BingReport("", "")
End Sub
Protected Sub Filter(ByVal sender As Object, ByVal e As EventArgs)
BingReport(ddlCountries.SelectedValue, ddlCities.SelectedValue)
End Sub
Private Sub BingReport(ByVal country As String, ByVal city As String)
Dim crystalReport As ReportDocument = New ReportDocument()
crystalReport.Load(Server.MapPath("~/EmployeesDetail.rpt"))
Dim dsPersonInformations As Employees = GetEmployees(country, city)
crystalReport.SetDataSource(dsPersonInformations)
crPersonInformation.ReportSource = crystalReport
End Sub
Private Function GetEmployees(ByVal country As String, ByVal city As String) As Employees
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand("SELECT EmployeeID,LastName,FirstName,BirthDate FROM Employees WHERE (Country = @Country OR @Country IS NULL) AND (City = @City OR @City IS NULL)")
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
cmd.Parameters.AddWithValue("@Country", If(Not String.IsNullOrEmpty(country), country, CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@City", If(Not String.IsNullOrEmpty(city), city, CObj(DBNull.Value)))
sda.SelectCommand = cmd
Using dsEmployees As Employees = New Employees()
sda.Fill(dsEmployees, "Employee")
Return dsEmployees
End Using
End Using
End Using
End Function
Screenshot
