Working with SQL Dates in .NET

[joopdog]

I getting better with this ASP.NET thing. It's great.

Now I'm having troubles with SQL string commands. I'm retrieving data no problem. Except for pulling dates.

Here my code:

Sub Daily_Hist_Sales(sender as Object, e as EventArgs)
      'http://www.w3schools.com/aspnet/aspnet_dbconnection.asp
      'Database Name = great_bay_db4.mdb
      'Table Name = customer
      Dim dbconn
      Dim sql,dbcomm,dbread
      Dim dteDate As Date
      'Create a Database Connection
      dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("great_bay_db4.mdb"))
      dbconn.Open()
      'Create a Database Command
      dteDate = "08/13/2004"
      sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte='#8/13/2004#'" 'DOESN'T WORK
      'sql="SELECT * FROM DAILY_SALES_HIST_X WHERE CustomerId='010110'"   'IT WORKS!
      'sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte='dteDate'"    'DOESN'T WORK
      dbcomm=New OleDbCommand(sql,dbconn)
      'Create a DataReader
      dbread=dbcomm.ExecuteReader()
      'Bind to a Repeater Control
      Daily_Hist_Sales_Info.DataSource=dbread
      Daily_Hist_Sales_Info.DataBind()
      'Close the Database Connection
      dbread.Close()
      dbconn.Close()
End Sub

Here my error message:

Data type mismatch in criteria expression. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error: 


Line 68:       dbcomm=New OleDbCommand(sql,dbconn)
Line 69:       'Create a DataReader
Line 70:       dbread=dbcomm.ExecuteReader()
Line 71:       'Bind to a Repeater Control
Line 72:       Daily_Hist_Sales_Info.DataSource=dbread

What Am I doing wrong? I tried everything. With the '#' sign, then without. I even put "8/13/2004 12:00:00 AM" what's the field.
I just want to pull any 8/13/2004 records.

Help please

Thanks

[szlamany]

The ACCESS-SQL Delimiter for a date is the #-sign - not the single-quote sign - you have both...

sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte='#8/13/2004#'"

should be:

sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte=#8/13/2004#"

[joopdog]

Oh my goodness, it worked!!


Thanks!

[szlamany]

No problem - ciao

[joopdog]

How do I handle it the SQL date with a variable.

I'm still getting type mismatch.

Dim dteDate As Date
      dteDate = "#08/13/2004#"
      'sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte=#8/13/2004#" 'IT WORKS!
      'sql="SELECT * FROM DAILY_SALES_HIST_X WHERE CustomerId='010110'"   'IT WORKS!
      sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte='dteDate'"

Help Please

[szlamany]

Maybe:

sql="SELECT * FROM DAILY_SALES_HIST_X WHERE DlvryDte=#" & Format(dteDate,"SHORT DATE") & "#"

Check the FORMAT function - that's a VB6 function that should not be used in VB.Net - but you should get the idea that you need to FORMAT the dteDate variable into a STRING...