Select query when date range given by TextBoxes...

Hiiiiii

A big big biiiiggggg THANK YOU, Amit.

 

the changes with the universal format of date has made it.

 

see the screen shots attached. the changes in parameterized query not worked but the universal format did. it's really surprising that the data is entered and saved in the table is in dd/MM/yyyy format but when retrieved, it should be yyyy/MM/dd format !!!!!!!!!

 

Thanks a lot once again...

Mayank Jani

No dear, it's not working for September.

 

please see the screen shots attached herewith. also, you can check the codes as I have removed unnecessary commets. there are two methods I have tried. first is my very much known and second is with parameterized query. 

 

please run the code and see the result. Please note that I want two types of result from the data range...

1. rows from dt for the same month between any dates. (01/08/2018 - 31/08/2018) &

2. rows from dt for different months between any dates. (12/08/2018 - 18/09/2018)

Hey

 

When I ran the below query, it shows the perfect result (all 5 rows)

 

In your code, you had converted from date to ToShortDateString() which might be the reason of issue, just pass the date value

 

 

OR

Your this code will also work after some modifications like:

 

 

Hii Amit,

 

thanks for your help.

everytime when I make change in the code, I get closer to the result but remain one step behind.

Please check the attachment. I have attached two screenshots and the code. the screenshots shows the result I get and also I have described the problem and what I want to do. the commented parts in code are previous codes I used to get results and below code is after changes. previously, I used parameters.add() then use parameters.addwithvalue()

Have a look to the below link (last answer) and determine what mistake you are making.

 

The code you are implementing are not required for the usage and decrease the performance.

 

Do check the link and let me know. If the problem still persist then post your current code

 

 https://stackoverflow.com/questions/35272405/how-to-search-between-two-dates-in-c-sharp-with-ms-access

Hiiii

an update...

I have tried datatable...

if(ReadData.Read())

{

DataTable dt = new DataTable();
dt.Load(ReadData);

dgvBillList.DataSource = dt;

dgvBillList.Columns[0].Width = 32;
dgvBillList.Columns[0].HeaderText = "Sr.";
dgvBillList.Columns[1].Width = 90;
dgvBillList.Columns[1].HeaderText = "Bill No.";
dgvBillList.Columns[2].Width = 90; ...

 

Now it works but my problem remains. if i fetch data for the same month, it shows correctly but mess ups only when the date range goes thru two months.

e.g. 05/08/2018 - 22/08/2018  //works fine.

but  05/08/2018 - 15/09/2018 //not giving correct data.

 

any idea what going on?

Hii

the query fields and table fields are same. it is working while I use select query with the old way, just gives wrong rows.

 

if everything is ok, I am just stucking at...

 

FetchBillList.ExecuteNonQuery();

OleDbDataReader ReadData = FetchBillList.ExecuteReader();

if(ReadData.Read())  //Working good till here.

{
dgvBillList.DataSource = (what should I write here to connect the data with the DGV? or is there anyother way to do so??)

dgvBillList.Columns[0].Width = 32;  //Here it throws error.
dgvBillList.Columns[0].HeaderText = "Sr.";
dgvBillList.Columns[1].Width = 90;
dgvBillList.Columns[1].HeaderText = "Bill No.";
dgvBillList.Columns[2].Width = 90;  ...

Hey, I have made changes as you suggested...

 

but I am used to fetch data by dataset and so, I write e.g. dgvBillList.DataSource = dsFetchBillList.Table[0]; followed by the DGV creation like...

dgvBillList.Columns[0].Width = 32;
dgvBillList.Columns[0].HeaderText = "Sr.";
dgvBillList.Columns[1].Width = 90;
dgvBillList.Columns[1].HeaderText = "Bill No.";  //and so on...

 

Here How to pass the data to the DGV after Read() the data from the dt.

 

presently it is throwing error, 'Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index '

the fields in query and columns in the DGV are equal.

Yes, you are doing good, just be assured that when passing dates, just pass the date picker value, DONOT convert it to string because conversation looses many data..

 

 

 

Change your snippet to above code and let me know if it works 

Hiii Amit,

 

I have searched and tried the parameterized query but still have some confusion/problem that how to show the fetched data into DataGridView as I have no experience with OleDbCommand with to show data into DGV.

Here is my query with parameters...

 

OleDbCommand FetchBillList = new OleDbCommand();

if (MyConnAcc.State == ConnectionState.Closed)
MyConnAcc.Open();

FetchBillList.Connection = MyConnAcc;

FetchBillList.CommandText=("Select BillSrNo, BillNumber, BillCustName, BillAmtPaid From Billings Where BillDate Between @Fdate AND @TDate Order By BillSrNo");

FetchBillList.Parameters.Add("@FDate", OleDbType.Date);
FetchBillList.Parameters.Add("@TDate", OleDbType.Date);

FetchBillList.Parameters["@FDate"].Value = dtpFrom.Value.ToShortDateString();
FetchBillList.Parameters["@TDate"].Value = dtpTo.Value.ToShortDateString();

FetchBillList.ExecuteNonQuery();

OleDbDataReader ReadData = FetchBillList.ExecuteReader();

if(ReadData.Read())

{
//Here I need to show the data into a DGV

}

 

Is this correct way I'm working on??

Please check the above code and suggest me solution where I am mistaking.

Hey

 

First of all always use the datetime datatype in the database if you are working with datetime.

 

Secondly, use Parameterized query to avoid injection (you can get more drtails over the web).

 

Third, use datetimepicker instead of textbox (as user can make mistake while typing dates)

 

You can put # between your query like below