Question Filter DataGridView by string in format of MMddyyyy

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
ok, guys, I have a SQL database with a column called "Date" but with a format of "nchar(8)" so examples of field data are...

12112012
01012013
02022013
etc...

In VB i have a DGV that is databound to this sql table. So, my second column in the DGV is the same as what i have explained above...

With these being Date values, but not of the DateTime data type, i am finding difficulty in Filtering this DGV based on date ranges, as the SQL data coming in is a String Data type...

My question is how can i Filter this DGV for my desired date ranges... An example of what I am needing is...

BUTTON:: to show Last 30 Days of Records
VB.NET:
Dim last30 as Date
Dim current as Date

last30 = today.date.adddays(-30)
current = today.date

Dim dv as DataView
dv = New DataView(DataSet.Tables("tblName"))
dv.RowFilter = "Date > #" & last30 & "# and Date <= #" & current & "#"
DataGridView1.DataSource = dv
Any assistance is much appreciated!!!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
This is exactly why you should NOT EVER store dates as text in a database. Only text should be stored as text. Numbers should be stored as numbers, dates as dates, etc. If this is your database then change your schema immediately and your problem is solved. You'll never encounter the same problem again by never storing anything other than text as text.

If this is not your database then the answer is still to store dates as dates, but you'll have to do it at the application level. After populating your DataTable from the database, add an extra column and populate it with Date values equivalent to the text values that came from the database. You can then filter on that column as dates because it does contain dates.

Finally, don't bind your grid to a DataView. Simply bind your grid to the DataTable. It already has a DataView associated with it in its DefaultView property. When the DataTable is bound, that's where the data comes from anyway. Set the RowFilter of that DefaultView and you're good. Better still, bind the DataTable to a BindingSource and bind that to the grid. You can then set the Filter of the BindingSource.
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
I learned your point the hard way, worst part is I knew better. The reason I did this to begin with is because the datetime SQL data type stores my dates like 01/01/2012 as 1/1/2012 and when i populate masked Text Boxes they are all flubbed up. I still havn't found a good way to resolve that. Do you have any suggestions?

Also, I changed my data type back to datetime, earlier today you'll be happy to hear. Thanks for your reply!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
SQL Server does NOT store dates like that. The whole point of storing dates as dates and not text is that binary dates have no format at all. They are just numbers. What you see displayed is NOT what is stored in the database. It's just an appropriate text representation of what's stored in the database. When you retrieve the binary date values from the database into your app it is completely up to you what format to use when displaying them to the user. In order to display the dates to the user they must be converted to text but that is only done at the presentation level, not the data storage level or even data manipulation level. If the data is displayed in a DataGridView then you can set the Format of the DefaultCellStyle for that column. If it's in a MaskedTextBox then set the FormatString of the Binding if it's bound. You really should be using a DateTimePicker rather than a MaskedTextBox though.
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
Its quite obvious that the data in sql server is binary. What it returns to VB is what I have to work with unfortunately. I do not want to use a DTP as it is more time consuming and is frowned upon by the customer.

The masked textbox with mask of __/__/____ ends up looking like 11/20/12__ which is also terrible. I have been unsuccessful using code...

Textbox.Text = Format(Textbox.Text, "MMddyyyy")

It ends up being blank? Literals not included as they are part of the mask...
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
Its quite obvious that the data in sql server is binary. What it returns to VB is what I have to work with unfortunately.
And that is also binary. If you have data in SQL Server in a date, datetime or datetime2 column then you're going to get that data as DateTime values in your .NET code. Again, that's just numbers and has no format. Format is only an issue when converting to or from a String, which you only do at the very last moment.
VB.NET:
Textbox.Text = Format(Textbox.Text, "MMddyyyy")
That line of code is never going to be useful. You can't format a String to a String. For Format to be useful you have to start with something that can be formatted, e.g. an Integer, Double or (wait for it...) a DateTime. The proper way to get what you want is to take the DateTime you get from the database and call its ToString method, specifying the desired format. Without having tested it, I believe that that should be:
VB.NET:
myMaskedTextBox.Text = myDateTime.ToString("MM/dd/yyyy")
To convert the text in the MaskedTextBox back to a DateTime to save the data back to the database:
VB.NET:
Dim myDateTime As DateTime

If DateTime.TryParse(myMaskedTextBox.Text, myDateTime) Then
    'The data is valid so save myDateTime.
Else
    'The data is not valid so don't save it.
End If
It's the fact a MaskedTextBox will permit invalid data while the a DateTimePicker will not is why it should be used in such cases. I guess the customer is always right though.
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
VB.NET:
myMaskedTextBox.Text = myDateTime.ToString("MM/dd/yyyy")
Thanks, the code above worked great for what i was attempting. And within using that, I discovered something you had attempted to point out to me. A String cant be reformatted as a String. So first I had to declare the table item as a DateTime, then use the .ToString formatter to get my desired result. Thanks again.

The suggestion you provided for the date column in DataGridView however is not working. I selected the Format for DefaultCellView but the dates still appear as they do in SQL (1/1/2012 instead of 01/01/2012). The DataGridView is bound.

Any suggestions?
 

cwnonthehill

Member
Joined
Nov 5, 2012
Messages
20
Programming Experience
1-3
Sorry, I figured it out. I had changed the SQL table back to datetime, but forgot to change my DataSet type to System.DateTime. Thanks again for your help, brilliant as always!
 
Top Bottom