Professional Web Applications Themes

SELECT TOP Flaking Out... - ASP Database

I'm having a very frustrating problem...I've got two pages, each of which are supposed to do basically the same thing, but from two different tables. Both use SELECT TOP n in the SQL statement to get only a certain number of records. One of them works (only returns 3 rows) and the other does not (returns all rows)...I don't get it. This one works: "SELECT TOP 3 lngID, strName, datDateStart, datDateEnd " &_ "FROM Events " &_ "WHERE bitHide=False AND datDateShowAsOf <= #" & Date & "# " &_ "ORDER BY datDateStart;" This one doesn't: "SELECT TOP 3 lngID, strName ...

  1. #1

    Default SELECT TOP Flaking Out...

    I'm having a very frustrating problem...I've got two pages, each of which are
    supposed to do basically the same thing, but from two different tables. Both
    use SELECT TOP n in the SQL statement to get only a certain number of
    records. One of them works (only returns 3 rows) and the other does not
    (returns all rows)...I don't get it.

    This one works:
    "SELECT TOP 3 lngID, strName, datDateStart, datDateEnd " &_
    "FROM Events " &_
    "WHERE bitHide=False AND datDateShowAsOf <= #" & Date & "# " &_
    "ORDER BY datDateStart;"

    This one doesn't:
    "SELECT TOP 3 lngID, strName " &_
    "FROM News " &_
    "WHERE bitHide=False AND datDateShowAsOf <= #" & Date & "# " &_
    "ORDER BY datDatePosted DESC;"

    I tried the second one without specifying the DESC part, thinking that might
    be it, but still didn't work. I can change the value of the TOP part of the
    first statement up there and it always returns the correct number of rows...I
    have no idea why the second one doesn't and always returns all rows.

    Any advice would be greatly appreciated...thanks in advance!

    LZ
    Quasi Guest

  2. #2

    Default Re: SELECT TOP Flaking Out...

    Quasi wrote: 

    You cannot troubleshoot sql statements without knowing what they actually
    are. You need to assign the statement to a variable and response.write the
    variable.

    sSQL = "Select ... "
    Response.Write sSQL

    This will allow you to open your database in Access*, create a new query in
    Design View, switch to SQL View, paste in the statement from the browser
    window, and try it out. You should use the Query Builder to create and debug
    all your queries before attempting to run them from any client application
    such as ASP.

    I suspect you will find that the WHERE clause in the statement that returns
    the incorrect results is not what you think it is.

    Bob Barrows

    *I'm guessing that you are using Access based on your use of # around the
    dates - in the future, please don't make us guess: tell us the type AND
    version of database you are using
    --
    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 Guest

  3. #3

    Default Re: SELECT TOP Flaking Out...

    Bob,

    *Sorry about this part of your message...will do.

    As for the WHERE clause, it returns what I thought it should.

    What I didn't realize was that since the dates of the items specified in the
    ORDER BY part of the statement were all the same, this causes the TOP part of
    the statement to return everything. I changed the dates in the five test
    records to all be different and now I only get three as desired as opposed to
    all five records.

    LZ

    "Bob Barrows [MVP]" wrote:
     
    >
    > You cannot troubleshoot sql statements without knowing what they actually
    > are. You need to assign the statement to a variable and response.write the
    > variable.
    >
    > sSQL = "Select ... "
    > Response.Write sSQL
    >
    > This will allow you to open your database in Access*, create a new query in
    > Design View, switch to SQL View, paste in the statement from the browser
    > window, and try it out. You should use the Query Builder to create and debug
    > all your queries before attempting to run them from any client application
    > such as ASP.
    >
    > I suspect you will find that the WHERE clause in the statement that returns
    > the incorrect results is not what you think it is.
    >
    > Bob Barrows
    >
    > *I'm guessing that you are using Access based on your use of # around the
    > dates - in the future, please don't make us guess: tell us the type AND
    > version of database you are using
    > --[/ref]
    Quasi Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139