where clause with date (asp.net - vb)

[Yannick]

Hi,

I try to execute request on a ms-access database but I have a problem
with date.
the "myDate" field's format is "date/time"
my request is:

SELECT myCode, myStuff, myDATE FROM myTable WHERE myDate = #05/21/2004#

in ms-access interface, it works fine !
I get real good lines but when I try to execute in my asp.net vb page,
I get "internal automation error" without any other information.

Requests without date stuff inside works, in both asp and access even
with a lot of lines in result, so it's not a timeout problem.

executing request with
"where myDate = #05/21/2004# "
"where myDate LIKE #05/21/2004# "
"where myDate = #05/21/2004 00:00:00# "
"where myDate LIKE #05/21/2004 00:00:00# "
with " # " or " ' " gives me an error

If I change date delimiters to " ' ", I also get an error.

I don't know how to get out of that !

Thanks in advance for any tips !

--yannick--
no direct email reply : fake address

[Herfried K. Wagner [MVP]]

* Yannick <popeye@nowhere.com> scripsit:

> I try to execute request on a ms-access database but I have a problem
> with date.
> the "myDate" field's format is "date/time"
> my request is:
>
> SELECT myCode, myStuff, myDATE FROM myTable WHERE myDate = #05/21/2004#
>
> in ms-access interface, it works fine !
> I get real good lines but when I try to execute in my asp.net vb page,
> I get "internal automation error" without any other information.
>
> Requests without date stuff inside works, in both asp and access even
> with a lot of lines in result, so it's not a timeout problem.
>
> executing request with
> "where myDate = #05/21/2004# "
> "where myDate LIKE #05/21/2004# "
> "where myDate = #05/21/2004 00:00:00# "
> "where myDate LIKE #05/21/2004 00:00:00# "
> with " # " or " ' " gives me an error
>
> If I change date delimiters to " ' ", I also get an error.

Post the code you use tho send the command.

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

[Ken Tucker [MVP]]

Hi,

I can't seam to reproduce your error. I tried all your sql statements
with the northwind database and got no errors.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Me.IsPostBack Then
BindDataToGrid()
End If
End Sub

Private Sub BindDataToGrid()
Dim strConn As String
Dim conn As OleDb.OleDbConnection
Dim daOrders As OleDb.OleDbDataAdapter
Dim ds As DataSet

ds = New DataSet
strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
strConn &= "Data Source = C:\Northwind.mdb;"

conn = New OleDb.OleDbConnection(strConn)

daOrders = New OleDb.OleDbDataAdapter("Select OrderID,
OrderDate, ShipVia from Orders where OrderDate LIKE #01/01/1998
00:00:00#", conn)

ds = New DataSet

daOrders.Fill(ds, "Orders")

DataGrid1.DataSource = ds.Tables("Orders")
DataGrid1.DataBind()
End Sub

Ken
-------------------

"Yannick" <popeye@nowhere.com> wrote in message
news:MPG.1b43c4a7e1a49540989682@news.online.net:

> Hi,
>
> I try to execute request on a ms-access database but I have a problem
> with date.
> the "myDate" field's format is "date/time"
> my request is:
>
> SELECT myCode, myStuff, myDATE FROM myTable WHERE myDate = #05/21/2004#
>
> in ms-access interface, it works fine !
> I get real good lines but when I try to execute in my asp.net vb page,
> I get "internal automation error" without any other information.
>
> Requests without date stuff inside works, in both asp and access even
> with a lot of lines in result, so it's not a timeout problem.
>
> executing request with
> "where myDate = #05/21/2004# "
> "where myDate LIKE #05/21/2004# "
> "where myDate = #05/21/2004 00:00:00# "
> "where myDate LIKE #05/21/2004 00:00:00# "
> with " # " or " ' " gives me an error
>
> If I change date delimiters to " ' ", I also get an error.
>
> I don't know how to get out of that !
>
> Thanks in advance for any tips !
>
> --yannick--
> no direct email reply : fake address
>

--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus ([url]http://www.grisoft.com[/url]).
Version: 7.0.230 / Virus Database: 263.3.3 - Release Date: 6/18/2004

[Aaron [SQL Server MVP]]

> SELECT myCode, myStuff, myDATE FROM myTable WHERE myDate = #05/21/2004#

>
> in ms-access interface, it works fine !
> I get real good lines but when I try to execute in my asp.net vb page,
> I get "internal automation error" without any other information.

You might think about using a safe date format, e.g. 2004-05-21.

> "where myDate LIKE #05/21/2004# "

No, don't ever treat a date column like a string. It is not stored the way
it looks!

> If I change date delimiters to " ' ", I also get an error.

That's because you're telling Access you're looking for a string. Access
expects # for date delimiters.

Followups set to the dotnet group only. This is not related to ASP.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)