Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Linux V Windows +paging through recordsets

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2009
    Posts
    4

    The Code

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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2009
    Posts
    4

    Cursor types

    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  7. #7
    Join Date
    Aug 2009
    Posts
    4

    Thanks

    Yes thats helped me sort it

    Thanks to all who took the time to reply

    Regards

    John Berman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •