How to display msg on page when eof reached or no records found

[Guest_imported]

I have a program the runs a query on a Microsoft Access database. If someone were to type in a value on the previous page, and that value is not found in the table, it brings up "This page cannot be displayed".

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I believe its because the value was not found and it reached the eof. How could I display a message on the page stated no records were found instead of having it go to the error page. My eof loop looks like this:

<% 'Loop through the recordset to make each entry in the list.
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields(&quot;PRP_ADDR&quot.Value %></td>
<td><b><%= rstSearch.Fields(&quot;PLACE&quot.Value %></b> - Located at:  <%= rstSearch.Fields(&quot;PREC_ADDR&quot.Value %>.</td>
</tr>
<%

rstSearch.MoveNext
Loop
%>

Thanks!

 

[onpnt]

try
<%
If rstSearch.EOF or rstSearch.BOF = True then
response.write &quot;There are no records to show&quot;
else
Do while not rstSearch.EOF or rstSearch.BOF
%>
<tr>
<td><%= rstSearch.Fields(&quot;PRP_ADDR&quot.Value %></td>
<td><b><%= rstSearch.Fields(&quot;PLACE&quot.Value %></b>
- Located at:<%= rstSearch.Fields(&quot;PREC_ADDR&quot.Value
%>.</td>
</tr>
<%
rstSearch.MoveNext
end if
%>

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[Guest_imported]

Now I'm getting this error:

Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/VoteTest1/GetAddr.asp, line 78
End if

Don't know if it makes a difference, but that If statement ends up being nested inside another one that pre-existed.

I'll post the whole thing, in case it helps.

<%
If strSearchAddr <> &quot;&quot; AND strSearchZip <> &quot;&quot; Then
strDBPath =&quot;c:\Inetpub\

http://wwwroot\VoteTest1\POLL_PLACES.mdb&quot;

Set cnnSearch = Server.CreateObject(&quot;ADODB.Connection&quot
cnnSearch.Open &quot;Provider=MSDASQL.1;Persist Security Info=False;Data Source=Vote&quot;

' Build query based on the input.
strSQL = &quot;SELECT PRP_ADDR, PRP_ZIP, PLACE, PREC_ADDR &quot; _
& &quot;FROM VOTE_POLLS &quot; _
& &quot;WHERE PRP_ADDR LIKE '&quot; & Replace(strSearchAddr, &quot;'&quot;, &quot;''&quot & &quot;%' &quot; _
& &quot;AND PRP_ZIP LIKE '&quot; & Replace(strSearchZip, &quot;'&quot;, &quot;''&quot & &quot;' &quot; _
& &quot;ORDER BY PRP_ZIP;&quot;

Set rstSearch = cnnSearch.Execute(strSQL)
%>
<font color=&quot;#FF0000&quot;><center><b>Your results within the&nbsp;<%= rstSearch.Fields(&quot;PRP_ZIP&quot.Value %>&nbsp;zip code:</font></b></center>
<br><center><table width=&quot;90%&quot; border=&quot;1&quot;>
<tr BGCOLOR=&quot;#FFFFFF&quot;>
<th>Your Property Address:</th>
<th>P o l l i n g&nbsp;&nbsp;&nbsp;&nbsp;P l a c e:</th>
</tr>

<center>
<%
If rstSearch.EOF or rstSearch.BOF = True then
response.write &quot;There are no records to show&quot;
else
Do while not rstSearch.EOF or rstSearch.BOF
%>
<tr>
<td><%= rstSearch.Fields(&quot;PRP_ADDR&quot.Value %></td>
<td><b><%= rstSearch.Fields(&quot;PLACE&quot.Value %></b> - Located at:<%= rstSearch.Fields(&quot;PREC_ADDR&quot.Value %>.</td>
</tr>

<%
rstSearch.MoveNext
End if
%>

</table></center>

<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>

 

[GaryC123]

You've omitted the loop wtih the Do while not rstSearch.EOF or rstSearch.BOF

 

[Guest_imported]

I don't think so, unless there is something you know that I don't and since I'm extremely new at this, there probably is. But, the code I have above shows this:

<%
If rstSearch.EOF or rstSearch.BOF = True then
response.write &quot;There are no records to show&quot;
else
Do while not rstSearch.EOF or rstSearch.BOF
%>
<tr>
<td><%= rstSearch.Fields(&quot;PRP_ADDR&quot.Value %></td>
<td><b><%= rstSearch.Fields(&quot;PLACE&quot.Value %></b> - Located at:<%= rstSearch.Fields(&quot;PREC_ADDR&quot.Value %>.</td>
</tr>

<%
rstSearch.MoveNext
End if
%>

Am I missing something? The error message acts as if it has a problem with my End If

 

[Guest_imported]

Ahh - now I see what you're saying. The word Loop is missing. I guess I've looked at this code for too long. However, since I'm new at this I'm not sure if I've placed everything where it should be, because now I'm getting this error.

Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/VoteTest1/GetAddr.asp, line 57

Here is the code as I have it in the GetAddr.asp file (the first three lines represent line 57):

<font color=&quot;#FF0000&quot;><center><b>Your results within
the&nbsp;<%= rstSearch.Fields(&quot;PRP_ZIP&quot.Value %>
&nbsp;zip code:</font></b></center>
<br>
<center><table width=&quot;90%&quot; border=&quot;1&quot;>
<tr BGCOLOR=&quot;#FFFFFF&quot;>
<th>Your Property Address:</th>
<th>P o l l i n g&nbsp;&nbsp;P l a c e:</th>
</tr>
<center>

<%
If rstSearch.EOF or rstSearch.BOF = True then
response.write &quot;There are no records to show&quot;
else
Do while not rstSearch.EOF or rstSearch.BOF
%>

<tr>
<td><%= rstSearch.Fields(&quot;PRP_ADDR&quot.Value %></td>
<td><b><%= rstSearch.Fields(&quot;PLACE&quot.Value %></b> - Located
at:<%= rstSearch.Fields(&quot;PREC_ADDR&quot.Value %>.</td>
</tr>

<%
rstSearch.MoveNext
Loop
end if
%>
</table></center>

What did I do wrong this time?

 

[GaryC123]

In this section your pulling back a recordset value without error checking first if nothing is found.

<font color=&quot;#FF0000&quot;><center><b>Your results within
the <%= rstSearch.Fields(&quot;PRP_ZIP&quot.Value %>
zip code:</font></b></center>

Replace it with the zip value requested from the previous page, not a database value

 

[Guest_imported]

OK - Sorry for being such a pest, but I'm realy struggling with some of the concepts. But, I've corrected my code as you suggested and now am getting this error.

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/VoteTest2/GetAddr.asp, line 57

I get this error even if both text boxes are filled out with data that matches records in the DB. How would I check from the first page to make sure they've keyed data into both boxes? I thinking maybe running some type of function onsubmit(), but wouldn't have a clue as to how to do that one (only read something about it. And if they do enter values but those values don't match anything in the database, I still want a message to display on the page so that the user understands why they didn't get results. Again, sorry to be such a pain, but I'm seriously new to this! Thanks for all your help!

 

[Gorkem]

This means that it cannot find the column PRP_ZIP in the database.. Maybe you accidentally changed it in the code to something else.. you should double check.

Cheers,

G.