Query runs in SQl server but not on ASP page

[Tapiwa Jongwe]

Hi,

I have a query to display a list of candidats for a ceratin post on an ASP
page. It seems to execute but does not display anything. When run directly
in SQL Server it does display some data. This behaviour is only specific for
ceratin posts, with others the result is displayed without any problem. I
suspect there's something wrong with the data but I cannot figure out what.
I am using classic ASP but using SQL Server 2005.

What could be the problem?

Thanks

 

[Bob Barrows]

Hi,

I have a query to display a list of candidats for a ceratin post on
an ASP page. It seems to execute but does not display anything. When run
directly in SQL Server it does display some data. This behaviour
is only specific for ceratin posts, with others the result is
displayed without any problem. I suspect there's something wrong
with the data but I cannot figure out what. I am using classic ASP
but using SQL Server 2005.
What could be the problem?

Thanks

There is no way for us to know given the information you've provided. I
suggest you fire up SQL Profiler and run a trace to see the sql statement
that is actually being executed on your server.

 

[Sylvain Lafontaine]

I suspect that you receiving a multi-resultset instead of a single
resultset. If you are calling a stored procedure, add the instruction SET
NOCOUNT ON at its very beginning. Otherwise, give some more details on what
you are doing.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tapiwa Jongwe]

I will tryn your suggestions tomorrow when I get back to work.

For the details:

My ASP page creates a dynamic query which joins about 4 tables. One of them
is joined twice. I use ADO to connect to SQL and create a recordset.
I printed the SQL query on the page using response.write and tried it
directly on SQL server - it works.The recordset produced is of about 300
lines with fields containg integers, ntext, varchar and bit.

When I add a condition in the query to limit my result set, say "where ID
between x1 and x2 the query works. I suspected there was some bad data so I
executed the query in 6 batches of 50 records and they all produce results.
I have also changed the cursortype to static, forward only, .... but same
story.

What could be the reason?

 

[Sylvain Lafontaine]

When we ask for details, we ask for about the query itself and the tables:
structures & data.

In your case, there are other possibilities like the fact that you might be
bit by a language problem such as the format of the date; if a date is used
to filter your query.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)