Very Computer

I am querying an MS Access data/time field from VB.NET, using
Microsoft.Jet.OLEDB.4.0, and get the following error:

An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Here is a snippet from my code:

__________________________________
Dim strStartDate, strEndDate As String
strStartDate = Me.StartDateTimePicker.Value.ToString("M/d/yyyy h:mm:ss")
strEndDate = Me.EndDateTimePicker.Value.ToString("M/d/yyyy h:mm:ss")

ChangeDataAdapter.SelectCommand.CommandText = _
"SELECT ch_ticket_id, ch_made_by, ch_made_on, ch_cost,
ch_description " & _
"FROM pt6bt_ti_change " & _
"WHERE ch_cost > 0" & _
" AND ch_made_on >= " & "'" & strStartDate & "'" & _
" AND ch_made_on <= " & "'" & strEndDate & "'"
ChangeDataSet.Clear()
ChangeDataAdapter.Fill(ChangeDataSet, "pt6bt_ti_change")
ChangeDataGrid.SetDataBinding(ChangeDataSet, "pt6bt_ti_change")
_________________________________________

This code works IF I change the date field in the database to a text
field, but does NOT work if the date field in the database is a
date/time field. And I have tried using a Date variable in VB instead of
a String variable; same result.

Thanks in advance

--
Posted via http://dbforums.com

Have you tried enclosing the data values in # signs
" AND ch_made_on >= #" & strStartDate & "# .........

--
Regards

Sukesh Hoogan

http://personal.vsnl.com/sukesh_hoogan

Quote:

> I am querying an MS Access data/time field from VB.NET, using
> Microsoft.Jet.OLEDB.4.0, and get the following error:

> An unhandled exception of type 'System.Data.OleDb.OleDbException'
> occurred in system.data.dll

> Here is a snippet from my code:

> __________________________________
> Dim strStartDate, strEndDate As String
> strStartDate = Me.StartDateTimePicker.Value.ToString("M/d/yyyy h:mm:ss")
> strEndDate = Me.EndDateTimePicker.Value.ToString("M/d/yyyy h:mm:ss")

> ChangeDataAdapter.SelectCommand.CommandText = _
> "SELECT ch_ticket_id, ch_made_by, ch_made_on, ch_cost,
> ch_description " & _
> "FROM pt6bt_ti_change " & _
> "WHERE ch_cost > 0" & _
> " AND ch_made_on >= " & "'" & strStartDate & "'" & _
> " AND ch_made_on <= " & "'" & strEndDate & "'"
> ChangeDataSet.Clear()
> ChangeDataAdapter.Fill(ChangeDataSet, "pt6bt_ti_change")
> ChangeDataGrid.SetDataBinding(ChangeDataSet, "pt6bt_ti_change")
> _________________________________________

> This code works IF I change the date field in the database to a text
> field, but does NOT work if the date field in the database is a
> date/time field. And I have tried using a Date variable in VB instead of
> a String variable; same result.

> Thanks in advance

> --
> Posted via http://dbforums.com

Sukesh,

That did the trick! I've never used a '#' in a SQL statement, and don't
really understand what it does, but I'll read up on it. And I thank you
for your help.

frank

--
Posted via http://dbforums.com