RDLC report filter by date to date

Siddharth Gajbhiye6y ago

Hello Suhel,

In the below link you will find it out how to filter RDLC reports with demo

https://www.aspsnippets.com/Articles/Filter-RDLC-Report-in-ASPNet-using-C-and-VBNet.aspx

For demo click here

https://www.aspsnippets.com/demos/2571/

Thanking you

Matrick Maths6y ago

Add additional 1 day to the toDateValue and remove the equal sign in the todatevalue

and run the query

suhel jamadar6y ago

Any Solution???

suhel jamadar6y ago

@ Amit Sir

I put time also, how to share data type of sale?

Amit Gupta6y ago

Have you tried putting time in your query as I had posted earlier? If yes then can you share the datatype of the sal column

suhel jamadar6y ago

@ Amit Sir

I Removed combobox code still not filter data,

I do not understand what to do

Thank you for support

Amit Gupta6y ago

Apart from query, your code is not optimised and has much repetitive code as there is only a change in query but wrotten 3 times the whole code, you can change the query based on combobox selection and execute the query and so on...

Amit Gupta6y ago

Your datatype might have time as well, so try including time as well SELECT * FROM Sale WHERE Date >= '{0} 00:00:00.000 ' AND Date<= '{1} 23:59:59.999' ", dateTimePicker1.Value.ToString("yyyy-MM-dd"), dateTimePicker2.Value.ToString("yyyy-MM-dd"));

suhel jamadar6y ago

Jignesh Sir How to check that???

Jignesh Kumar6y ago

Hi, Check your date format in where clause. It should be match with sql date fromat.

suhel jamadar6y ago

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

using Microsoft.Reporting.WinForms;

using System.Web.UI.WebControls;

namespace WindowsFormsApp2

{

public partial class SalesReport : Form

{

public SalesReport()

{

InitializeComponent();

}

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

SqlCommand cmd;

SqlDataAdapter sda;

private void SalesReport_Load(object sender, EventArgs e)

{

con.Open();

string sql = "select * from CustIn ";

cmd = new SqlCommand(sql, con);

sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

comboBox1.DisplayMember = "CustName";

comboBox1.DataSource = dt;

comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;

comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;

con.Close();

this.reportViewer1.RefreshReport();

}

DataTable dt = new DataTable();

private void button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

con.Open();

string query = string.Format("SELECT * FROM Sale WHERE Date >= '{0}' AND Date<= '{1}'", dateTimePicker1.Value.ToString("yyyy-MM-dd"), dateTimePicker2.Value.ToString("yyyy-MM-dd"));

cmd = new SqlCommand(query,con);

sda = new SqlDataAdapter(cmd);

sda.Fill(dt);

ReportDataSource rdsd = new ReportDataSource("DataSet1", dt);

reportViewer1.LocalReport.ReportPath = @"E:\poulatry\WindowsFormsApp2\SalesR.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(rdsd);

reportViewer1.RefreshReport();

con.Close();

if (comboBox1.SelectedIndex == -1)

{

con.Open();

string sql = "select * from Sale ";

cmd = new SqlCommand(sql, con);

sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

ReportDataSource rds = new ReportDataSource("DataSet1", dt);

reportViewer1.LocalReport.ReportPath = @"E:\poulatry\WindowsFormsApp2\SalesR.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(rds);

reportViewer1.RefreshReport();

con.Close();

}

else

{

con.Open();

string sql = "select * from Sale where CustName = '"+comboBox1.Text+"' ";

cmd = new SqlCommand(sql, con);

sda = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

sda.Fill(dt);

ReportDataSource rds = new ReportDataSource("DataSet1", dt);

reportViewer1.LocalReport.ReportPath = @"E:\poulatry\WindowsFormsApp2\SalesR.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(rds);

reportViewer1.RefreshReport();

con.Close();

}

void fillcombobox()

{

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

string sql = "select * from CustIn where CustName ='" + comboBox1.Text + "' ";

SqlCommand cmd = new SqlCommand(sql, con);

SqlDataReader myreader;

try

{

con.Open();

myreader = cmd.ExecuteReader();

while (myreader.Read())

{

string cname = myreader.GetString(1);

comboBox1.Items.Add(cname);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

@Amit Sir This is code & Otput. even I Select Date 19 its show report of 20

Amit Gupta6y ago

Can you share what's not working and whats happened instead?

suhel jamadar6y ago

@Ammit Sir Changes Done but Still Same

Amit Sir Changed but still same

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Del\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

con.Open();

string query = string.Format("SELECT * FROM Sale WHERE Date >= '{0}' AND Date<= '{1}'", dateTimePicker1.Value.ToString("yyyy-MM-dd"), dateTimePicker2.Value.ToString("yyyy-MM-dd"));

cmd = new SqlCommand(query,con);

sda = new SqlDataAdapter(cmd);

sda.Fill(dt);

ReportDataSource rdsd = new ReportDataSource("DataSet1", dt);

reportViewer1.LocalReport.ReportPath = @"E:\poulatry\WindowsFormsApp2\SalesR.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(rdsd);

reportViewer1.RefreshReport();

con.Close();

suhel jamadar6y ago

Amit Sir Changed but still same

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Del\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30");

con.Open();

string query = string.Format("SELECT * FROM Sale WHERE Date >= '{0}' AND Date<= '{1}'", dateTimePicker1.Value.ToString("yyyy-MM-dd"), dateTimePicker2.Value.ToString("yyyy-MM-dd"));

cmd = new SqlCommand(query,con);

sda = new SqlDataAdapter(cmd);

sda.Fill(dt);

ReportDataSource rdsd = new ReportDataSource("DataSet1", dt);

reportViewer1.LocalReport.ReportPath = @"E:\poulatry\WindowsFormsApp2\SalesR.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(rdsd);

reportViewer1.RefreshReport();

con.Close();

Amit Gupta6y ago

As already stated, you have to replace tablename with your actual database table and date with your date column plus datepicker name respectively

Amit Gupta6y ago

string query = string.Format("SELECT * FROM Tablename WHERE date >= '{0}' AND date<= '{1}'", fromDatePicker.Value.ToString("yyyy-MM-dd"), toDatePicker.Value.ToString("yyyy-MM-dd"));

suhel jamadar6y ago

@Amit Sir,

I m beginner if possible send me source code

Jignesh Kumar6y ago

Hi,

Plese fing this link which will help to resolve your issue,

https://www.aspforums.net/Threads/151022/Filter-RDLC-report-with-multiple-parameters-in-ASPNet/

Amit Gupta6y ago

@suhel jamadar

I think you had not understood the scenario of the code (what does it do). I had given a sample date, you have to replace it with your datepicker selected date but the format should be in yyyy/MM/dd.

Hope you got the point.

suhel jamadar6y ago

Amit i want code between selected date to date to from date, not particular date

Amit Gupta6y ago

Apply a where clause to filter your query resuts like