Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Stored Query & Parameters.

Reply
Thread Tools

Stored Query & Parameters.

 
 
AJ
Guest
Posts: n/a
 
      07-17-2006
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

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-17-2006
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
> 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

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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Full Text Search query without stored procedure Mate ASP .Net 2 07-15-2009 02:26 PM
LINQ Query : How to return a single value from a stored procedure psycho ASP .Net 2 11-20-2008 11:57 PM
sql query or stored procudre which one is giving best performance in asp.net tulasikumar ASP .Net 2 01-12-2007 08:44 PM
MS Access Query - use like stored procedure? Brenda Pasquarello ASP .Net 1 03-08-2006 12:46 PM
Server Error in '/' Application - when executing stored query - novice question sean ASP .Net 0 12-02-2003 05:43 AM



Advertisments