Rowfilters not working with custom date format in VB.NET?

Question

0.00/5 (No votes)

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)

Posted 26-Jun-20 13:41pm

Sree1981

Updated 28-Jun-20 22:39pm

v2

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2020-06-28T22:39:00

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:

VB

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:

VB

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)

Posted 28-Jun-20 22:39pm

Maciej Los

Comments

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?

Maciej Los 2-Jul-20 7:21am

Take a look here: DateTimeFormatInfo Class (System.Globalization) | Microsoft Docs[^]