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.OLEDB.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

 

[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...

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...

 

[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.CloseConnection)

DataGrid1.DataSource = dr
DataGrid1.DataBind()
End Sub

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

 

[Rob T]

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

 

[Chris Gallucci]

----- Original Message -----
From: "Rob T" <[email protected]>
|

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

 

[Yannick]

Yes, I've tried with dates like:

05/05/2004, 05-05-2004

I always get the error "internal automation error"

?!

--yannick--

 

[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--

 

[Greg Burns]

CONVERT only works with SQL server as mentioned by Chris.

I suggest (again) that you use parameters.

Greg


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

----- Original Message -----
From: "Rob T" <[email protected]>
|

| 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.

 

[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

 

[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

 

[Chris Gallucci]

| 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