Comparing Dates

[yabba321]

Hello All

I have built an VB.NET application that compares a date selected from a calendar control
With dates from a table in a database using an SQL query . Then creates a datareader containing all the rows that matched that date

It works fine but if the day part of the date is less than 12 e.g. 03/10/2007 the comparison seems to convert the selected date to American format 10/03/2007
And therefore does not match any data and returns an empty datareader

Has anyone else experienced this problem or know how to stop it happening

Many thanks

[wwb_99]

Are you converting the DateTime to a string anywhere?

[yabba321]

Yes when a date in the calendar is clicked I get the date using

Dim datDate as string = eventCalendar.SelectedDate.ToShortDateString()

then use datDate in the SQL query to match the dates

[wwb_99]

Don't do that, keep everything as a date. There is never any reason to call any of the ToString() overloads on a DateTime unless it is going into some sort of output stream.

[yabba321]

Thanks I have tried that but it's still forcing the date to mm/dd/yyyy when the day part is less than 12

Heres the sub causing the problem

Sub EventCalendar_Click(sender as object, e as EventArgs)
lblSelected.text = eventCalendar.SelectedDate.ToLongDateString()
Dim datDate = Cdate(eventCalendar.SelectedDate)
Dim dateArray() = split(datDate,"/")
if dateArray(0) <= 12 then
datDate = Cdate(dateArray(1) & "/" & dateArray(0) & "/" & dateArray(2))
end if
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "SELECT [tblAirLineBudget].[AirLine], [tblAirLineBudget].[Event], " & _
"[tblAirLineBudget].[SalesManager], [tblAirLineBudget].[Description], [tblAirLineBudget].[Event], " & _
"[tblAirLineBudget].[PC], [tblAirLineBudget].[Type], [tblAirLineBudget].[Market], " & _
"[tblAirLineBudget].[Cost] FROM [tblAirLineBudget] " & _
"WHERE [tblAirLineBudget].[EventDate] = #"& datDate &"# "

Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

dgDayEvents.DataSource = dataReader
dgDayEvents.DataBind()
dbConnection.Close
End Sub

[wwb_99]

You are still treating the date like a string. Rather than splitting things and extracting numbers, use the Year, Month and Day properties. Or just skip all that and use a parameter rather than string concatenation to supply the date:

 Dim queryString As String = "SELECT [tblAirLineBudget].[AirLine], [tblAirLineBudget].[Event], " & _
"[tblAirLineBudget].[SalesManager], [tblAirLineBudget].[Description], [tblAirLineBudget].[Event], " & _
"[tblAirLineBudget].[PC], [tblAirLineBudget].[Type], [tblAirLineBudget].[Market], " & _
"[tblAirLineBudget].[Cost] FROM [tblAirLineBudget] " & _
"WHERE [tblAirLineBudget].[EventDate] = @date"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbCommand.Parameters.AddWithValue("@date", Calendar.SelectedDate);

[yabba321]

Thanks for your response i have not used parameters in this way before
I have changed the code as you suggested but got the following error

'AddWithValue' is not a member of 'System.Data.IDataParameterCollection'

But it does look like a much better approach

[wwb_99]

Ok, looks like you are still using 1.1 (hence no AddWithValue method). You can get there easily enough:

Dim Parameter p as dbCommand.Parameters.Add("@name");
p.Type=OleDbType.DateTime
p.Value=Calendar.SelectedDate;

Note my VB is rusty, so the above syntax might not be perfect, but you should be able to get the idea.

[yabba321]

I have just checked my IIS settings and all the apps are using .net version
2.0.50727 so there is something strange going on here.
I shall continue to dig

Thanks for your help anyway

[yabba321]

To finish this post I found the following line

dbCommand.Parameters.Add(New OleDbParameter("@date", eventCalendar.SelectedDate))

Does now pass the date correctly and the whole application runs perfectly