Linux V Windows +paging through recordsets

johnberman · #1 (**permalink**) 08-24-09, 08:57

Hi

I have created large number of pages using an old version of ASP Maker to access a variety of dbases, they have all worked well with no issues, recently our ISP have moved us from Linux to Windows – the databases all work fine with regards to searching and they bring back whats expected, however they long longer page

So if you go to here http://www.jgsgb.org.uk/1851/An_1851....asp?cmd=reset
And search for cohen it will return 10 results (there are in reality around 500 cohens in the dabse) and underneath say no records found

Previously under the search results I t would say page 1 of x etc and allow you to page through the results – now it simply skips that code and I cant understand why ?

I can post code here if that’s ok, we have loads of pages like this and I need to find an answer

Regards

John B

gvee · #2 (**permalink**) 08-24-09, 09:23

post the code and we'll see if we can spot anything

P.S. wrap your code in [ CODE] [/ CODE] (remove spaces) blocks to retain formatting and readability

johnberman · #3 (**permalink**) 08-24-09, 09:59

Thanks in advance, here is the code

Code:

<!--#INCLUDE FILE="inc/adovbs.inc"-->
<!--#include file="db.asp"-->
<%
On Error resume Next
%>

<% 
displayRecs = 10
recRange = 10
%>

<%

'tablename = "global"
tablename = "1851_Main"
pname = "1851_Mainlist.asp"
dataset = "United Kingdom Burial Records"
dc = 5
%>


<%	

stype = (request.querystring("stype"))
globsurname = (request.querystring("globsurname"))


' Build Query

set rs = Server.CreateObject("ADODB.Recordset")


If stype = "exact" then
strsql = "SELECT * FROM 1851_Main  WHERE MATCH (surname) AGAINST ('"& globsurname & "' IN BOOLEAN MODE ) order by Given_names, Year_of_birth "



Else
If stype = "start" then



strsql = "SELECT * FROM 1851_Main  WHERE surname_sdx = soundex('"& globsurname & "') "

Else
If stype = "sub" Then

strsql = "SELECT * FROM 1851_Main  WHERE SubjectID like ('"& globsurname & "') "

Else
If stype = "full" Then

given = (request.querystring("given"))


strsql = " SELECT * FROM 1851_Main  WHERE Surname like  ('"& globsurname & "') and Given_names like ('"& given & "')"




end if
end if
end If
end if







RS.Open strsql, xDb_Conn_Str, adOpenStatic, adLockReadOnly, adCmdText
totalRecs = rs.RecordCount

' Check for a START or PAGENO parameter
If Request.QueryString("start").Count > 0 Then
	startRec = Request.QueryString("start")
	Session("tablename") = tablename
	Session("startRec") = startRec
ElseIf Request.QueryString("pageno").Count > 0 Then
	pageno = Request.QueryString("pageno")
	if isnumeric(pageno) then
		startRec = (pageno-1)*displayRecs+1
		if startRec <= 0 then
			startRec = 1
		elseif startRec >= ((totalRecs-1)\displayRecs)*displayRecs+1 then
			startRec = ((totalRecs-1)\displayRecs)*displayRecs+1
		end if
		Session("tablename") = tablename
		Session("startRec") = startRec
	Else
		startRec = Session("startRec")
	End If
Else
	If tablename = Session("tablename") Then
		startRec = Session("startRec")
	Else
		'reset start record counter
		startRec = 1
		Session("startRec") = startRec
	End If
End If

'Set the last record to display
stopRec = startRec + displayRecs - 1
%>



<!--#include file="header.asp"-->


<table width="100%" border="0" cellspacing="10" cellpadding="0"><tr><td>




<% 'response.write strsql %>
<div align="center">
  <center>


<table width="75%" bordercolor="#C0C0C0" cellpadding="3" cellspacing="0" border="1" style="border-collapse: collapse">
	<!-- Table header -->
	<tr>
		<td valign="top" bgcolor="#FFFFCC"><span><b>Surname</b></span></td>
		<td valign="top" bgcolor="#FFFFCC"><span><b>Given names</b></span></td>
		<td valign="top" bgcolor="#FFFFCC"><span><b>Year of birth</b></span></td>
		<td valign="top" bgcolor="#FFFFCC"><span><b>Place of birth</b></span></td>
        <td valign="top" bgcolor="#FFFFCC"><span><b>View Record</b></span></td>

	</tr>

<%
recCount = 0
recActual = 0
Do While (NOT rs.EOF) AND (recCount < stopRec)
	recCount = recCount + 1
	If Clng(recCount) >= Clng(startRec) Then 
		recActual = recActual + 1 %>

<%
	'set row color
	bgcolor="#FFFFFc"
%>

<%	
	' Display alternate color for rows
	If recCount mod 2 <> 0 Then
		bgcolor="#FFFFFF"
	End If
%>

<%
x_ID = rs("ID")
	x_SubjectID = rs("SubjectID")
	x_Surname = rs("Surname")
	x_altsurname = rs("altsurname")
	x_Given_names = rs("Given_names")
	x_gender = rs("gender")
	x_Year_of_birth = rs("Year_of_birth")
	x_Date_of_birth = rs("Date_of_birth")
	x_Place_of_birth = rs("Place_of_birth")
	x_Source_birth = rs("Source_birth")
	x_Year_of_death = rs("Year_of_death")
	x_Date_of_death = rs("Date of death")
	x_Place_of_burial = rs("Place_of_burial")
	x_burial_plot_reference = rs("burial_plot_reference")
	x_Cause_of_death = rs("Cause_of_death")
	x_Source_death = rs("Source_death")
	x_Notes = rs("Notes")
	x_father = rs("father")
	x_mother = rs("mother")
	x_faithaffiliationearly = rs("faithaffiliationearly")
	x_faithaffiliationmid = rs("faithaffiliationmid")
	x_faithaffiliationlate = rs("faithaffiliationlate")
	x_sourcefaithaffiliation = rs("sourcefaithaffiliation")

	%>


<tr bgcolor="<%= bgcolor %>">

		<!-- SubjectID -->
		
		<!-- Surname -->
		<td><span>
<% Response.Write x_Surname %>
</span></td>

		<!-- Given_names -->
		<td><span>
<% Response.Write x_Given_names %>
</span></td>
		<!-- Year_of_birth -->
		<td><span>
<% Response.Write x_Year_of_birth %>
</span></td>
		<!-- Date_of_birth -->
		
		<!-- Place_of_birth -->
		<td><span>
<% Response.Write x_Place_of_birth %>
</span></td>
<td><span class="aspmaker"><a href="1851.asp?SubjectID=<%= Server.URLEncode(x_SubjectID)%>">View Record</a></span></td>
	</tr>












<%
	end if

	rs.MoveNext
Loop 
%>

</table>

  </center>
</div>

</td></tr></table>
</form>

<table border="0" cellspacing="0" cellpadding="10"><tr><td>
<%
if totalRecs > 0 then

	PrevStart = startRec - displayRecs
	If PrevStart < 1 Then PrevStart = 1
	NextStart = startRec + displayRecs
	If NextStart > totalRecs Then NextStart = startRec
	LastStart = ((totalRecs-1)\displayRecs)*displayRecs+1
	%>

<form>	
	<table border="0" cellspacing="0" cellpadding="0"><tr><td><font size="-1">Page</font>&nbsp;</td>
<!--first page button-->
	<% If clng(startRec)=1 Then %>
	<td><img src="images/firstdisab.gif" alt="First" width="20" height="15" border="0"></td>
	<% Else %>
	<td><a href="<%=pname%>?start=1&globsurname=<%= Server.URLEncode(globsurname) %>&stype=<%= Server.URLEncode(stype) %>"><img src="images/first.gif" alt="First" width="20" height="15" border="0"></a></td>
	<% End If %>



<!--previous page button-->
	<% If clng(PrevStart) = clng(startRec) Then %>
	<td><img src="images/prevdisab.gif" alt="Previous" width="20" height="15" border="0"></td>
	<% Else %>
	<td><a href="<%=pname%>?start=<%=PrevStart%>&globsurname=<%= Server.URLEncode(globsurname) %>&stype=<%= Server.URLEncode(stype) %>"><img src="images/prev.gif" alt="Previous" width="20" height="15" border="0"></a></td>
	<% End If %>
<!--current page number-->
	<td><input type="text" name="pageno" value="<%=(startRec-1)\displayRecs+1%>" size="4" style="font-size: 9pt;"></td>
<!--next page button-->
	<% If clng(NextStart) = clng(startRec) Then %>
	<td><img src="images/nextdisab.gif" alt="Next" width="20" height="15" border="0"></td>
	<% Else %>
	<td><a href="<%=pname%>?start=<%=NextStart%>&globsurname=<%= Server.URLEncode(globsurname) %>&stype=<%= Server.URLEncode(stype) %>"><img src="images/next.gif" alt="Next" width="20" height="15" border="0"></a></td>
	<% End If %>
<!--last page button-->
	<% If clng(LastStart) = clng(startRec) Then %>
	<td><img src="images/lastdisab.gif" alt="Last" width="20" height="15" border="0"></td>
	<% Else %>
	<td><a href="<%=pname%>?start=<%=LastStart%>&globsurname=<%= Server.URLEncode(globsurname) %>&stype=<%= Server.URLEncode(stype) %>"><img src="images/last.gif" alt="Last" width="20" height="15" border="0"></a></td>
	<% End If %>
	<td>&nbsp;<font size="-1">of <%=(totalRecs-1)\displayRecs+1%></font></td>
      </tr></table>	
</form>	
	<% If stopRec > recCount Then stopRec = recCount %>
	<font size="-1">Records <%= startRec %> to <%= stopRec %> of <%= totalRecs %></font>

<% Else %>

<p><font size="-1">No records found!</font></p>


<% End If %>
</td></tr></table>
<%
' Close recordset and connection
rs.Close
Set rs = Nothing


 %>
<!--#include file="footer.asp"-->

gvee · #4 (**permalink**) 08-24-09, 12:59

What database are you using; MySQL, SQL Server, etc?

The reason you get no paging is because this line

Code:

totalRecs = rs.RecordCount

Is returning a value less than or equal to zero; most likely -1. I believe that this is returned due to the type of cursor you are using on your data

Personally I would change your methodology slightly and start using .GetRows and enumerate the returned array instead of a recordset. The benefit of this is that you can close the connection as soon as .GetRows is called instead of waiting till you've done your looping logic. This also allows you to use the adOpenForwardOnly cursor type which is faster.

Here's a quick example:

Code:

<%
With objRS
   .Source = strSQL
   .ActiveConnection = objConn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
End With 

objRS.Open ,,,, adCmdText

If Not objRS.EOF then
   arrResults = objRS.GetRows
End if

objRS.Close
Set objRS = Nothing
...
If IsArray(arrResults) Then
   intRecordCount = UBound(arrResults, 2) + 1
End if
%>

Give this a quick bash to see if it alleviates your problems.

Any questions just let us know

johnberman · #5 (**permalink**) 08-24-09, 13:46

thanks, not sure im clever enough ?

We are using mysql

I think your saying that the cursor type is the issue ?, we were on Linux and I assume that supported x and now we are on windows this only supports x and the types supported may well be different ?

Can i set cursor types in adovbs.inc so at least this will work as a temp fix ?

John B

gvee · #6 (**permalink**) 08-26-09, 09:54

MySQL?

Code:

SELECT a.your
     , a.list
     , a.of
     , a.columns
     , b.total_records
FROM   your_table As a
 CROSS
  JOIN (
        SELECT Count(*) As total_records
        FROM   your_table
        WHERE  some_criteria = true
       ) As b
WHERE  a.some_criteria = true
ORDER
    BY a.order_by_column
LIMIT  20, 10

LIMIT X, Y.

X = starting record
Y = number of records

So in our example we are grabbing records 20 through to 30!

We are also returning the total number of records in the table as a column in our query; this alleviates the need for .RecordCount.

In the example we are only returning 10 rows to the ASP page, MySQL is doing the hard work. This will be more efficient than returning 100 records and only displaying a select 10

Hope this helps

johnberman · #7 (**permalink**) 08-26-09, 10:33

Yes thats helped me sort it

Thanks to all who took the time to reply

Regards

John Berman

Internet Services

Web Development

Digital Marketing

Consumer Tech