Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   Nested MSAccess Union-Queries in asp (http://www.velocityreviews.com/forums/t797461-nested-msaccess-union-queries-in-asp.html)

d2r2 12-28-2004 09:16 AM

Nested MSAccess Union-Queries in asp
 
Hi,

I'm trying to run a nested (UNION) query against a MSAccessXP database
(version 2002; build 10.6501.6714; SP3)
In Access the SQL-statement executes just fine. When I run it in a asp-page
I get the following error:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

Can a nested union-query be used at all or should I use an intermediate
table?

Thanks,
Djurre

Below are the details of the sql I want to use.

The query sums the competition results for several teams.
I have one table with the team-codes. This table is joined with the
match-results for home and away-games.
For home-games the team-code is record in the column game.home-team and for
away-games the team-code is recorded in the column game.away-team.
Per team I want to list: sort-code, team-code, team-name, games played,
wins, draws, losses, points, goals scored, goals against

The sql statement I want to use is like this:
Select
SELECT Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name], sum(played)
AS games, sum(w) AS Wins, sum(D) AS Draws, sum(L) AS Losses, sum(GP) AS
Gamepoints, sum(For) AS GF, sum(Against) AS GA
FROM (
SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 1, 0)) as W,
sum(Iif( WT.[goals-home] = WT.[goals-away] , 1, 0)) as D,
sum(Iif( WT.[goals-home] < WT.[goals-away] , 1, 0)) as L,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WT.[goals-home]) as For,
sum(WT.[goals-away]) as Against
FROM
Teams
left outer JOIN Games as WT ON (Teams.[Team-code] = WT.[Team-code home] and
WT.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]

UNION

SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WU.[goals-home] < WU.[goals-away] , 1, 0)) as W,
sum(Iif( WU.[goals-home] = WU.[goals-away] , 1, 0)) as D,
sum(Iif( WU.[goals-home] > WU.[goals-away] , 1, 0)) as L,
sum(Iif( WU[goals-home] < WU.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WU.[goals-away]) as For,
sum(WU.[goals-home]) as Against
FROM
Teams
left outer JOIN Games as WU ON (Teams.[Team-code] = WU.[Team-code away] and
WU.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
)

GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
ORDER BY Teams.[Sort-code];





Bob Barrows [MVP] 12-28-2004 11:58 AM

Re: Nested MSAccess Union-Queries in asp
 
I'm surprised it runs in Access without an alias for the subquery. The basic
syntax should be:

Select q.col1,...,q.colN FROM (select ...) As q

Personally, I would put the whole thing into a saved query and execute the
saved query from asp:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery rs

ADO allows you to run a stored procedure/saved query by treating it as if it
was a builtin method of the Connection object. If the saved query accepts
parameters, then you simply supply them as if they were arguments for the
method:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery parm1,...,parmN, rs

And if the query does not return records, simply leave off the recordset
variable:
conn.NameOfSavedQuery parm1,...,parmN

See here for a little more:
http://www.google.com/groups?selm=eE...&output=gplain -

I just noticed something else. See below:

d2r2 wrote:
> Hi,
>
> I'm trying to run a nested (UNION) query against a MSAccessXP database
> (version 2002; build 10.6501.6714; SP3)
> In Access the SQL-statement executes just fine. When I run it in a
> asp-page I get the following error:
> Microsoft JET Database Engine (0x80040E10)
> No value given for one or more required parameters.
>
> Can a nested union-query be used at all or should I use an
> intermediate table?
>

<snip>
> Select
> SELECT


This also seems suspicious. Two SELECT keywords in a row???

> Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
> sum(played) AS games, sum(w) AS Wins, sum(D) AS Draws, sum(L) AS
> Losses, sum(GP) AS Gamepoints, sum(For) AS GF, sum(Against) AS GA
> FROM (

<snip>
> )


See? No alias. The query engine does not know what is meant by "Teams." in
your SELECT, GROUP BY and WHERE clauses. You need to put

As Teams

here.
>
> GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
> ORDER BY Teams.[Sort-code];


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



d2r2 12-28-2004 01:38 PM

Re: Nested MSAccess Union-Queries in asp
 
Bob,
thanks for your pointers.
I noticed the typo (select select) too. I began to type in the statement and
then pasted it in anyway.

Initially I tried using a saved query but that didn't work as I connect thru
a DSN on the hosting service supplier webserver I use.
Locally the saved query does work thru IIS/Microsoft.Jet.OLEDB.4.0.

Found the bug.
In the asp-version of the sql-statement I included a "+" too many. Removed
that , and now it works just fine.

thanks again,
Djurre
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23m8nGSN7EHA.1452@TK2MSFTNGP11.phx.gbl...
> I'm surprised it runs in Access without an alias for the subquery. The
> basic syntax should be:
>
> Select q.col1,...,q.colN FROM (select ...) As q
>
> Personally, I would put the whole thing into a saved query and execute the
> saved query from asp:
>
> set rs=createobject("adodb.recordset")
> conn.NameOfSavedQuery rs
>
> ADO allows you to run a stored procedure/saved query by treating it as if
> it was a builtin method of the Connection object. If the saved query
> accepts parameters, then you simply supply them as if they were arguments
> for the method:
>
> set rs=createobject("adodb.recordset")
> conn.NameOfSavedQuery parm1,...,parmN, rs
>
> And if the query does not return records, simply leave off the recordset
> variable:
> conn.NameOfSavedQuery parm1,...,parmN
>
> See here for a little more:
> http://www.google.com/groups?selm=eE...&output=gplain -
>
> I just noticed something else. See below:
>
> d2r2 wrote:
>> Hi,
>>
>> I'm trying to run a nested (UNION) query against a MSAccessXP database
>> (version 2002; build 10.6501.6714; SP3)
>> In Access the SQL-statement executes just fine. When I run it in a
>> asp-page I get the following error:
>> Microsoft JET Database Engine (0x80040E10)
>> No value given for one or more required parameters.
>>
>> Can a nested union-query be used at all or should I use an
>> intermediate table?
>>

> <snip>
>> Select
>> SELECT

>
> This also seems suspicious. Two SELECT keywords in a row???
>
>> Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
>> sum(played) AS games, sum(w) AS Wins, sum(D) AS Draws, sum(L) AS
>> Losses, sum(GP) AS Gamepoints, sum(For) AS GF, sum(Against) AS GA
>> FROM (

> <snip>
>> )

>
> See? No alias. The query engine does not know what is meant by "Teams." in
> your SELECT, GROUP BY and WHERE clauses. You need to put
>
> As Teams
>
> here.
>>
>> GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
>> ORDER BY Teams.[Sort-code];

>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>





All times are GMT. The time now is 12:28 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.