PDA

View Full Version : ASP/Access problem

cletus
01-12-2003, 06:49 PM
I've made an ASP script for paging through recordsets. It works perfectly - apart from this...
When it returns the first five records from the database, they're not ordered alphabetically. Same with the next five, and so on.
I'd like to know how to sort the database so that it returns all the records from A through to Z in order. At the moment, it seems to be returning them in the order they were created.
I've tried going into Access and sorting the database ascending, but that only works whilst viewing it.
I don't know if this can be done inside the ASP code, or if it's an Access issue.
Either way, I need help and would appreciate it if somebody could help me out.

Thanks in advance! :thumbsup:
arnyinc
01-12-2003, 08:17 PM
SELECT id, name, description FROM yourtable ORDER BY name
cletus
01-12-2003, 08:37 PM
Suppose that works for a second...
Whereabouts in this script would I put it, exactly?


<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
<style>
body { font-family : Verdana; font-size : 8pt; }
a { font-family : Verdana; font-size : 8pt; text-decoration : none; }
</style>
</head>

<body>
<p align="center">

<a href="pagingA.asp">A</a>&nbsp;|&nbsp;
<a href="pagingB.asp">B</a>&nbsp;|&nbsp;
<a href="pagingC.asp">C</a>&nbsp;|&nbsp;
<a href="pagingD.asp">D</a>&nbsp;|&nbsp;
</p>
<%
Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("paging.mdb")

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

rs.PageSize = 8
rs.CacheSize = 8
rs.CursorLocation = adUseClient
rs.Open "A", connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

If Not rs.EOF Then
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf

Dim fldF, intRec

Response.Write "<table border=0 align=center cellpadding=3 cellspacing=0><thead><tr>"

Response.Write "</tr></thead><tbody>"

For intRec=1 To rs.PageSize
If Not rs.EOF Then
Response.Write "<tr>"
For Each fldF in rs.Fields
Response.Write "<td>" & fldF.Value & "</td>"
Next
Response.Write "<tr>"
rs.MoveNext
End If
Next
Response.Write "</tbody></table><p>"

' Now showing first, next, back, last buttons.
response.Write("<center>")
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
Response.Write "&nbsp;|&nbsp;"

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If

Response.Write "&nbsp;|&nbsp;"

If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If
Response.Write "&nbsp;|&nbsp;"
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
response.Write "</p>"

Else
Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing
%>
</body>
</html>


Thanks
oracleguy
01-12-2003, 11:52 PM
Like this:


rs.Open "A ORDER BY name", connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
cletus
01-13-2003, 12:40 AM
I can safely say that doesn't work. Just tried it, no luck.
Someone on experts-exchange said to me that it could be to do with me having created a "firehose" cursor in my database.
Any ideas?
arnyinc
01-13-2003, 03:07 AM
This page mentions a firehose cursor. It looks like it's just a one-way cursor.

http://www.4guysfromrolla.com/webtech/112998-1.shtml

Wherever you specify your SQL statement is where you need to add the "ORDER BY" clause. It should be where the A is in this line, but I have no idea where you're getting A from.


rs.Open "A", connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect