Ad:

ASP Discussion Thread
Unsolved
Views: 5623

Aug 28th, 2006

SQL query problem with WHERE clause

Expand Post »

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:

ASP Syntax (Toggle Plain Text)

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:

ASP Syntax (Toggle Plain Text)

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!

(Toggle Plain Text)

<% 
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

Similar Threads

sql query problem with MS Access and C# in C#
sql query updating problem in Visual Basic 4 / 5 / 6
Please help me out with MySQL query in MySQL
query problem in MySQL

prophet224

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Aug 2006

Permalink

Aug 29th, 2006

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.

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Aug 30th, 2006

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:

ASP Syntax (Toggle Plain Text)

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

Last edited by prophet224; Aug 30th, 2006 at 3:45 pm. Reason: Wanted to add about the 'WHERE' at the end

prophet224

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Aug 2006

Permalink

Aug 30th, 2006

Re: SQL query problem with WHERE clause

glad you got it working

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Oct 12th, 2006

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

ASP Syntax (Toggle Plain Text)

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

cancer10

Reputation Points: 58

Solved Threads: 1

Posting Whiz in Training

Offline

234 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP Forum Timeline: Creating a login page

Next Thread in ASP Forum Timeline: Why this error???Operation is not allowed when the object is closed.

DaniWeb Message

Cancel Changes

User Name
Password