SQL query problem with WHERE clause

[prophet224]

Hello, this is my first post, so thank you in advance for any help you may be able to give!

I have an ASP page that I need to have pull some information from a SQL table and display. The query in question is very simple:

rs.open "SELECT  UID2, EntryDate, EntryAgent FROM tb_LeadBox WHERE UID2 = '1258753'"

If I view the page with this query, I get a blank page with no header, and not even the column names in the display table.

If I then remove the WHERE clause so that the query is:

rs.open "SELECT  UID2, EntryDate, EntryAgent FROM tb_LeadBox"

then the query runs, the page runs, and displays the result set.

I have also tried rs.Filter = "UID2 = '1258753'" with the exact same result, a blank page.

Looking at the source code, the table shows the first result, but again, nothing actually displays. This fact, along with the fact that the query works in SQL Query Analyzer, indicates to me that it isn't a problem with the query, but with how ASP is handling it. Any help? Additional source is below. Thank you all very much!

<% 
DIM CONN,RS 
DIM SSQL 
DIM SORDER 
dim qCallGroupFilter 
 
userid = request.servervariables("LOGON_user") 
 
DBOPEN 
%> 
 
<CENTER><B><FONT size=5 color="black">Data Entry Lead Count 
For Call Group: <%response.write qCallGroupFilter%></FONT></B></CENTER> 
<CENTER><B><FONT size=5 color="black">Last Updated: <%RESPONSE.WRITE SLASTUPDATE%></font></b> </center> 
 
<button onClick="lockCol('tbl')"  id="toggle">Lock Name Column</button> 
<div id="tbl-container"> 
<table id="tbl" border=1 align=center cellpadding=0 cellspacing=0 width=100%> 
<thead><tr><th align=center>Count</th><th align=center>Entry Agent</th><th align=center>Entry Date</th><th align=center>User ID</th></tr></thead> 
 
<% 
dim cnt 
dim strSQL 
dim strWhere 
dim SStr 
cnt=1 
set RS.ActiveConnection = CONN 
rs.open "SELECT  UID2, EntryDate, EntryAgent FROM tb_LeadBox WHERE UID2 = '1258753'"   
 
do while not rs.eof 
 
%> 
 
 <TR> 
     <TD id=r<%=rs("EntryAgent")%>c0 align=center><%= cnt %> 
     </TD> 
     <TD id=r<%=rs("EntryAgent")%>c1 align=center><%=rs("EntryAgent")%> 
     </TD> 
     <TD  id=name_c2 c2 align=left><%=rs("EntryDate")%> 
     </TD> 
     <TD id=r<%=rs("UID2")%>c1 align=center><%=rs("UID2")%> 
     </TD> 
 </TR><%     
 cnt=cnt+1 
 RS.MOVENEXT 
LOOP 
%> 
</table>     
</div> <!-- end tbl-container --> 
 
Optimized for Resolutions 1024x768 and above. 
 
<% 
dbclose

[campkev]

from the information you have given, this shouldn't be happening. Let's try this, run sql profiler then load the page and watch what is actually being sent from asp to sql.

[prophet224]

Thank you so much for responding.
I hate to admit this (it's rather embarassing really), but it turns out that it was a simple solution.
After making some backups of the file, removing almost everything except the sql statement in question, and trying section by section, it turns out that where I had:

<script language = javascript>
<!--

it turns out that after my functions, I had left out a '--> at the end. Thus, all of the HTML in the page was being read as comments, so I got blank screens with working code.

I'm sorry, hopefully this will at least serve to emphasize to someone how important it is to ensure that you have both opening and closing syntax for everything.
It also indicates how a very simple problem can sometimes be extremely hard to find. Anyway again, thank you.
**************

Though I still don't know why it only happens when the 'WHERE' clause is added. Doesn't make sense to me. But it does work now. :)

[campkev]

glad you got it working

[cancer10]

If your UID2 field in your database has the datatype as interger then remove the single quotes from the number.
So your query should be like

rs.open "SELECT  UID2, EntryDate, EntryAgent FROM tb_LeadBox WHERE UID2 = 1258753"