Stored Query & Parameters.

[AJ]

Folllowing on from a previous post, i have created a stored query as follows.

SELECT
c.ID, c.Company_Name, p.[level], 1 As QueryNbr
FROM
(Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
LEFT JOIN
Package AS p ON s.Package_ID = p.ID
WHERE
c.Category = 'EXH'
AND
(s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
AND
EXISTS(SELECT Company_ID FROM Event_Company_L ink WHERE Event_ID =
@EventID AND Company_ID = c.ID)
AND
(INT(Start_Date ) <= @StartDate) AND (INT(End_Date) >= @EndDate)
ORDER BY
p.[level] DESC , c.Company_Name, c.ID
UNION
SELECT
c.ID, c.Company_Name, p.[level], 2 As QueryNbr
FROM
(Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID)
LEFT JOIN
Package AS p ON s.Package_ID = p.ID
WHERE
c.Category = 'EXH'
AND
(s.ID = (SELECT Max(ID) FROM Sale WHERE Company_ID = c.ID) Or
IsNull(s.ID))
AND
EXISTS(SELECT Company_ID FROM Event_Company_L ink WHERE Event_ID
=@EventID AND Company_ID = c.ID)
ORDER BY
c.Company_Name, c.ID

I want use the results from the preceding query in the following way:

SELECT
ID, Company_Name, level, QueryNbr
FROM
ExhibitorsSearc hByName
//this query requires three parameters, Start_Date, End_Date, Event_ID
GROUP BY
ID, First(Company_N ame), First(level), First(QueryNbr)
WHERE
Company_Name LIKE '%myCriteria'

What would be the best way to execute the previous query in ASP, including
sending the appropriate parameters???

Sample code would be great!

Cheers,
Adam

[Bob Barrows [MVP]]

AJ wrote:

Folllowing on from a previous post, i have created a stored query as
follows.

Is this SQL Server or Access? ... OK, I just found your previous post and
discovered that this is Access.

I'm a little surprised it's allowing the @ symbol in your parameter names
.... I'm assuming you tested this and it works correctly in Access.

>

<snip>

I want use the results from the preceding query in the following way:

Did you try running the following query? It should not have worked as
written.

>
SELECT
ID, Company_Name, level, QueryNbr
FROM
ExhibitorsSearc hByName
//this query requires three parameters, Start_Date, End_Date,
Event_ID
GROUP BY
ID, First(Company_N ame), First(level), First(QueryNbr)
WHERE
Company_Name LIKE '%myCriteria'

The '%myCriteria should also be a parameter.
The WHERE clause (which should really come _before_ the GROUP BY clause if
you were going to keep using the WHERE clause) needs to be a HAVING clause,
because you are filtering by the result of an aggregate function that
provides a result _after_ the grouping is done.
The GROUP BY is strange. I think you want the aggregates in the Select
statement, don't you? Like this:
SELECT
ID,
First(Company_N ame) As Company_Name,
First(level) As level,
First(QueryNbr) As QueryNbr
FROM
ExhibitorsSearc hByName
GROUP BY
ID
HAVING
First(Company_N ame) LIKE [@myCriteria]
Test this query in Access before attempting to run it in ASP!!!

I would save this query as well - call it "ExhibitorsForS pecifiedCompany ".
When you test it, take note of the order in which Access prompts you for
parameter values. You will need to supply the values in the same order when
executing it from ASP.

>
What would be the best way to execute the previous query in ASP,
including sending the appropriate parameters???

Sample code would be great!

This is air code so it's untested:

Dim cn, rs, sDate, eDate, Event_ID, criteria
sDate=#2006-7-1#
eDate=#2006-7-31#
Event_ID = 28
criteria="%crit eria"

set cn=createobject ("adodb.connect ion")
cn.open "provider=micro soft.jet.oledb. 4.0;" & _
"data source=p:\ath\t o\db.mdb"
set rs=createobject ("adodb.records et")

'This is my guess as to the parameter order. Modify if your testing
'shows the order is different:

cn.ExhibitorsFo rSpecifiedCompa ny Event_ID,sDate, _
eDate, criteria, rs

if not rs.eof then ...

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"