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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(leDataset)

[Greg Burns]

Try using parameters instead.

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=" & Server.MapPath(ConfigurationSettings.AppSettings(" dbPath")))
Dim cmd As New OleDbCommand("SELECT * 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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(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.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 cmdOrders As OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader

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

conn = New OleDb.OleDbConnection(strConn)

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

conn.Open()

dr = cmdOrders.ExecuteReader(CommandBehavior.CloseConne ction)

DataGrid1.DataSource = dr
DataGrid1.DataBind()
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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(leDataset)

[Chris Gallucci]

----- Original Message -----
From: "Rob T" <RT*********@DONTwalchemSPAM.com>
|

| 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.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=" & Server.MapPath(ConfigurationSettings.AppSettings(" dbPath")))
Dim cmd As New OleDbCommand("SELECT * 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*********@DONTwalchemSPAM.com>
|

| 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*********@DONTwalchemSPAM.com>
|

| 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

[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(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim cn As OleDb.OleDbConnection

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

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

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

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

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

Catch ex As Exception
MsgBox(ex.ToString)
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.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=" & Server.MapPath(ConfigurationSettings.AppSettings(" dbPath")))
Dim cmd As New OleDbCommand("SELECT * 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.SelectCommand.Connection = OLE_dataBase
OleData_prod.SelectCommand.CommandType = CommandType.Text
OleData_prod.SelectCommand.CommandText = requete

OleData_prod.SelectCommand.Connection.Open()

OleData_prod.SelectCommand.ExecuteReader()

OleData_prod.SelectCommand.Connection.Close()

OleData_prod.Fill(leDataset)

[Chris Gallucci]

"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message news:Od**************@TK2MSFTNGP12.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(ByVal sender As Object, ByVal e As
| System.EventArgs) Handles MyBase.Load
| Dim cn As OleDb.OleDbConnection
|
| cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
| Source=db.mdb")
|
| Dim sql As String
| sql = "SELECT * FROM laTable WHERE laDate = @laDate"
|
| Dim cmd As New OleDbCommand(sql, cn)
| cmd.Parameters.Add(New OleDbParameter("@laDate",
| OleDbType.Date)).Value = New DateTime(2004, 5, 21)
|
| Dim dr As OleDbDataReader
| Try
| cn.Open()
| dr = cmd.ExecuteReader()
|
| Do While dr.Read
| Debug.WriteLine(dr("laDate"))
| Loop
|
| Catch ex As Exception
| MsgBox(ex.ToString)
| 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