Paging a recordset in a div by number and start of surname

[snowneil]

Hi,

What i want to try and do is have a list of customers but display them in rows of 10, with a next button for more records and also break them up by the start of their surname depending on what letter has been clicked.

I have a basic search page for a list of customers in .asp using javascript.

My sql statement is
"SELECT id,firstname,middlename,surname,postcode FROM Customer ORDER BY surname ASC";

At the moment my page displays all records in a list with a link to the customers full details next to each record.

Like the below:
<td bgcolor="<%=screenColour%>"><%= sqlRS('id')%></td>
<td bgcolor="<%=screenColour%>"><%= sqlRS('firstname')%></td>
<td bgcolor="<%=screenColour%>"><%= sqlRS('middlename')%></td>
<td bgcolor="<%=screenColour%>"><%= sqlRS('surname')%></td>
<td bgcolor="<%=screenColour%>"><%= sqlRS('postcode')%></td>

I am using a loop to change the background colour for each record so ignore the screenColour bit.

Here is an example of what i want to try and do, possibly using divs for each letter in the alphabet and displaying only the one selected:

<div id="A">
... 23 records found with surname starting with A ...
... list of 10 records ...
<a>Link to previous 10 records</a><a>Link to Next 10 records to be displayed</a>
</div>
Links for each letter of the alphabet to choose your filter<a>A</a>,B,C,D,E,F,G ...etc

 

[johnwm]

Start with your alphabet across the top of your query page as a link to itself, adding the appropriate querystring:

<tr>
<%
For a = 65 to 90
  response.write "<td><a href=" & chr(34) & "thispage.asp?initial=" & chr(a) & chr(34) & ">" & chr(a) & "</a></td>"
  Next
%>
</tr>

Then use the querystring to construct the appropriate SQL query:

"SELECT id,firstname,middlename,surname,postcode FROM Customer where surname LIKE '%" & request.QueryString("initial") & "' ORDER BY surname ASC";

Sorry this is in VBScript syntax - I'm rubbish at Javascript. You will need to check for empty "initial" for first arrival at the page and take appropriate action, and of course protect against SQL injection attack.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[snowneil]

Sorry for the late reply thanks for that info, i have managed to do it by creating an array of the alphabet and looping through it creating a link for each one, each link then puts the value in the querystring and that is used in the sql query.

I will look into paging it at some point in the future.