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_Link 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_Link 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
ExhibitorsSearchByName
//this query requires three parameters, Start_Date, End_Date, Event_ID
GROUP BY
ID, First(Company_Name), 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]]

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.

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
ExhibitorsSearchByName
//this query requires three parameters, Start_Date, End_Date,
Event_ID
GROUP BY
ID, First(Company_Name), 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_Name) As Company_Name,
First(level) As level,
First(QueryNbr) As QueryNbr
FROM
ExhibitorsSearchByName
GROUP BY
ID
HAVING
First(Company_Name) LIKE [@myCriteria]


Test this query in Access before attempting to run it in ASP!!!

I would save this query as well - call it "ExhibitorsForSpecifiedCompany".
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="%criteria"

set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=p:\ath\to\db.mdb"
set rs=createobject("adodb.recordset")

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

cn.ExhibitorsForSpecifiedCompany Event_ID,sDate, _
eDate, criteria, rs

if not rs.eof then ...

Bob Barrows