Using a SELECT statement to return a row where a DateTime column = variable of type Date?

I am using OleDb, trying to do this:

Dim d as Date = DateSerial(Year(rptDate), Month(rptDate), 1 - 1)

Dim conn as OleDbConnection = new OleDbConnection(connStr)
Dim cm as OleDbCommand = new OleDbCommand()

conn.Open()
cm.Connection = conn
cm.CommandText = "SELECT * FROM history WHERE ReportDate = " & d

Okay, so I know it returns 1 row because I'm staring at it in SQL Server. Here is what I've tried so far for the query in the last line, none work:

"SELECT * FROM history WHERE ReportDate = " & d
"SELECT * FROM history WHERE ReportDate = " & d.toString("G")
"SELECT * FROM history WHERE ReportDate = '" & d & "'"
"SELECT * FROM history WHERE ReportDate = '" & d.toString("G") & "'"
"SELECT * FROM history WHERE ReportDate = '#" & d & "#'"
"SELECT * FROM history WHERE ReportDate = #" & d & "#" 'These last 2 with toString

I have also tried:

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.Add(d)

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.Add(d开发者_Python百科.toString("G"))

cm.CommandText = "SELECT * FROM history WHERE ReportDate = '?'"
cm.Parameters.Add(d.toString("G"))

cm.CommandText = "SELECT * FROM history WHERE ReportDate = '?'"
cm.Parameters.Add(d)

Clearly I am missing something here, help? I know that d is the same date as the one in SQL server, specifically 3/31/2011 12:00:00 AM.

Write

cm.CommandText = "SELECT * FROM history WHERE ReportDate = ?"
cm.Parameters.AddWithValue("ParamName", d)

Depending on your provider, you may need to use named parameters:

cm.CommandText = "SELECT * FROM history WHERE ReportDate = @date"
cm.Parameters.AddWithValue("date", d)

Try using another date format in your SQL such as:

Cm.CommandText = "SELECT * FROM history WHERE ReportDate = '" & d.toString("mm/dd/yyyy") & "'"

Did you mean to create your date variable (d) as the day before the rptDate value (ie. with a day of zero (1-1)) ?

This (below) will get you your record (assuming the record has no time part).

SELECT * FROM history WHERE ReportDate = '" & d.toString("dd-MMM-yyyy") & "'"

(The explicit date format is unambiguous, whereas "G" is not.)

...or...

cm.CommandText = "SELECT * FROM history WHERE ReportDate = @date"
cm.Parameters.AddWithValue("@date", d)  

Your query as stated is asking "give me all the entries for exactly midnight on this date".

Something like this should hopefully work:

cm.CommandText = "DECLARE @MyDate DATETIME; SET @MyDate = ?; SELECT * FROM history WHERE ReportDate >= @MyDate AND ReportDate < DateAdd(day,1,@MyDate);"
cm.Parameters.Add(d)

This will return all entries on or after midnight of the date and before midnight of the next day.