|
 |
access_asp thread: 0x80040E14
Message #1 by "Dave Savage" <dave@w...> on Wed, 11 Sep 2002 01:05:58
|
|
Does anyone know why:
<%@LANGUAGE="VBSCRIPT"%>
<% OPTION EXPLICIT %>
<!-- #include file ="adovbs.inc" -->
<!-- #include file ="includes/config.asp" -->
<%
' Select the latest deal (by date)
Dim SQL ' AS queryString
DIM Rs ' AS Recordset object
Dim arrData ' AS array
Dim numRows ' AS Integer
Dim i ' AS counter
Dim blnShow ' AS boolean
blnShow = false
SQL = "SELECT MAX(dateAdded), dealID, shortDescription FROM tblDeal"
Set Rs = server.createobject("ADODB.Recordset")
Rs.Open SQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
if not Rs.EOF then
blnShow = true
arrData = Rs.GetRows
end if
Rs.Close
Set Rs = Nothing
%>
would result in:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
that does not include the specified expression 'dealID' as part of an
aggregate function.
the error is thrown at Rs.Open....
TIA
Dave
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 11 Sep 2002 13:22:56 +1000
|
|
Couple of things:
a) You don't need
: blnShow = true
Latter on, just test to see if arrData is an array:
<%
If isArray(arrData) then
' We have results
Else
' We do not
End If
%>
b) You are getting the error because SQL does not work that way (ie the way
you are trying to use it). You want to get the Max(DateAdded) - that's easy:
SELECT
Max(DateAdded)
FROM
TableHere
but you also want to get a DealID. Well, what happens if you data looks like
this:
DateAdded DealID
1/1/1900 1
1/1/1900 2
The result set is going to look like:
DateAdded DealID
1/1/1900 ???
what is the database supposed to put into DealID? Instead, you need to do
what the error message says - use the GROUP BY clause to GROUP BY each
DealID - this gives you the Max(DateAdded) for each given DealID though.
SELECT
Max(DateAdded),
DealID
FROM
TableNameHere
GROUP BY
DealID
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dave Savage" <dave@w...>
Subject: [access_asp] 0x80040E14
: Does anyone know why:
:
: <%@LANGUAGE="VBSCRIPT"%>
: <% OPTION EXPLICIT %>
: <!-- #include file ="adovbs.inc" -->
: <!-- #include file ="includes/config.asp" -->
: <%
: ' Select the latest deal (by date)
:
: Dim SQL ' AS queryString
: DIM Rs ' AS Recordset object
: Dim arrData ' AS array
: Dim numRows ' AS Integer
: Dim i ' AS counter
: Dim blnShow ' AS boolean
: blnShow = false
: SQL = "SELECT MAX(dateAdded), dealID, shortDescription FROM tblDeal"
: Set Rs = server.createobject("ADODB.Recordset")
: Rs.Open SQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
: if not Rs.EOF then
: blnShow = true
: arrData = Rs.GetRows
: end if
: Rs.Close
: Set Rs = Nothing
: %>
:
: would result in:
:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: that does not include the specified expression 'dealID' as part of an
: aggregate function.
:
:
: the error is thrown at Rs.Open....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "Dave Savage" <dave@w...> on Wed, 11 Sep 2002 17:06:44 +0100
|
|
If the dateAdded field was a date/time field (which it is), would that not
make a difference?
Because then the data would look like this:
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 11 September 2002 04:23
To: Access ASP
Subject: [access_asp] Re: 0x80040E14
Couple of things:
a) You don't need
: blnShow = true
Latter on, just test to see if arrData is an array:
<%
If isArray(arrData) then
' We have results
Else
' We do not
End If
%>
b) You are getting the error because SQL does not work that way (ie the way
you are trying to use it). You want to get the Max(DateAdded) - that's easy:
SELECT
Max(DateAdded)
FROM
TableHere
but you also want to get a DealID. Well, what happens if you data looks like
this:
DateAdded DealID
1/1/1900 1
1/1/1900 2
The result set is going to look like:
DateAdded DealID
1/1/1900 ???
what is the database supposed to put into DealID? Instead, you need to do
what the error message says - use the GROUP BY clause to GROUP BY each
DealID - this gives you the Max(DateAdded) for each given DealID though.
SELECT
Max(DateAdded),
DealID
FROM
TableNameHere
GROUP BY
DealID
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dave Savage" <dave@w...>
Subject: [access_asp] 0x80040E14
: Does anyone know why:
:
: <%@LANGUAGE="VBSCRIPT"%>
: <% OPTION EXPLICIT %>
: <!-- #include file ="adovbs.inc" -->
: <!-- #include file ="includes/config.asp" -->
: <%
: ' Select the latest deal (by date)
:
: Dim SQL ' AS queryString
: DIM Rs ' AS Recordset object
: Dim arrData ' AS array
: Dim numRows ' AS Integer
: Dim i ' AS counter
: Dim blnShow ' AS boolean
: blnShow = false
: SQL = "SELECT MAX(dateAdded), dealID, shortDescription FROM tblDeal"
: Set Rs = server.createobject("ADODB.Recordset")
: Rs.Open SQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
: if not Rs.EOF then
: blnShow = true
: arrData = Rs.GetRows
: end if
: Rs.Close
: Set Rs = Nothing
: %>
:
: would result in:
:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: that does not include the specified expression 'dealID' as part of an
: aggregate function.
:
:
: the error is thrown at Rs.Open....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Dave Savage" <dave@w...> on Wed, 11 Sep 2002 17:06:45 +0100
|
|
If the dateAdded field were a date/time field (which it is), would that not
make a difference?
Because then the data would look like this:
DateAdded DealID
1/1/1900 12:15:01 1
1/1/1900 12:17:42 2
so is there not clearly a Max(dateAdded) here?
Dave
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 11 September 2002 04:23
To: Access ASP
Subject: [access_asp] Re: 0x80040E14
Couple of things:
a) You don't need
: blnShow = true
Latter on, just test to see if arrData is an array:
<%
If isArray(arrData) then
' We have results
Else
' We do not
End If
%>
b) You are getting the error because SQL does not work that way (ie the way
you are trying to use it). You want to get the Max(DateAdded) - that's easy:
SELECT
Max(DateAdded)
FROM
TableHere
but you also want to get a DealID. Well, what happens if you data looks like
this:
DateAdded DealID
1/1/1900 1
1/1/1900 2
The result set is going to look like:
DateAdded DealID
1/1/1900 ???
what is the database supposed to put into DealID? Instead, you need to do
what the error message says - use the GROUP BY clause to GROUP BY each
DealID - this gives you the Max(DateAdded) for each given DealID though.
SELECT
Max(DateAdded),
DealID
FROM
TableNameHere
GROUP BY
DealID
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Dave Savage" <dave@w...>
Subject: [access_asp] 0x80040E14
: Does anyone know why:
:
: <%@LANGUAGE="VBSCRIPT"%>
: <% OPTION EXPLICIT %>
: <!-- #include file ="adovbs.inc" -->
: <!-- #include file ="includes/config.asp" -->
: <%
: ' Select the latest deal (by date)
:
: Dim SQL ' AS queryString
: DIM Rs ' AS Recordset object
: Dim arrData ' AS array
: Dim numRows ' AS Integer
: Dim i ' AS counter
: Dim blnShow ' AS boolean
: blnShow = false
: SQL = "SELECT MAX(dateAdded), dealID, shortDescription FROM tblDeal"
: Set Rs = server.createobject("ADODB.Recordset")
: Rs.Open SQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
: if not Rs.EOF then
: blnShow = true
: arrData = Rs.GetRows
: end if
: Rs.Close
: Set Rs = Nothing
: %>
:
: would result in:
:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: that does not include the specified expression 'dealID' as part of an
: aggregate function.
:
:
: the error is thrown at Rs.Open....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by "Ken Schaefer" <ken@a...> on Thu, 12 Sep 2002 12:05:32 +1000
|
|
...Yes, in your case, with the specific data that you have below, there is
clearly a Max(DateAdded).
But think about how SQL (the language) was developed. It needs to work in
*all* situations. And the query that you are trying to run would fail if
your data looked like this:
DateAdded DealID
1/1/1900 12:15:01 1
1/1/1900 12:17:42 1
1/1/1900 12:17:42 2
That's why you need GROUP BY, because it groups by each DealID, then gets
the MAX for each DeadlID, so your output would be:
DateAdded DealID
1/1/1900 12:15:01 1
1/1/1900 12:17:42 2
Now, if you want only a single record returned and you know what formula you
want to use to resolve the deadlock above (or you know that the date/times
will always be unique), then you could use a subselect to return the
DateAdded, then find the first corresponding DealD
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Dave Savage" <dave@w...>
To: "Access ASP" <access_asp@p...>
Sent: Thursday, September 12, 2002 2:06 AM
Subject: [access_asp] Re: 0x80040E14
: If the dateAdded field were a date/time field (which it is), would that
not
: make a difference?
: Because then the data would look like this:
:
: DateAdded DealID
: 1/1/1900 12:15:01 1
: 1/1/1900 12:17:42 2
:
: so is there not clearly a Max(dateAdded) here?
:
: Dave
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 11 September 2002 04:23
: To: Access ASP
: Subject: [access_asp] Re: 0x80040E14
:
:
: Couple of things:
:
: a) You don't need
: : blnShow = true
: Latter on, just test to see if arrData is an array:
:
: <%
: If isArray(arrData) then
: ' We have results
: Else
: ' We do not
: End If
: %>
:
: b) You are getting the error because SQL does not work that way (ie the
way
: you are trying to use it). You want to get the Max(DateAdded) - that's
easy:
:
: SELECT
: Max(DateAdded)
: FROM
: TableHere
:
: but you also want to get a DealID. Well, what happens if you data looks
like
: this:
:
: DateAdded DealID
: 1/1/1900 1
: 1/1/1900 2
:
: The result set is going to look like:
:
: DateAdded DealID
: 1/1/1900 ???
:
: what is the database supposed to put into DealID? Instead, you need to do
: what the error message says - use the GROUP BY clause to GROUP BY each
: DealID - this gives you the Max(DateAdded) for each given DealID though.
:
: SELECT
: Max(DateAdded),
: DealID
: FROM
: TableNameHere
: GROUP BY
: DealID
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Dave Savage" <dave@w...>
: Subject: [access_asp] 0x80040E14
:
:
: : Does anyone know why:
: :
: : <%@LANGUAGE="VBSCRIPT"%>
: : <% OPTION EXPLICIT %>
: : <!-- #include file ="adovbs.inc" -->
: : <!-- #include file ="includes/config.asp" -->
: : <%
: : ' Select the latest deal (by date)
: :
: : Dim SQL ' AS queryString
: : DIM Rs ' AS Recordset object
: : Dim arrData ' AS array
: : Dim numRows ' AS Integer
: : Dim i ' AS counter
: : Dim blnShow ' AS boolean
: : blnShow = false
: : SQL = "SELECT MAX(dateAdded), dealID, shortDescription FROM tblDeal"
: : Set Rs = server.createobject("ADODB.Recordset")
: : Rs.Open SQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
: : if not Rs.EOF then
: : blnShow = true
: : arrData = Rs.GetRows
: : end if
: : Rs.Close
: : Set Rs = Nothing
: : %>
: :
: : would result in:
: :
: : Error Type:
: : Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
: : [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
: : that does not include the specified expression 'dealID' as part of an
: : aggregate function.
: :
: :
: : the error is thrown at Rs.Open....
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:
:
:
:
|
|
|
View Cart
