vb.net - sql statement with "where date ="

[Yannick]

Hi,

I've got a problem accessing a ms-access db with a sql statement like
this:

SELECT * FROM laTable WHERE laDate = #05/21/2004# ;
with asp.net (vb code)

laTable contains a "laDate" datetime field .

Request works in ms-access, but not in asp.net,
I get "internal automation error" on line:
OleDbDataAdapt. ExecuteReader()

I can't understand...

If I run a request with a "where" on something other than a date stuff,
it works both in access and asp.

I've tried some other things:
with " ' " => donnees incompatibles dans le critere
with date and time => automation error
with LIKE instead of " = " => automation error

I'm busy with that since now a few days...

thanks for any help

-- yannick --

PS:
config:
access 2000 database / asp.net framework 1.1 / VS.net

code:
requete = "SELECT * FROM laTable WHERE laDate = #05/21/2004#

OleData_prod.Se lectCommand.Con nection = OLE_dataBase
OleData_prod.Se lectCommand.Com mandType = CommandType.Tex t
OleData_prod.Se lectCommand.Com mandText = requete

OleData_prod.Se lectCommand.Con nection.Open()

OleData_prod.Se lectCommand.Exe cuteReader()

OleData_prod.Se lectCommand.Con nection.Close()

OleData_prod.Fi ll(leDataset)

[Greg Burns]

I just threw this Windows Forms program together in 2 minutes. It works for
me! (The database I am reading from is in MS Access 97 format)

Private Sub Form1_Load(ByVa l sender As Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Dim cn As OleDb.OleDbConn ection

cn = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=db.mdb")

Dim sql As String
sql = "SELECT * FROM laTable WHERE laDate = @laDate"

Dim cmd As New OleDbCommand(sq l, cn)
cmd.Parameters. Add(New OleDbParameter( "@laDate",
OleDbType.Date) ).Value = New DateTime(2004, 5, 21)

Dim dr As OleDbDataReader
Try
cn.Open()
dr = cmd.ExecuteRead er()

Do While dr.Read
Debug.WriteLine (dr("laDate"))
Loop

Catch ex As Exception
MsgBox(ex.ToStr ing)
Finally
If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close()
cn.Close()
End Try
End Sub

HTH,
Greg

"Yannick" <po****@nowhere .com> wrote in message
news:MP******** *************** *@news.wanadoo. fr...

I get exactly the same error !

I find this really strange. Am I the only one in the universe to get
that vb.net's behaviour ?!

--yannick--

In article <#N************ **@TK2MSFTNGP09 .phx.gbl>, you say...

Try using parameters instead.

Dim cn As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;Data Source=" & Server.MapPath( ConfigurationSe ttings.AppSetti ngs("dbPath")))
Dim cmd As New OleDbCommand("S ELECT * FROM laTable WHERE laDate = @laDate", cn)
cmd.Parameters. Add(New OleDbParameter( "@laDate", OleDbType.Date) ).Value = New DateTime(2004, 5, 21)
HTH,
Greg

[Ron Allen]

Yannick,
Have you tried this with just a list of named fields to retrieve vs. * ?
Possibly you have a reserved word for a field name which could be confusing
the OleDb parser. This often causes problems in ADO.NET where the same
query will work in Access. Start with just
Select laDate from laTable WHERE (laDate = #your date here#);
and see if that works.

Ron Allen
"Yannick" <po****@nowhere .com> wrote in message
news:MP******** *************** *@news.wanadoo. fr...

Hi,

I've got a problem accessing a ms-access db with a sql statement like
this:

SELECT * FROM laTable WHERE laDate = #05/21/2004# ;
with asp.net (vb code)

laTable contains a "laDate" datetime field .

Request works in ms-access, but not in asp.net,
I get "internal automation error" on line:
OleDbDataAdapt. ExecuteReader()

I can't understand...

If I run a request with a "where" on something other than a date stuff,
it works both in access and asp.

I've tried some other things:
with " ' " => donnees incompatibles dans le critere
with date and time => automation error
with LIKE instead of " = " => automation error

I'm busy with that since now a few days...

thanks for any help

-- yannick --

PS:
config:
access 2000 database / asp.net framework 1.1 / VS.net

code:
requete = "SELECT * FROM laTable WHERE laDate = #05/21/2004#

OleData_prod.Se lectCommand.Con nection = OLE_dataBase
OleData_prod.Se lectCommand.Com mandType = CommandType.Tex t
OleData_prod.Se lectCommand.Com mandText = requete

OleData_prod.Se lectCommand.Con nection.Open()

OleData_prod.Se lectCommand.Exe cuteReader()

OleData_prod.Se lectCommand.Con nection.Close()

OleData_prod.Fi ll(leDataset)

[Chris Gallucci]

"Greg Burns" <greg_burns@DON T_SPAM_ME_hotma il.com> wrote in message news:Od******** ******@TK2MSFTN GP12.phx.gbl...
| I just threw this Windows Forms program together in 2 minutes. It works for
| me! (The database I am reading from is in MS Access 97 format)
|
| Private Sub Form1_Load(ByVa l sender As Object, ByVal e As
| System.EventArg s) Handles MyBase.Load
| Dim cn As OleDb.OleDbConn ection
|
| cn = New OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data
| Source=db.mdb")
|
| Dim sql As String
| sql = "SELECT * FROM laTable WHERE laDate = @laDate"
|
| Dim cmd As New OleDbCommand(sq l, cn)
| cmd.Parameters. Add(New OleDbParameter( "@laDate",
| OleDbType.Date) ).Value = New DateTime(2004, 5, 21)
|
| Dim dr As OleDbDataReader
| Try
| cn.Open()
| dr = cmd.ExecuteRead er()
|
| Do While dr.Read
| Debug.WriteLine (dr("laDate"))
| Loop
|
| Catch ex As Exception
| MsgBox(ex.ToStr ing)
| Finally
| If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close()
| cn.Close()
| End Try
| End Sub
|
| HTH,
| Greg

Much better solution than my suggestion.

ChrisG