ASP Question (1 Viewer)

[expublish]

Hi all,

1) Great new look forum!

2) I know this is not a direct Access question, but IMO Access and ASP go hand in hand.

My situation is as follows; I have an asp page that simply shows records from a Access DB table or query. They are showing fine. However, I need the ability to be able to show a message 'No matches found' if no records are returned.

My current ASP code is shown below, but when there are no records it doesn't work properly. Can anyone see where I have gone wrong?

<font face="verdana,sans-serif">
                        <p><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
                          <%
'here is the connection string
Set conn = server.createobject("adodb.connection")
'this connection uses JET 4 it is the prefered method of connecting to an access database
DSNtemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../add/data.mdb")
'if you cant use JET then comment out the line above and uncomment the line below
'DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../add/data.mdb")
conn.Open DSNtemp

SQL = "Select * from qryart"& qry
pagetot = 10000
		
const adOpenForwardOnly = 0
const adOpenKeySet = 1
const adOpenDynamic = 2
const adOpenStatic = 3
const adLockReadOnly = 1
const adLockPessimistic = 2
const adLockOptimistic = 3
const adLockBatchOptimistic = 4
Set RS = Server.CreateObject("Adodb.RecordSet")
RS.Open SQL, Conn, adopenkeyset, adlockoptimistic
If RS.EOF Then
%>
                          </font></p>
                        <p> <font face="verdana,sans-serif"><table cellspacing=1 border=0 cellpadding=2 width="100%">
  <tr> 
                            <td height="13"> 
                              <p><font size="2">There are no websites in this 
                                section. Add your web site <a href="../add/addsite.asp">here</a>.</font> 
                              </p>
                              </td>
  </tr>
</table>
                        </font> 
                        <p><font face="Verdana, Arial, Helvetica, sans-serif" size="2"> 
  <%ElseIf request("begin") = "" Then
        begin = 1
      Else
        begin = Cint(request("begin"))
      End If
	  
	  tot = ((begin+pagetot)-1)
	  
	  If tot > RS.RecordCount Then
		tot = RS.RecordCount
	  End If
	  
	  i = 1
	  navi = ""
	  For j = 1 to RS.RecordCount step pagetot
         If j <> 1 Then navi = navi &  " | "
			 If j = Cint(begin) Then
				navi = navi &  "<b>"&i&"</b>"
			Else
				navi = navi &   "<a href="""
				navi = navi &   Request.ServerVariables("url") & "?begin=" & j & "&which=" & tbl
				navi = navi &   """>"
				navi = navi &   i & "</a>" 
      		End If
		i = i + 1
	  Next
 	  i  = 1
	RS.Move begin - 1

%>
  </font></p>
                        <table cellspacing=1 border=0 cellpadding=1 width="565" height="54">
                          <tr align="left"> 
                            <th align=left width="275"> 
                              <div align="left"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=RS("Title")%></font></div>
                            </th>
                            <th width="277" colspan="2"> 
                              <div align="left"><font face="Verdana, Arial, Helvetica, sans-serif" size="2" color="#FF0000"><a href="<%=url%>">
                                <% url=RS("URL") %>
                                <%=RS("URL")%></a></font></div>
                            </th>
                          </tr>
                          <%
For x = begin to begin + (pagetot - 1)
If RS.EOF Then exit For
%>
                          <tr> 
                            <td align=left colspan="3" valign="middle" height="22"> 
                              <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=RS("Description")%></font></td>
                          </tr>
                          <%
i = i + 1
RS.MoveNext
Next
%>
                        </table>
<p><font face="Verdana, Arial, Helvetica, sans-serif" size="2"> 
  <%
RS.Close
Set RS = nothing
Conn.close
Set Conn = Nothing
%>
</font></p>
</font>

TIA

Scott Lyndon.

 

[RichMorrison]

Scott,

In your code is:

RS.Open SQL, Conn, adopenkeyset, adlockoptimistic
If RS.EOF Then

It's been a while since I have done any VBScript, but....
I think you want to test RS.RecordCount immediately after the Open statement. I think the EOF condition does not occur automatically when the recordset is empty.

RichM

 

[expublish]

Right, i think i follow ...

So any ideas what has to be changed to make it work?

At a very blind guess:

RS.Open SQL, If RS.EOF Then
Conn, adopenkeyset, adlockoptimistic

Scott.

 

[RichMorrison]

Scott,

I took a fast look at an ASP book and now I am uncertain about my previous reply. The book shows an example where:
1) a Connection is opened
2) a Recordset is instantiated by Set RS = conn.execute("Select....Where")
3) the result is evaluated with If RS.EOF Then "Nothing found"

Since your code example is similar it seems that it should work.

Maybe someone with better ADO (or better eyesight) will help.

RichM

 

[seven11]

What you will want to check for is the following:


If (RS.EOF and RS.BOF) Then
'do logic here to display no records message
else
'do logic here to display your records
end if

When a recordset returns empty, the End of File (EOF) and Beginning of File (BOF) attributes of the recordset will be True at the same time.

Just another note, make sure to explicitly destroy all objects before the end of your ASP page (i.e. set RS = nothing). This will make your code more efficient by releasing resources (memory) as soon as possible.

Good Luck - 7/11

 

[expublish]

Thanks for your reply.

I have changed my code a little, but it still doesn't work. Could you please point out what I have to change in my existing code to make it do what you suggest?

Thanks

Scott

Here's the code:

---

<p><%
'here is the connection string
Set conn = server.createobject("adodb.connection")
'this connection uses JET 4 it is the prefered method of connecting to an access database
DSNtemp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../add/data.mdb")
'if you cant use JET then comment out the line above and uncomment the line below
'DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../add/data.mdb")
conn.Open DSNtemp

SQL = "Select * from qryart"& qry
pagetot = 10000

const adOpenForwardOnly = 0
const adOpenKeySet = 1
const adOpenDynamic = 2
const adOpenStatic = 3
const adLockReadOnly = 1
const adLockPessimistic = 2
const adLockOptimistic = 3
const adLockBatchOptimistic = 4
Set RS = Server.CreateObject("Adodb.RecordSet")
RS.Open SQL, Conn, adopenkeyset, adlockoptimistic
If (RS.EOF and RS.BOF) Then
%>
</p>
<table cellspacing=1 border=0 cellpadding=2 width="100%">
<tr>
<td> <font face="Verdana, Arial, Helvetica, sans-serif" arial="arial" size="2">Sorry,
no records were found. </font><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><a href="index.htm">Homepage
</a></font> </td>
</tr>
</table>
<p><font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<%
ElseIf request("begin") = "" Then
begin = 1
Else
begin = Cint(request("begin"))
End If

tot = ((begin+pagetot)-1)

If tot > RS.RecordCount Then
tot = RS.RecordCount
End If

i = 1
navi = ""
For j = 1 to RS.RecordCount step pagetot
If j <> 1 Then navi = navi & " | "
If j = Cint(begin) Then
navi = navi & "<b>"&i&"</b>"
Else
navi = navi & "<a href="""
navi = navi & Request.ServerVariables("url") & "?begin=" & j & "&which=" & tbl
navi = navi & """>"
navi = navi & i & "</a>"
End If
i = i + 1
Next
i = 1
RS.Move begin - 1
%>
</font></p>
<table cellspacing=1 border=0 cellpadding=1 width="583">
<%
For x = begin to begin + (pagetot - 1)
If RS.EOF Then exit For
%>
<tr>
<td align=Left width="223"> <% url=RS("URL") %>
<div align="left"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><a href="<%=url%>"><%=RS("Title")%></a></font></div>
</td>
<td align=center width="353">
<div align="left"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=RS("Description")%></font></div>
</td>
</tr>
<%
i = i + 1
RS.MoveNext
Next
%>
</table>
<p>
<%
RS.Close
Set RS = nothing
Conn.close
Set Conn = Nothing
%>
</p>

---