Different results for the same query in ASP and SQL Server- F1 please. 1

[Deadline]

Hi,
I guess I have encountered the 8th wonder of the modern world.

I have a query, let us call SQLa. I attempted to run this in an ASP page.. I gave me 111 records.

But when I ran the same query in SQL Query Analyzer, against the same database, I am getting 243 or so records, never 111.

Why ? Have you encountered this situation ? Is there any Link or FAQ for this ?? Thank you...
RR

 

[FengShui1998]

DesperateCoder,

Can you show both your code from Query Analyzer and your ASP page? Try writing your SQL query string to the WEB page first before you execute it and then plug it into Query Analyzer.

fengshui1998

 

[Deadline]

Fengshui,
I wrote the query to the webpage, and then copied and pasted it to the Query Analyzer, and then I figured out that it was giving different results.

Here is the query I used in the webpage...

SELECT NAME, UPI, TripNumber,APPROVINGEMAIL, DEPDATE, C.ctryName AS Country, G.ID, G.Purpose, G.ApprovedTimeStamp AS Status FROM clearance AS G, ctry AS C WHERE G.CtryCode=C.CtryCode AND DepDate BETWEEN ('8/1/01') AND ('8/31/01') AND G.Ctrycode='IN' AND IsDate(G.ApprovedTimeStamp) = 1 ORDER BY APPROVEDTIMESTAMP,NAME

Thats the same one I copied and pasted in the SQL QA.


Why this happens to me alone!! :-(
Thank you...
RR

 

[Deadline]

A little desperate here... Can anyone of you help me out from this mess ??? Thank you...
RR

 

[FengShui1998]

desperatecoder,

From your reply, I thought you figured out what the problem was. So is the web page SQL string returning what you are supposed to be getting? I'm confused.

fengshui1998

 

[Deadline]

No actually, the query when executed in an ASP page using ADO/ OLEDB, it returns 111 or so results.

But, when I execute the same query in SQL Server Query Analyzer, it returns 243 or so records.

Why this difference ? Here are some sample outputs out of that query:-

NAME UPI TripNumber APPROVINGEMAIL DEPDATE Country ANYTHING Purpose Status
------------------------------------------------------------ ----------- ----------- ---------------------------------------------------------------------- --------------------------- --------------------------------------------------------------------------- ----------- ------- ---------------------------
XXXXXX,YYYYYY 166321 119174 NULL 2001-08-02 00:00:00.000 India 228 O 2001-08-03 06:44:09.000
XXX,XXXXXXX 184224 119126 NULL 2001-08-02 00:00:00.000 Belgium 239 O 2001-08-02 20:32:44.000
XXX,XXXXXXX 184224 119126 NULL 2001-08-02 00:00:00.000 Belgium 240 O 2001-08-02 20:32:44.000
Grosvenor_XXX 119706 119066 NULL 2001-08-03 00:00:00.000 India 185 O 2001-08-03 06:44:09.000 Thank you...
RR

 

[Deadline]

Thank you Fengshui and Sorry..

It was all my mistake..
I was using a variable to count the records, instead of RecordCOunt.

That variable got stuck outside the loop and was freaking me out all this time.. I noticed it and killed it and used RecordCount.
Now it is fine.. I sincerely apologise.. Thank you very much. Thank you...
RR