SQL Select Date Format

  • Thread starter Thread starter Justin Emlay
  • Start date Start date
J

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
 
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
 
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
 
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!
 
Back
Top