Unable to determine a blank SQL query in ASP

[hamning]

I'm trying to recognize an SQL query that results in no available records from the recordset. When I enter a value that I know is not in the record set, I keep getting "There is a problem with the page you are trying to reach and it cannot be displayed." When I enter a value known to be in the record set, it locates and displays it properly.

Here is what I've got:
sql = "select * from PTJobs where " & Request.Form("Query&quot
set rs = Server.CreateObject("ADODB.RecordSet&quot
rs.open sql,conn

If rs.EOF and rs.BOF Then
Response.Write "Sorry, no records were found matching that criteria."
Call CloseClientDatabase
Response.End
else
do while not rs.eof
response.write &quot;<br><font face='arial' size='2'>&quot; & &quot;<b>Title: </b>&quot; & rs(&quot;Title&quot & &quot;<br>&quot; & &quot; <b>Ad: </b>&quot; & rs(&quot;Advertisement&quot & &quot;<br>&quot; & &quot; <b>Location: </b>&quot; & rs(&quot;Location&quot & &quot;</font>&quot; & &quot;<br>_<br>&quot;
Response.Write rs.recordCount
rs.movenext
loop
End If

Any help is greatly appreciated..

Thanks

 

[Ovatvvon]

well, first I have a question.
In your sql statement, you put:

sql = &quot;select * from PTJobs where &quot; & Request.Form(&quot;Query&quot

It is my understanding that you usually will have a comparing statment for the WHERE clause. In other words, if you have a db of careers or jobs for people (PTJobs), and you have fields: pk, name, phonenumber, and jobname, and you wanted to pull up everyone with the job of &quot;Chimney Sweep&quot;, then you'd have that selected on the prior page, then request that and compare it like this:

<%
Dim job, sql
job = Request.Form(&quot;Query&quot 'or job, or whatever it's named

sql = &quot;select * from PTJobs where ((PTJobs.jobname)='&quot; & job & &quot;');&quot;
%>


This might be your problem..try it out. Also, instead of writing,

If rs.EOF and rs.BOF Then

Try useing an Or statement:

If rs.EOF Or rs.BOF Then

I don't think it would be both at the beginning AND the end of the file at the same time..this way, even using a dynamic cursor type, it'll track it both ways.

Try those, if doesn't work, try takin out all the code execpt the sql statement and what's needed to set it up, then write the sql statement to your page to see if the variables are being passed properly:


<%
Dim query, sql
query = Request.Form(&quot;Query&quot
sql = &quot;select * from PTJobs where &quot; & Request.Form(&quot;Query&quot
'SQL as you have it above...
Response.write(sql)
%>


Let me know if this helps.
-Ovatvvon :-Q

 

[Geee]

I have had this problem when I have passed nothing in the URL for the querystring to pick up. Just a thought.


G
-GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[shaddow]

Try this way
This should be the universal type of using Recordsets

U have to use these constants because the RecordSet object have a lot's of function that are'n accesible directly
like rs.open sql,com
and if u put the constants u could use all the function including rs.RecordCount that if this is 0 u have no records in..

Sorry for my english(i'm just a rookie)

<%
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

set rs=server.CreateObject(&quot;ADODB.Recordset&quot

sub ExecuteSQL(byval cmd)
if rs.State=1 then rs.Close 'if is already open u must close it before use it again
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = cmd
rs.ActiveConnection = conn 'The record set needs to know what connection to use.
rs.Open
end sub
%>
<%
sql = &quot;select * from PTJobs where &quot; & Request.Form(&quot;Query&quot
ExecuteSQL(sql)
If rs.RecordCount=0 Then 'no recordsets
Response.Write &quot;Sorry, no records were found matching that criteria.&quot;
Call CloseClientDatabase
Response.End
else
while not rs.eof
response.write &quot;<br><font face='arial' size='2'>&quot; & &quot;<b>Title: </b>&quot; & rs(&quot;Title&quot & &quot;<br>&quot; & &quot; <b>Ad: </b>&quot; & rs(&quot;Advertisement&quot & &quot;<br>&quot; & &quot; <b>Location: </b>&quot; & rs(&quot;Location&quot & &quot;</font>&quot; & &quot;<br>_<br>&quot;
Response.Write rs.recordCount
rs.movenext
wend
End If
%>

 

[Ovatvvon]

Has any of this helped you hamning?
-Ovatvvon :-Q

 

[hamning]

I've tried the suggestions and still can't figure out why I'm not getting an appropriate response when the SQL query results in zero records. I'm stumped.... This is the first time I've ever tried to utilize the query functionality. Usually, I've simply displayed the data, but that will not work for my current project.. Thanks for all your help! It is greatly appreciated..

Here is the entire test page:

Search Form:
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body bgcolor=&quot;#FFFFFF&quot;>
<form method=&quot;post&quot; action=&quot;TestPage.asp&quot;>
<font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><b><font size=&quot;3&quot;>Search</font></b></font>
<input type=&quot;text&quot; name=&quot;Query&quot;>
<input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Submit&quot;>
</form>
</body>
</html>
-----------------------------------
ASP PAGE:
Tested query:

<%
Dim query, sql
query = Request.Form(&quot;Query&quot
sql = &quot;select * from PTJobs where location = &quot; & Request.Form(&quot;Query&quot
'SQL as you have it above...
Response.write(sql)
%>

Result = select * from PTJobs where location = location
&quot;location&quot; is in the database and I get a full response
&quot;boulder&quot; is not in the database and it fails.. ;-(
-----------------------------------
ACTUAL ASP PAGE:


'<%
'Response.Expires = 15
'Response.ExpiresAbsolute = Now() - 2
'Response.AddHeader &quot;pragma&quot;,&quot;no-cache&quot;
'Response.AddHeader &quot;cache-control&quot;,&quot;private&quot;
'Response.CacheControl = &quot;no-cache&quot;
'%>


<%
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockPessimistic = 2

set query = Request.Form(&quot;Query&quot

connString = &quot;DSN=gq1com3.data;DRIVER=Microsoft Access Driver (*.mdb)&quot;
set conn = server.createobject(&quot;ADODB.Connection&quot
conn.open connString

sql = &quot;select * from PTJobs where location = &quot; & Request.Form(&quot;Query&quot
set rs=conn.execute(sql)
'server.CreateObject(&quot;ADODB.Recordset&quot

sub ExecuteSQL(byval cmd)
if rs.State=1 then rs.Close 'if is already open u must close it before use it again
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = cmd
rs.ActiveConnection = conn 'The record set needs to know what connection to use.
rs.Open
end sub
%>
<%

ExecuteSQL(sql)
'---> Both failed to respond properly if no records are available.
'If rs.EOF or rs.BOF then
If rs.RecordCount=0 Then 'no recordsets
Response.Write &quot;Sorry, no records were found matching that criteria.&quot;
Response.End
else
while not rs.eof
response.write &quot;<br><font face='arial' size='2'>&quot; & &quot;<b>Title: </b>&quot; & rs(&quot;Title&quot & &quot;<br>&quot; & &quot; <b>Ad: </b>&quot; & rs(&quot;Advertisement&quot & &quot;<br>&quot; & &quot; <b>Location: </b>&quot; & rs(&quot;Location&quot & &quot;</font>&quot; & &quot;<br>_<br>&quot;
Response.Write rs.recordCount
rs.movenext
wend
End If

rs.close
set rs = nothing
conn.close
set conn = nothing

%>

 

[hamning]

OK. Now I feel stupid.

This is all it turned out to be:
Original: sql = &quot;select * from PTJobs where location = &quot; & Request.Form(&quot;Query&quot

Fix: sql = &quot;select * from PTJobs where location = &quot; & &quot;'&quot; & Request.Form(&quot;Query&quot & &quot;'&quot;

The query was looking for at &quot;Query&quot; as a variable instead of a string..

Signed: HAPPY NOW.. Thanks to all of you who took the time to try and help me out...

Brian

 

[scorpio66]

One thing hamning - you will need to do this to prevent a nasty bug when the user includes the ' character as part of the query.

sql = "select * from PTJobs where location = " & "'" & Replace(Request.Form("Query"), "'", "''") & "'"

I always have a function SQLQuote

Function SQLQuote(inStr)
  SQLQuote = "'" & Replace(inStr, "'", "''") & "'"
End Function

which means you can do this:

  sql = "select * from PTJobs where location = " & SQLQuote(Request.Form("Query"))

which makes it a little easier to read.

Chaz

 

[hamning]

Excellent.. Thanks for the advice.

Bri