Stored Query & Parameters.

Discussion in 'ASP General' started by AJ, Jul 17, 2006.

  1. AJ

    AJ Guest

    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
     
    AJ, Jul 17, 2006
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Jul 17, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kavitha N via .NET 247

    passing parameters to stored procedure from crystal reports

    kavitha N via .NET 247, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    10,824
    galahad
    Feb 15, 2011
  2. TaeHo Yoo
    Replies:
    0
    Views:
    404
    TaeHo Yoo
    Aug 14, 2003
  3. Machelle Chandler
    Replies:
    1
    Views:
    1,980
    Thiago Almeida
    Oct 16, 2003
  4. Rob Meade

    Stored Proc with parameters

    Rob Meade, Aug 18, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    2,968
    Rob Meade
    Aug 18, 2004
  5. =?Utf-8?B?SklNLkgu?=

    Q: number of parameters in stored procedure

    =?Utf-8?B?SklNLkgu?=, Jan 11, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    1,286
    Steve C. Orr [MVP, MCSD]
    Jan 12, 2005
Loading...

Share This Page