getting data between two Longdates c# MS access database

Question

Hi guys when I insert dates by longdate using Datetimepicker ex: wednesday, April 26,2017 then I want to search between two dates but it can not give me all the dates between ?

Insert code

cmd.CommandText = "insert into [Table1] ( [Employee Name],[Date],[Hours] ) values ('" + textBox1.Text + "','" + dateTimePicker1.Text + "','" + textBox2.Text + "')";

search between Codes :

cmd.CommandText = "select * from Table1 where [Date] between '" + dateTimePicker1.Text + "' AND '" + dateTimePicker2.Text + "' ";

Creating commands this way is not very secure. "....Text" could be anything. — Jeroen Heier, Apr 27 '17 at 08:14
use parameters , do not rush the query directly with value in it — Saurabh, Apr 27 '17 at 08:59

score 0 · Answer 1 · answered Apr 27 '17 at 08:16

First of all use parameters because right now your code is vulnerable to sql injection. So do this this way:

cmd.CommandText = ("insert_questions @store_result, @store_title, 
                    @store_des");
    cmd.Parameters.AddWithValue("@store_result", store_result);

More reading : Sql command parameters

And use dateTimePicker1.Value instead of text that should do it.

score 0 · Answer 2 · answered Apr 27 '17 at 08:16

0

Use parameters to avoid date values being converted from strings:

cmd.CommandText = "insert into [Table1] ( [Employee Name],[Date],[Hours] ) values (@empName, @date, @hours)";
cmd.Parameters.AddWithValue("@empName", textBox1.Text);
cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@hours", textBox2.Text);

// ...................

cmd.CommandText = "select * from Table1 where [Date] between @startDate AND @endDate ";
cmd.Parameters.AddWithValue("@startDate", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@endDate", dateTimePicker2.Value);

answered Apr 27 '17 at 08:16

Dmitry Egorov

9,542
3
22
40

I did it but I get this warning – hogr rzgar Apr 27 '17 at 08:25
Warning 2 'System.Data.OleDb.OleDbParameterCollection.Add(string, object)' is obsolete: 'Add(String parameterName, Object value) has been deprecated. Use AddWithValue(String parameterName, Object value). http://go.microsoft.com/fwlink/?linkid=14202' c:\users\dell\documents\visual studio 2012\Projects\AbsentSchedule\AbsentSchedule\Form2.cs 31 17 AbsentSchedule and inserted date like this 2017-04-27 00:00:00 – hogr rzgar Apr 27 '17 at 08:25
I changed date in my access table now data is inserted as longdate but in showing it in my datagridview show dates like this 18/5/2017 – hogr rzgar Apr 27 '17 at 08:47
[Set up `DefaultCellStyle.Format` property](http://stackoverflow.com/a/38726576/4295017) of the date column to the desired format – Dmitry Egorov Apr 27 '17 at 08:50
thanks a lot Mr.Dmitry Egorov it works very good it show me like that Thursday, April 27, 2017 Saturday, April 01, 2017 Saturday, April 29, 2017 now I have to sort it ascending right ? to sort it from beginning of the month to end – hogr rzgar Apr 27 '17 at 09:59
Add `order by` clause to your query: `cmd.CommandText = "select * from Table1 where [Date] between @startDate AND @endDate order by [Date]";` – Dmitry Egorov Apr 27 '17 at 10:10
thanks its done by cmd.CommandText = "select * from [Table1] ORDER BY Date ASC"; – hogr rzgar Apr 27 '17 at 10:12

score 0 · Accepted Answer · answered Apr 27 '17 at 08:20

0

You're inserting DateTime as string and aslo trying fetch data using strings as filter for between.

So, few things:

make sure your column Date is of type Date and not string.
use dateTimePicker1.Value instead of Text because Value returns DateTime object.
use parameters in your SQL commands

To sum things:

cmd.CommandText = "insert into [Table1] ( [Employee Name],[Date],[Hours] ) values (?, ? , ?)";
cmd.Parameters.AddWithValue("EmployeeName", textBox1.Text);
cmd.Parameters.AddWithValue("Date", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("Hours", textBox2.Text);



cmd.CommandText = "select * from Table1 where [Date] between ? AND ?";
cmd.Parameters.AddWithValue("DateFrom", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("DateTo", dateTimePicker2.Value);

answered Apr 27 '17 at 08:20

Nino

6,931
2
27
42

thanks Bro search is good now but when I insert my long date to my table it now show the days name sunday and monday did I have to do some thing in my access table but actually in my C# form I can see days name – hogr rzgar Apr 27 '17 at 08:35
format your code for display the way you want. For example, like this: i `label1.Text = DateTime.Now.ToShortDateString();` or `label1.Text = DateTime.Now.ToString("MM/dd/yyyy HH:mm;` – Nino Apr 27 '17 at 08:43
but I want days name also like Sunday monday – hogr rzgar Apr 27 '17 at 08:53
It is important to know that date is completely (with all elements, includin milliseconds) written in database. To Show it, you have to format it the way you want. Take a look at [this MSDN article](https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.110).aspx) and [this one](https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx). You need something like `label1.Text = DateTime.Now.ToString("D");` or `label1.Text = DateTime.Now.ToLongDateString()` – Nino Apr 27 '17 at 08:55
thanks Bro I inserted the date longdate and I can see it in my table as Saturday, April 01, 2017 and I canged my date table to long date now the problem is that when I call it to my data gridview not show the date like this : Saturday, April 01, 2017 it show 18/5/2017 in datagridview – hogr rzgar Apr 27 '17 at 09:01
show your DataGridView code. It's probably, as @Dmitry Egorov said, format. You should set `DefaultCellStyle.Format = "D" to your `DateTime` columns. – Nino Apr 27 '17 at 09:10

getting data between two Longdates c# MS access database

3 Answers3