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]

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

"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)

[Mike Smith]

hmm just something to try, have u checked other dates yet ?
something more in the line that works for d/m/y and m/d/y
as in dont use 21 incase its a datetime format error that you are
encountering.. try #05/05/2004# and see if u get an error...

"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)
hmm just something to try, have u checked other dates yet ?
something more in the line that works for d/m/y and m/d/y
as in dont use 21 incase its a datetime format error that you are
encountering.. try #05/05/2004# and see if u get an error...

"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)

[Ken Tucker [MVP]]

Hi,

Here is sample using the northwind database.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) 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.OleDbConn ection
Dim cmdOrders As OleDb.OleDbComm and
Dim dr As OleDb.OleDbData Reader

strConn = "Provider = Microsoft.Jet.O LEDB.4.0;"
strConn &= "Data Source = C:\Northwind.md b;"

conn = New OleDb.OleDbConn ection(strConn)

cmdOrders = New OleDb.OleDbComm and("Select OrderID, OrderDate,
ShipVia from Orders where OrderDate = #01/01/1998#", conn)

conn.Open()

dr = cmdOrders.Execu teReader(Comman dBehavior.Close Connection)

DataGrid1.DataS ource = dr
DataGrid1.DataB ind()
End Sub

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

"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)

--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.230 / Virus Database: 263.3.6 - Release Date: 6/25/2004

[Rob T]

try putting the date in single quotes? Where laData ='#5/21/2004#'

"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]

----- Original Message -----
From: "Rob T" <RT*********@DO NTwalchemSPAM.c om>
|

| try putting the date in single quotes? Where laData ='#5/21/2004#'
Access recognizes values delimited with # as a Date type. SQL Server does not. SQL Server will *implicitly* convert a string
(delimited by single quotes) to a DATETIME value *only* if the format of the date matches the default DATETIME format setting for
SQL Server. This can be observed by executing "SELECT GETDATE()" from a Query Analyzer window. This typically returns in the ODBC
canonical format of "yyyy-mm-dd hh:mi:ss.mmm" (style = 121).

To ensure string date value can be used by SQL Server use the CONVERT() function on your string value (example style setting is
101 - USA format mm/dd/yyyy)...

WHERE laDate = CONVERT( DATETIME, '05/21/2004', 101)

See SQL Server Books Online, Transact-SQL Reference, CAST and CONVERT for more information and other style settings.

ChrisG

[Yannick]

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

[Yannick]

Yes, I've tried with dates like:

05/05/2004, 05-05-2004

I always get the error "internal automation error"

?!

--yannick--
In article <Oj************ **@TK2MSFTNGP12 .phx.gbl>, te**@test.com
says...

hmm just something to try, have u checked other dates yet ?
something more in the line that works for d/m/y and m/d/y
as in dont use 21 incase its a datetime format error that you are
encountering.. try #05/05/2004# and see if u get an error...

[Yannick]

If I change my query to integrate your idea:
WHERE laDate = CONVERT( DATETIME, '05/21/2004', 101)

I get the error:
"La référence d'objet n'est pas définie à une instance d'un objet"
which can be translated to something like :
"Object reference not set to an instance of an object "

Any idea of what I can do ?

--yannick--

In article <OB************ **@tk2msftngp13 .phx.gbl>, ch***@gallucci. com
says...

----- Original Message -----
From: "Rob T" <RT*********@DO NTwalchemSPAM.c om>
|

| try putting the date in single quotes? Where laData ='#5/21/2004#'


Access recognizes values delimited with # as a Date type. SQL Server doesnot. SQL Server will *implicitly* convert a string
(delimited by single quotes) to a DATETIME value *only* if the format of the date matches the default DATETIME format setting for
SQL Server. This can be observed by executing "SELECT GETDATE()" from a Query Analyzer window. This typically returns in the ODBC
canonical format of "yyyy-mm-dd hh:mi:ss.mmm" (style = 121).

To ensure string date value can be used by SQL Server use the CONVERT() function on your string value (example style setting is
101 - USA format mm/dd/yyyy)...

WHERE laDate = CONVERT( DATETIME, '05/21/2004', 101)

See SQL Server Books Online, Transact-SQL Reference, CAST and CONVERT formore information and other style settings.

ChrisG

[Greg Burns]

CONVERT only works with SQL server as mentioned by Chris.

I suggest (again) that you use parameters. :)

Greg

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

If I change my query to integrate your idea:
WHERE laDate = CONVERT( DATETIME, '05/21/2004', 101)

I get the error:
"La référence d'objet n'est pas définie à une instance d'un objet"
which can be translated to something like :
"Object reference not set to an instance of an object "

Any idea of what I can do ?

--yannick--

In article <OB************ **@tk2msftngp13 .phx.gbl>, ch***@gallucci. com
says...

----- Original Message -----
From: "Rob T" <RT*********@DO NTwalchemSPAM.c om>
|

| try putting the date in single quotes? Where laData ='#5/21/2004#'
Access recognizes values delimited with # as a Date type. SQL Server does not. SQL Server will *implicitly* convert a string (delimited by single quotes) to a DATETIME value *only* if the format of the date matches the default DATETIME format setting for SQL Server. This can be observed by executing "SELECT GETDATE()" from a Query Analyzer window. This typically returns in the ODBC canonical format of "yyyy-mm-dd hh:mi:ss.mmm" (style = 121).

To ensure string date value can be used by SQL Server use the CONVERT() function on your string value (example style setting is 101 - USA format mm/dd/yyyy)...

WHERE laDate = CONVERT( DATETIME, '05/21/2004', 101)

See SQL Server Books Online, Transact-SQL Reference, CAST and CONVERT for more information and other style settings.
ChrisG