Recently I faced a problem when I was deploying a project on a remote server.

Locally my ASP.Net web application was working like breeze, but when I deployed it on a remote server the problem appeared. When the user try to generate a report selecting a date range and hit go, the user gets an error message “String was not recognized as a valid DateTime”.

The report is consisted of two Calendars with “dd/MM/yyyy” date format, a Grid View and a Text box to filter the grid. I used the Visual Studio to create the GridView which takes the Select parameters from both Calendars when binding. The date column in the MS SQL Server 2005 is of DateTime format and it shows like “31/05/ 2009 11:10:00 AM”.

So obviously there is a misunderstanding of the date entered, which means a difference in the Culture environment. I tried changing the regional settings of the server to match local machines settings, but that didn’t work; still the same error. I also tried to force the Culture info in the web.config, but that also didn’t work for me!

Then I was only left with formatting the date manually to the server’s taste 🙂

To do that, I used the Selecting event of the SQL Data Source to modify the parameter data before going to the Database. Note that if your case is with updating or deleting, .Net also has those events for you to use.

So I used the following function to convert the date format of my Calendar which is “dd/MM/yyyy” to whatever the machine likes 🙂 (Is compatible with).

Public Shared Function ConvertToDate(ByVal dateString As String, ByRef result As DateTime) As Boolean
Try

‘Here is the date format you desire to use

Dim supportedFormats() As String = New String() {“dd/MM/yyyy”}

‘Now it will be converted to what the machine supports

result = DateTime.ParseExact(dateString, supportedFormats,System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None)

Return True
Catch ex As Exception
Return False
End Try
End Function

And from the Selecting event of the SQL Data Source:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting

Dim d1, d2 As Date

If txtFrom.Text <> “” And txtTo.Text <> “” Then

ConvertToDate(txtFrom.Text, d1)
ConvertToDate(txtTo.Text, d2)

e.Command.Parameters.Item(0).Value = d1

e.Command.Parameters.Item(1).Value = d2
End If
End Sub

I just wanted to share this, I hope this would help 😉