Click here to Skip to main content
14,607,231 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a select statement in VB.NET as
Dim SQLString As String = "select JobNo as [Job No],Format(Date,'dd-MM-yyyy') as Date,Aluminium as [Aluminium] from actualcost"

I have a filter to search for a [Job No] between two given dates as follows.Its not working because of the
Format(Date,'dd-MM-yyyy') as Date
in the select statement. Its working fine if I use only Date without any format.

I can not use only Date as I want to see date as 'dd-MM-yyyy' in the gridview.

What I have tried:

dv.RowFilter = String.Format("Convert([Job No],'System.String') Like '%{0}%' AND Date >= '{1:dd-MM-yyyy}' AND Date <= '{2:dd-MM-yyyy}'", TextBox1.Text, DateTimePicker1.Value, DateTimePicker2.Value)
Updated 28-Jun-20 22:39pm
Richard MacCutchan 27-Jun-20 7:22am
Use your debugger to check the generated string.
Sree1981 28-Jun-20 16:15pm
I have solved the issue by formatting date with
DataGridView1.Columns("Date").DefaultCellStyle.Format = "dd-MM-yyyy"
and left the date as it is in the select statement.I hope the issue was "date converted into string".But I am really curious to know what change in dv.filter could have solved the issue with the same select statement?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Date is a date, not its string representation! To get proper data, you need to use proper expression. See: DataColumn.Expression Property (System.Data) | Microsoft Docs[^]

So, use this:
dv.RowFilter = String.Format("Convert([Job No], 'System.String') Like '%{0}%' AND [Date] >= #{1}# AND [Date] <= #{2}#", TextBox1.Text, DateTimePicker1.Value, DateTimePicker2.Value)

In case, when your datasource holds string representation of date (in Date column), use this:
dv.RowFilter = String.Format("Convert([Job No], 'System.String') Like '%{0}%' AND Convert([Date], 'System.DateTime') >= #{1::dd-MM-yyyy}# AND Convert([Date], 'System.DateTime') <= #{2:dd-MM-yyyy}#", TextBox1.Text, DateTimePicker1.Value, DateTimePicker2.Value)
Sree1981 29-Jun-20 12:51pm
Sorry its not working.Error message is "String was not recognized as a valid DateTime" in both cases.
But when I replace the # with ' the first code is working.
Maciej Los 30-Jun-20 2:18am
Can you be so kind and provide sample data?
What's database?
Sree1981 30-Jun-20 12:32pm
Database is SQL Sever Management Studio v18.4 and coding in Visual Studio 2019.
Column names are JobNo(Data Type- int),ALuminium(Data Type- decimal(18, 3)) and Date(Data Type- date)
ID-------------JobNo ---------- Aluminium ------------ Date
6 -------------8325-------------50.000----------------2018-08-10
18 ----------- 8324-------------100.000---------------2015-02-01
19 ----------- 8324--------------75.000---------------2018-12-25
Maciej Los 30-Jun-20 16:43pm
If the format of date in your database is yyyyy-MM-dd, use this format to pass it to RowFilter function.
Sree1981 1-Jul-20 14:18pm
It makes me crazy...Its behaving differently when I change the regional format of PC.No method to keep one filter irrespective of date formats in PC,datetimepicker,dv and SQL?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100