Hi Methoun,
Check this example. Now please take its reference and correct your code.
Using the below article i have created the example. For this example I have used of NorthWind database Orders Table.
Generate Crystal Reports using Strongly Typed DataSet in ASP.Net using C# and VB.Net
SQL
CREATE PROCEDURE Orders_GetOrdersBasedOnCountryAndDate
@ShipCountry VARCHAR(20) = NULL,
@OrderFromDate DATETIME = NULL,
@OrderToDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT [CustomerID]
,[ShipAddress]
,[ShipCountry]
,[OrderDate]
FROM [dbo].[Orders]
WHERE (ShipCountry = @ShipCountry OR @ShipCountry IS NULL)
AND ((OrderDate BETWEEN @OrderFromDate AND @OrderToDate)
OR (@OrderFromDate IS NULL AND @OrderToDate IS NULL))
END
GO
HTML
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<%@ 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">
<cc1:ToolkitScriptManager runat="server">
</cc1:ToolkitScriptManager>
<div>
<asp:DropDownList ID="ddlCompanyName" runat="server" Height="25px" Width="120px">
<asp:ListItem Text="Germany" Value="Germany"></asp:ListItem>
<asp:ListItem Text="Brazil" Value="Brazil"></asp:ListItem>
<asp:ListItem Text="France" Value="France"></asp:ListItem>
<asp:ListItem Text="Belgium" Value="Belgium"></asp:ListItem>
<asp:ListItem Text="Switzerland" Value="Switzerland"></asp:ListItem>
</asp:DropDownList>
From
<asp:TextBox ID="txtFromD" runat="server" Text="1996-07-01"></asp:TextBox>
<asp:ImageButton ID="ImgCal1" runat="server" ImageAlign="Top" ImageUrl="~/calendar.png"
Width="30px" />
<cc1:CalendarExtender ID="txtFromD_CalendarExtender" runat="server" Enabled="True"
PopupButtonID="ImgCal1" PopupPosition="TopLeft" TargetControlID="txtFromD" Format="yyyy-MM-dd">
</cc1:CalendarExtender>
To :
<asp:TextBox ID="txtToD" runat="server" Text="1997-07-01"></asp:TextBox>
<cc1:CalendarExtender ID="txtToD_CalendarExtender" runat="server" Enabled="True"
PopupButtonID="ImgCal" PopupPosition="TopLeft" TargetControlID="txtToD" Format="yyyy-MM-dd">
</cc1:CalendarExtender>
<asp:ImageButton ID="ImgCal" runat="server" ImageAlign="Top" ImageUrl="~/calendar.png"
Width="30px" />
<asp:Button ID="btnSearchTerm" runat="server" Text="Search" OnClick="SearchClick" />
<br />
<CR:CrystalReportViewer ID="crPersonInformation" runat="server" AutoDataBind="true"
EnableDatabaseLogonPrompt="False" EnableParameterPrompt="False" DisplayGroupTree="False" />
</div>
</form>
</body>
</html>
Namespaces
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BingReport("", "", "");
}
}
protected void SearchClick(object sender, EventArgs e)
{
this.BingReport(txtFromD.Text, txtToD.Text, ddlCompanyName.SelectedItem.Text);
}
private void BingReport(string fromDate, string toDate, string country)
{
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("~/OrderDetails.rpt"));
Employees dsPersonInformations = GetEmployees(fromDate, toDate, country);
crystalReport.SetDataSource(dsPersonInformations);
crPersonInformation.ReportSource = crystalReport;
}
private Employees GetEmployees(string fromDate, string toDate, string country)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand("Orders_GetOrdersBasedOnCountryAndDate");
cmd.CommandType = CommandType.StoredProcedure;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@ShipCountry", !string.IsNullOrEmpty(country) ? country : (object)DBNull.Value);
cmd.Parameters.AddWithValue("@OrderFromDate", !string.IsNullOrEmpty(fromDate) ? fromDate : (object)DBNull.Value);
cmd.Parameters.AddWithValue("@OrderToDate", !string.IsNullOrEmpty(toDate) ? toDate : (object)DBNull.Value);
sda.SelectCommand = cmd;
using (Employees dsEmployees = new Employees())
{
dsEmployees.EnforceConstraints = false;
sda.Fill(dsEmployees, "Employee");
return dsEmployees;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BingReport("", "", "")
End If
End Sub
Protected Sub SearchClick(ByVal sender As Object, ByVal e As EventArgs)
Me.BingReport(txtFromD.Text, txtToD.Text, ddlCompanyName.SelectedItem.Text)
End Sub
Private Sub BingReport(ByVal fromDate As String, ByVal toDate As String, ByVal country As String)
Dim crystalReport As ReportDocument = New ReportDocument()
crystalReport.Load(Server.MapPath("~/OrderDetails.rpt"))
Dim dsPersonInformations As Employees = GetEmployees(fromDate, toDate, country)
crystalReport.SetDataSource(dsPersonInformations)
crPersonInformation.ReportSource = crystalReport
End Sub
Private Function GetEmployees(ByVal fromDate As String, ByVal toDate As String, ByVal country As String) As Employees
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand("Orders_GetOrdersBasedOnCountryAndDate")
cmd.CommandType = CommandType.StoredProcedure
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
cmd.Parameters.AddWithValue("@ShipCountry", If(Not String.IsNullOrEmpty(country), country, CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@OrderFromDate", If(Not String.IsNullOrEmpty(fromDate), fromDate, CObj(DBNull.Value)))
cmd.Parameters.AddWithValue("@OrderToDate", If(Not String.IsNullOrEmpty(toDate), toDate, CObj(DBNull.Value)))
sda.SelectCommand = cmd
Using dsEmployees As Employees = New Employees()
dsEmployees.EnforceConstraints = False
sda.Fill(dsEmployees, "Employee")
Return dsEmployees
End Using
End Using
End Using
End Function
Screenshot
