Friday, March 27, 2009

Setting the DateTimePicker to a Blank Value

I wanted to use the DateTimePicker control on a data entry form; however, it always has to be set to a value. I did not want to set the date value to Now or some other preset value because it is not clear to the user that the value needs to be set. I found several solutions to this problem, but the one below appears to work and is easy to use.

Initialize the DateTimePicker control to a blank value by setting the Format property to Custom and the CustomFormat property to a space (" "):

DateTimePickerConstDate.CustomFormat = " "
DateTimePickerConstDate.Format = DateTimePickerFormat.Custom

Then, when the value changes, change the Format property back to its original value (or define a usable Custom Format:

Private Sub DateTimePickerConstDate_ValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DateTimePickerConstDate.ValueChanged

DateTimePickerConstDate.Format = DateTimePickerFormat.Short

End Sub

For more information on this solution and other approaches, see the following:

Sunday, March 22, 2009

Sorting a VarChar Attribute as a Number in a SQL Statement

This post is not particularly .NET, but I am posting it anyway.

I was needing to sort the results of a SQL statements by a Priority attribute (which has the values 1 to 10). Unfortunately, the Priority attribute is defined as VarChar, so 10 always follows 1 when you do an ORDER BY Priority.

There are many solutions to this problem, but this simplest (although not the more readily obvious, and maybe not the most efficient) is this:

SELECT * FROM Data_Table ORDER BY Len(Priority), Priority ASC

Another approach is:

SELECT * FROM Data_Table ORDER BY RIGHT('0000' + RTRIM(LTRIM(Priority)), 4)

There are other approaches in the posts where I found this solution: