SQL Select Date Format

[Justin Emlay]

I'm trying to select from an Access DB based on a date field. The date
field in Access is specified as Date/Time and the following code is grabbing
the date from a DatePicker in short format:


Error - Date type mismatch in criteria expression

Dim PeriodStart As Date = dpTSPeriodStart.Text
DsTimeSheets1.Clear()
DaTimeSheets.SelectCommand.CommandText = "SELECT [Job Date], Employee, [Job
Number], [Reg Hours], [Over Hours], [Vac Hours], [Hol Hours], [Period
Start], [Date Entered], ID FROM TimeSheets WHERE [Period Start] = '" &
PeriodStart & "'"


The date in the DB is "6/27/2004" and not "06/27/2004" and the following
doesn't work either:

Format$(PeriodStart, "MM/dd/yy")


I'd appreciate any insight,
Thanks,
Justin

 

[Cor Ligthert]

Hi Justin,

I have a sample someone made in one of this dotNet newsgroups.
\\\By domenique
Dim cmd As New OleDbCommand("SELECT User, ID, Date FROM Work WHERE Date
BETWEEN @begindate And @enddate", objConn)
cmd.Parameters.Add("@begindate", cdate(txtBeginDate.text))
cmd.Parameters.Add("@enddate", cdate(txtEndDate.text))
dim adapter as new OleDbDataAdapter(cmd)

(It is VBNet however the only difference with C# as far as I see is the cast
of the dates and the Dim, so that must be easy to use)

I hope this helps?

Cor

 

[Justin Emlay]

My connection is built at runtime so my best implimentation of this code is:

Private Sub btnTSStart_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnTSStart.Click
DsTimeSheets1.Clear()
DaTimeSheets.SelectCommand.Parameters.Add("@PeriodStart",
CDate(dpTSPeriodStart.Text))
DaTimeSheets.SelectCommand.CommandText = "SELECT [Date Entered], ID FROM
TimeSheets WHERE [Period Start] = @PeriodStart"
DaTimeSheets.Fill(DsTimeSheets1)
End Sub


Now when I run my program and click the button I get exactly what I'm after
however after that I get nothing. That is when I select another date from
the datepicker and click the button again my dataset doesn't change even
though "dpTSPeriodStart.Text" does change.

Thanks for the code Cor!

Still stuck,
Justin

 

[Justin Emlay]

Ah, got it!

DaTimeSheets.SelectCommand.Parameters.Clear()


So:
DsTimeSheets1.Clear()
DaTimeSheets.SelectCommand.Parameters.Clear()
DaTimeSheets.SelectCommand.Parameters.Add("@PeriodStart",
CDate(dpTSPeriodStart.Text))
DaTimeSheets.SelectCommand.CommandText = "SELECT [Date Entered], ID FROM
TimeSheets WHERE [Period Start] = @PeriodStart"
DaTimeSheets.Fill(DsTimeSheets1)

Thanks again!