display problem when query is empty!?

Discussion in 'ASP General' started by Jerome, Aug 31, 2004.

  1. Jerome

    Jerome Guest

    Hi,

    I've got the following problem:

    I want my ASP page to display a certain number (based on a COUNT query),
    it works fine if the result is at least 1! If there are no records to be
    counted (= the query is empty), I get errors.

    What I've tried is this:

    if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
    response.write "amount: 0"
    else
    response.write "amount: " & response.write
    rsNP_MB_Site.Fields.Item("NP").Value
    end if

    But I keep getting an 'object required' error?

    I've also tried with isnull(rsNP_MB_Site.Fields.Item("NP").Value) but
    that didn't work out either.

    Any help is greatly appreciated,

    Jerome
     
    Jerome, Aug 31, 2004
    #1
    1. Advertising

  2. Jerome

    Evertjan. Guest

    Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:

    > I've got the following problem:
    >
    > I want my ASP page to display a certain number (based on a COUNT
    > query), it works fine if the result is at least 1! If there are no
    > records to be counted (= the query is empty), I get errors.


    A reasonable ;-) correct SQL count() will return a valid 0 count

    > What I've tried is this:
    >
    > if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then


    if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
    is the same as just:
    if rsNP_MB_Site.BOF or rsNB_MB_Site.EOF then

    > response.write "amount: 0"
    > else
    > response.write "amount: " & response.write
    > rsNP_MB_Site.Fields.Item("NP").Value


    This is vbs nonsense, should be:

    response.write "amount: " & rsNP_MB_Site.Fields.Item("NP").Value

    but how can this "Item" be the result of a SQL count() ?

    > end if
    >


    I suggest you show your code and specify the db-engine and connection
    type.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress,
    but let us keep the discussions in the newsgroup)
     
    Evertjan., Aug 31, 2004
    #2
    1. Advertising

  3. Jerome

    Jerome Guest

    Hi, sorry for my messed up coding ...

    Here's the SQL code (accessing an Access DB):

    SELECT Count(museebus_Site.IDUmeldung) AS NP
    FROM museebus_Site GROUP BY museebus_Site.printed HAVING
    (((museebus_Site.printed)=No));

    What I want to do is display on a summary page how many records there
    are which are still un-printed (hence the count).

    But if there are no unprinted records, the query is empty instead of
    having 0.

    Thanks for any help.

    Jerome


    Evertjan. wrote:

    > Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:
    >
    >
    >>I've got the following problem:
    >>
    >>I want my ASP page to display a certain number (based on a COUNT
    >>query), it works fine if the result is at least 1! If there are no
    >>records to be counted (= the query is empty), I get errors.

    >
    >
    > A reasonable ;-) correct SQL count() will return a valid 0 count
    >
    >
    >>What I've tried is this:
    >>
    >>if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then

    >
    >
    > if rsNP_MB_Site.BOF = true or rsNB_MB_Site.EOF = true then
    > is the same as just:
    > if rsNP_MB_Site.BOF or rsNB_MB_Site.EOF then
    >
    >
    >> response.write "amount: 0"
    >>else
    >> response.write "amount: " & response.write
    >>rsNP_MB_Site.Fields.Item("NP").Value

    >
    >
    > This is vbs nonsense, should be:
    >
    > response.write "amount: " & rsNP_MB_Site.Fields.Item("NP").Value
    >
    > but how can this "Item" be the result of a SQL count() ?
    >
    >
    >>end if
    >>

    >
    >
    > I suggest you show your code and specify the db-engine and connection
    > type.
    >
     
    Jerome, Aug 31, 2004
    #3
  4. Jerome

    Evertjan. Guest

    Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:

    > Hi, sorry for my messed up coding ...
    >
    > Here's the SQL code (accessing an Access DB):
    >
    > SELECT Count(museebus_Site.IDUmeldung) AS NP
    > FROM museebus_Site GROUP BY museebus_Site.printed HAVING
    > (((museebus_Site.printed)=No));
    >
    > What I want to do is display on a summary page how many records there
    > are which are still un-printed (hence the count).
    >


    Why not simply [something like/not tested]:

    SQL="SELECT Count(*) AS NP FROM museebus_Site "_
    "WHERE printed = 'No'"

    And then [assuming the table has records]:

    x.execute(SQL)

    response.write "amount: " & x("NP")


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress,
    but let us keep the discussions in the newsgroup)
     
    Evertjan., Aug 31, 2004
    #4
  5. Jerome

    Jerome Guest

    That works fine!

    Thanks.

    Evertjan. wrote:
    > Jerome wrote on 31 aug 2004 in microsoft.public.inetserver.asp.general:
    >
    >
    >>Hi, sorry for my messed up coding ...
    >>
    >>Here's the SQL code (accessing an Access DB):
    >>
    >>SELECT Count(museebus_Site.IDUmeldung) AS NP
    >>FROM museebus_Site GROUP BY museebus_Site.printed HAVING
    >>(((museebus_Site.printed)=No));
    >>
    >>What I want to do is display on a summary page how many records there
    >>are which are still un-printed (hence the count).
    >>

    >
    >
    > Why not simply [something like/not tested]:
    >
    > SQL="SELECT Count(*) AS NP FROM museebus_Site "_
    > "WHERE printed = 'No'"
    >
    > And then [assuming the table has records]:
    >
    > x.execute(SQL)
    >
    > response.write "amount: " & x("NP")
    >
    >
     
    Jerome, Aug 31, 2004
    #5
  6. Jerome wrote:
    > Hi, sorry for my messed up coding ...
    >
    > Here's the SQL code (accessing an Access DB):
    >
    > SELECT Count(museebus_Site.IDUmeldung) AS NP
    > FROM museebus_Site GROUP BY museebus_Site.printed HAVING
    > (((museebus_Site.printed)=No));
    >


    Evertian has it correct. You are confused about when it is correct to use
    Having instead of Where. The simple explanation is: WHERE conditions are
    applied BEFORE the records are grouped and aggregations are calculated.
    HAVING conditions are applied AFTER the grouping. Whenever possible, you
    should use WHERE, because the fewer records you supply to the grouping
    mechanism, the better your query will perform. Only use HAVING conditions
    when you need to evaluate the records after the grouping and aggregation are
    completed.


    Some guidelines:
    1. If the field you wish to filter appears in the GROUP BY clause, use WHERE

    Select a, sum(b)
    from table
    where a = 3
    group by a

    This query will return a single row

    2. If you wish to limit the resultset based on the result of an aggregation,
    use HAVING

    Select a, sum(b)
    from table
    group by a
    HAVING sum(b) > 25

    This query will return only rows where the sum is greater than 25.

    3. If the field you are grouping by does not appear in the SELECT list, then
    there is no need to put it in the GROUP BY clause:

    Select sum(b)
    from table
    where a = 3

    This query will again return a single row

    4. If the field you wish to filter appears neither in the GROUP BY list nor
    the SELECT list, use WHERE:

    Select a, sum(b)
    from table
    where c = 3
    group by a
    HAVING sum(b) > 25


    The reason that your query returned no records is because the HAVING clause
    was applied to the records resulting from the grouping operation. These
    records contained no field called printed, so the HAVING condition could
    never be satisfied, resulting in no records being returned.

    HTH,
    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], Aug 31, 2004
    #6
    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. John

    empty/non-empty element

    John, Jul 15, 2003, in forum: XML
    Replies:
    1
    Views:
    1,061
    Klaus Johannes Rusch
    Jul 16, 2003
  2. Lukas
    Replies:
    3
    Views:
    864
    spiff
    Nov 10, 2005
  3. Marcia Hon

    Check if a directory is empty and empty it

    Marcia Hon, Feb 8, 2004, in forum: C Programming
    Replies:
    8
    Views:
    610
    Dave Thompson
    Feb 14, 2004
  4. Brian Roberts

    empty lists vs empty generators

    Brian Roberts, May 3, 2005, in forum: Python
    Replies:
    12
    Views:
    680
    Jeremy Bowers
    May 4, 2005
  5. ButlerDJIAM
    Replies:
    0
    Views:
    580
    ButlerDJIAM
    Nov 9, 2006
Loading...

Share This Page