DaniWeb IT Discussion Community - SQL query problem with WHERE clause

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/index.php)

- ASP (http://www.daniweb.com/forums/forum62.html)

- - SQL query problem with WHERE clause (http://www.daniweb.com/forums/thread53581.html)

SQL query problem with WHERE clause

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

Re: SQL query problem with WHERE clause

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.

Re: SQL query problem with WHERE clause

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. :)

Re: SQL query problem with WHERE clause

glad you got it working

Re: SQL query problem with WHERE clause

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"