Display records between two Dates in ASP.Net Crystal Report using C# and VB.Net

jmprateek
 
on Aug 22, 2022 10:43 PM
Sample_108746.zip
640 Views

Hi,

I want to fetch all bills between two dates but my query doesn't seem to work. It is working fine in SQL but it is showing me all the records in crystal reports which are not supposed to happen. Please help me.

My SQL query:

SELECT DISTINCT(bno), name,
    CONVERT(varchar(50), cdate, 105) as 'Date',
    CAST(IsNULL(disc, '0') AS DECIMAL(18,2)) as 'Discount', totalgst,
    case when pymtmode = 'Cash'
    then CAST(paidamt AS DECIMAL(18,2)) end as 'Cash',
    case when pymtmode = 'Credit'
    then CAST(paidamt AS DECIMAL(18,2)) end as 'Credit',
    CAST(IsNULL(paidamt, '0') AS DECIMAL(18,2))  as 'GT'
from Sales
where CONVERT(varchar(50), cdate, 105) >= CONVERT(varchar(50),'03-10-2021',103)
    AND CONVERT(varchar(50), cdate, 105) < CONVERT(varchar(50),'07-01-2022',103) and bno!='0'
group by bno, name, cdate, totalgst, disc, paidamt, pymtmode;

The query is working fine in SQL server but not while generating crystal reports.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Aug 23, 2022 06:34 AM

Hi jmprateek

Note:I have created sample by refering the below Article. For more details pleaser refer below Article.

Crystal Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

HTML

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports CrystalDecisions.CrystalReports.Engine

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(Server.MapPath("~/Sales.rpt"));
    DataSet1 dsCustomers = GetData("SELECT bno,name,cdate,totalgst,disc,paidamt,pymtmode from Sales where convert(varchar(50),cdate,105) >= convert(varchar(50),'03-10-2021',103) AND convert(varchar(50),cdate,105) < convert(varchar(50),'07-01-2022',103) and bno!='0' group by bno,name,cdate,totalgst,disc,paidamt,pymtmode");
    crystalReport.SetDataSource(dsCustomers);
    CrystalReportViewer1.ReportSource = crystalReport;
}

public DataSet1 GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            cmd.Connection = con;
            using (DataSet1 dsCustomers = new DataSet1())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim crystalReport As ReportDocument = New ReportDocument()
    crystalReport.Load(Server.MapPath("~/Sales.rpt"))
    Dim dsCustomers As DataSet1 = GetData("SELECT bno,name,cdate,totalgst,disc,paidamt,pymtmode from Sales where convert(varchar(50),cdate,105) >= convert(varchar(50),'03-10-2021',103) AND convert(varchar(50),cdate,105) < convert(varchar(50),'07-01-2022',103) and bno!='0' group by bno,name,cdate,totalgst,disc,paidamt,pymtmode")
    crystalReport.SetDataSource(dsCustomers)
    CrystalReportViewer1.ReportSource = crystalReport
End Sub

Public Function GetData(ByVal query As String) As DataSet1
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
            cmd.Connection = con
            Using dsCustomers As DataSet1 = New DataSet1()
                sda.Fill(dsCustomers, "DataTable1")
                Return dsCustomers
            End Using
        End Using
    End Using
End Function

Screenshot