Displaying records problem

[stevem2004]

Hi,

I have an Access DB, if I am using the code below, is it possible to split the list (as I have about 200 records) into an alphabetical list, ideally it would be nice if I have A - Z across the top, plus # for numbers, which the user can click on to take them to a page where only that letter of the alphabet will be displayed. E.g. click S and get everything beginning with S.

I hope I have explained myself clearly, does anyone know if this is even possible?

TIA

Steve

<%
dim conn, rs, strconn, strSQL

strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("database")
set conn = server.createobject("adodb.connection")
conn.open strconn
strSQL = "SELECT * FROM table ORDER BY promocode ASC;"
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 3, 3


%>
<table border="1" width="620" bordercolor="#809CC9" cellpadding="1" cellspacing="0">
<tr>
<th class="smallbold">&nbsp;ID&nbsp;</th>
<th class="smallbold">Promo Code</th>
<th class="smallbold">Status</th>
<th class="smallbold">Country</th>
<th class="smallbold">Sales From</th>
<th class="smallbold">Sales To</th>
<th class="smallbold">Travel From</th>
<th class="smallbold">Travel To</th>
<th class="smallbold">Created by</th>
</tr>
<% Do While Not rs.EOF %>
<tr>
<td class="small" align="center" valign="top"><a href=view.asp?id=<%=rs("id")%>><%=rs("id")%></a></td>
<td class="small" align="center" valign="top"><%=rs("promocode")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("promostatus")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("country")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("salefrom")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("saleto")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("travelfrom")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("travelto")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("txtName")%>&nbsp;</td>
</tr>
<%
rs.MoveNext
Loop

%>
</table>

[simflex]

This is not hard to do.
Create 2 asp pages.
First page will have something like this:

<p><a href="SearchByLetter.asp?Letter=A"><b><font face="Arial" size="3">A
</font>
</b></a> <b><font face="Arial"><font face="Arial Black" size="3">
</font>
<a href="SearchByLetter.asp?Letter=B"><font size="3">B
</font>
</a>
<font face="Arial Black" size="3">
</font>
<a href="SearchByLetter.asp?Letter=C"> <font size="3"> C
</font>

Do this till the letter Z

Then on page 2 called SearchByLetter (you call this whatever you want),

You would create a query like this:

strSQL = "SELECT * From YourTable Where Letter LIKE '" & request.form("Letter") & "%';"


Your looping construct is fine.
You just need to close your connection to free up resources.

<%
rs.close
conn.close
%>

[stevem2004]

Hi simflex,

I just get an error msg, although the script looks like it might work

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/esol/promo/view/findbyletter.asp, line 22

line 22 is - rs.open strSQL, conn, 3, 3 -

[buntine]

Sorry to butt in, but Simflex accidently used the wrong collection in the SQL query.

strSQL = "SELECT * From YourTable Where Letter LIKE '" & Request.QueryString("Letter") & "%';"

That should get it going.

Regards,
Andrew Buntine.

[simflex]

thanks buntine,
for catching that.

To answer stevem2004's questions.
The too few parameters error means that you are either selecting fields to your recordset than you have in your form collection or you are selecting a field that doesn't exist.
For instance, if you do this:

select fname, lname, dob from your table

then you have:

<input type="text" name="fname" value="<%=rs("fname")%>">
<input type="text" name="fname" value="<%=rs("lname")%>">
You are going to get an error.
So either select the exact fields you need or use a wild *
like:
select * from your table.
It is slower but it is safer.

Second, you don't need to split your records but if you insist, there is an example that buntine has somewhere in one of his most recent responses..

[stevem2004]

Thanks simflex & buntine,

Still not working, the error msg is

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

What am I doing wrong??


My full code is

<%
dim conn, rs, strconn, strSQL


strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("database")
set conn = server.createobject("adodb.connection")
conn.open strconn
strSQL = "SELECT * FROM table WHERE Letter LIKE '%" & Request.QueryString("Letter") & "%';"
set rs = server.createobject("adodb.recordset")
rs.open strSQL, conn, 3, 3


%>
<table border="1" width="620" bordercolor="#809CC9" cellpadding="1" cellspacing="0">
<tr>
<th class="smallbold">&nbsp;ID&nbsp;</th>
<th class="smallbold">Promo Code</th>
<th class="smallbold">Status</th>
<th class="smallbold">Country</th>
<th class="smallbold">Sales From</th>
<th class="smallbold">Sales To</th>
<th class="smallbold">Travel From</th>
<th class="smallbold">Travel To</th>
<th class="smallbold">Created by</th>
</tr>
<% Do While Not rs.EOF %>
<tr>
<td class="small" align="center" valign="top"><a href=view.asp?id=<%=rs("id")%>><%=rs("id")%></a></td>
<td class="small" align="center" valign="top"><%=rs("promocode")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("promostatus")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("country")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("salefrom")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("saleto")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("travelfrom")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("travelto")%>&nbsp;</td>
<td class="small" align="center" valign="top"><%=rs("txtName")%>&nbsp;</td>
</tr>
<%
rs.MoveNext
Loop

%>
</table>