Re: SELECT TOP Flaking Out...

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 09/26/04 

Date: Sun, 26 Sep 2004 07:54:27 -0400

Quasi wrote:
> 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

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"

Relevant Pages

  • Re: SELECT TOP Flaking Out...
    ... As for the WHERE clause, it returns what I thought it should. ... Both use SELECT TOP n in the SQL statement to get ... You should use the Query Builder to create and debug ... > version of database you are using ...
    (microsoft.public.inetserver.asp.db)
  • Re: IDENTITY, Access, and multiple SQL statements
    ... set conn = CreateObject ... gives me an error that the SQL statement doesn't end in a semi colon. ... can run multiple SQL statements on an MS Access database? ... This email account is my spam trap so I ...
    (microsoft.public.scripting.jscript)
  • Re: syntax error connecting database
    ... access database, but i keep getting the following error: ... sql string you have built via concatenation in vbscript. ... You cannot troubleshoot a sql statement without knowing what it is. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Checking for Existence in RS
    ... > used to filter a recordset. ... WHERE clause in a sql statement is for. ... I'd like to provide a specific example, but I don't know what database you ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: ADO data control
    ... Another way to test your query would be to try ... instinctively think that the database is probably not normalized. ... written on the topic of normalization, and one you might like is "Database ... Then the SQL statement and the existing WHERE and ORDER BY calculations ...
    (microsoft.public.vb.controls)