Problem with simple query

[OMRebel]

For some reason, this query isn't returning any records:
sql = "Select * from tblTrans where tblTrans.DOS BETWEEN " & dtpStart.Value.ToShortDateString & " AND " & dtpEnd.Value.ToShortDateString

The tblTrans has a field called DOS. That field is updated in another part of the application using the ToShortDateString. So, on one form I created two DateTimePicker components, and set the dates to where I know there is a valid record for that time. However, it won't return a record. I even tried hardcoding it to read:

sql = "Select * from tblTrans where tblTrans.DOS = 10/18/2006"

I know there is a record for that date, yet, it doesn't return anything. The DOS field is a date field. The data is an Access database. Why am I having such a hard time with this? lol

 

[jmcilhinney]

Don't ever use string concatenation to build SQL code if you can possible avoid it:

Dim myCommand As New OleDbCommand("SELECT * FROM tblTrans WHERE DOS BETWEEN @StartDate AND @EndDate", myConnection)

myCommand.Parameters.AddWithValue("@StartDate", dtpStart.Value.Date)
myCommand.Parameters.AddWithValue("@EndDate", dtpEnd.Value.Date)

Then execute the command.

If you were going to use string concatenation for a start you'd have to enclose the value in '#' symbols to indicate that it's a date literal. Also, you shouldn't use ToShortDateString because in many cultures that will put the day before the month. Date literals MUST have the month before the day. If you were to use string concatenation, which you absolutely should not, it would look like this:

sql = "Select * from tblTrans where tblTrans.DOS BETWEEN #" & dtpStart.Value.ToString("M/dd/yyyy") & "# AND #" & dtpEnd.Value.ToString("M/dd/yyyy") & "#"

 

[OMRebel]

@jmcilhinney - Thanks for the help, and for the advice! I'll certainly take your advice on this and give it a shot. Once again, thanks!