Select query when date range given by TextBoxes...

Question

Hiii There is a Windows Form with a DataGridView, two TextBoxes and a Button. date range is given in the TextBoxes as from and to dates. my problem is when I give date range of the same month, e.g. 05/08/2018 to 20/08/2018, it gives correct records within the date range but, when I give date range within two or more months, e.g. 15/08/2018 to 11/09/2018, it gives all the records regardless the date range given!!! I do not understand what is the problem. Here is my query... Select BillSrNo, BillNumber, BillCustName, BillAmtPaid From Billings Where BillDate Between'" + txtFromDt.Text + "' AND '" + txtToDt.Text + "' Order By BillSrNo Please note that I use MS Access and the DataType is Text. Previously I have tried the same with DateTimePicker and DataType as Date/Time but it gives error. I want to use TextBox, is it possible? Why it is not giving correct data when date range is between two or more months ? Thanks Mayank Jani

Amit Gupta · Answer

Hey I had made some correction in your code. please find the attached code file Do check and let me know, if it working or not update: You have to choose the universal format of date i.e yyyy/MM/dd to fetch the query between dates below is the working code, you can use it freely SELECT Billings.BillSrNo, Billings.BillCustName, Billings.BillDate FROM Billings where billDate >= #2018-08-01# and billDate order by billsrno

Amit Gupta · Answer

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 SELECT [Column1],[Column2],[Column3],[Date] from [Total] Where [Date] between # " + dateTimePicke1.Value.ToString() + " # AND # " + dateTimePicker2.Value.ToString() + " #",

Mayank Jani · Answer

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

Mayank Jani · Answer

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)

Amit Gupta · Answer

Hey When I ran the below query, it shows the perfect result (all 5 rows) SELECT Billings.BillSrNo, Billings.BillCustName, Billings.BillDate FROM Billings where billDate between #01-08-2018# and #31-08-2018# In your code, you had converted from date to ToShortDateString() which might be the reason of issue, just pass the date value FetchBillList.Parameters.AddWithValue("@FDate", dtpFrom.Value); FetchBillList.Parameters.AddWithValue("@TDate", dtpTo.Value); OR Your this code will also work after some modifications like: OleDbDataAdapter FetchBillList = new OleDbDataAdapter( "Select BillSrNo, BillNumber, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate >= #" + dtpFrom.Value.ToString( "dd/MM/yyyy" ) + "# AND BillDate + dtpTo.Value.ToString( "dd/MM/yyyy" ) + "# Order By BillSrNo" , MyConnAcc);

Mayank Jani · Answer

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()

Amit Gupta · Answer

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

Mayank Jani · Answer

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?

Mayank Jani · Answer

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; ...

Amit Gupta · Answer

Your both the question are not identical. Your table might not contains any table or column you are fetching. Moreover, you can change the column names while querying to the database like Select billNo as 'Bill No' from tableName

Mayank Jani · Answer

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.

Amit Gupta · Answer

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.. FetchBillList.Parameters[ "@FDate" ].Value = dtpFrom.Value; FetchBillList.Parameters[ "@TDate" ].Value = dtpTo.Value; Change your snippet to above code and let me know if it works

Mayank Jani · Answer

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.