Scrollable querys

Trillithium · Apr 2, 2001

Hi, im a newbe at ASP and i need help.
Can somebody tell me how to do scrollable querys in an ASP form. I want to scroll in ASP through a database (access) with VBscript, please help me..

smbure · Apr 2, 2001

Hey Trill,

Couple things first...

I think that scrollable query means that you want to gather records and navigate one by one or n by n. If this is the case, you did mention ASP but I would like to verify that you would like to navigate through the records request by request, or do you mean that you would pull down a bunch of records and handle the paging at the client?

smbure

phuctran · Apr 2, 2001

<html><head>
<TITLE>dbtablepaged.asp</TITLE>
</head><body bgcolor="#FFFFFF">

<%
connectme="DSN=Student;uid=student;pwd=magic"
sqltemp="select * from publishers"

' Troubleshooting TIP:
' if you use this code and get an error, for example:
'
' ADODB.Recordset error 800a0cb3
'
' The operation requested by the application is not
' supported by the provider.
'
' You may have a driver that is out of date, see:
'

http://www.learnasp.com/learn/connectioninfo.asp

' for code that will identify what your driver version is
' this script works with Access, SQLserver and Oracle
' with up-to-date drivers

If aduseclient="" THEN
ref="

http://www.learnasp.com/adovbs.inc"

response.write "You forgot to include:<br>"
response.write "/adovbs.inc<br>"
response.write "Get the file from <a href='" & ref & "'>" & ref & "<br>"
response.end
END IF

mypage=request("whichpage&quot

If mypage="" then
mypage=1
end if
mypagesize=request("pagesize&quot

If mypagesize="" then
mypagesize=10
end if
mySQL=request("SQLquery&quot

IF mySQL="" THEN
mySQL=SQLtemp
END IF

set rstemp=Server.CreateObject("ADODB.Recordset&quot

rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
tempSQL=lcase(mySQL)
badquery=false
IF instr(tempSQL,"delete&quot

>0 THEN
badquery=true
END IF
IF instr(tempSQL,"insert&quot

>0 THEN
badquery=true
END IF
IF instr(tempSQL,"update&quot

>0 THEN
badquery=true
END IF
If badquery=true THEN
response.write "Not a SELECT Statement<br>"
response.end
END IF

rstemp.open mySQL,connectme
rstemp.movefirst
rstemp.pagesize=mypagesize
maxpages=cint(rstemp.pagecount)
maxrecs=cint(rstemp.pagesize)
rstemp.absolutepage=mypage
howmanyrecs=0
howmanyfields=rstemp.fields.count -1
response.write "Page " & mypage & " of " & maxpages & "<br>"
response.write "<table border='1'><tr>"

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td><b>" & rstemp(i).name & "</b></td>"
NEXT
response.write "</tr>"

' Now loop through the data
DO UNTIL rstemp.eof OR howmanyrecs>=maxrecs
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=rstemp(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue=" "
END IF
response.write "<td valign='top'>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
rstemp.movenext
howmanyrecs=howmanyrecs+1
LOOP
response.write "</table><p>"

' close, destroy
rstemp.close
set rstemp=nothing

' Now make the page _ of _ hyperlinks
Call PageNavBar

sub PageNavBar()
' Thanks to Jeff Emrich <jeff.emrich@datafuse.com>
pad=""
scriptname=request.servervariables("script_name&quot

response.write "<table rows='1' cols='1' width='97%'><tr>"
response.write "<td>"
response.write "<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>"
if (mypage mod 10) = 0 then
counterstart = mypage - 9
else
counterstart = mypage - (mypage mod 10) + 1
end if
counterend = counterstart + 9
if counterend > maxpages then counterend = maxpages
if counterstart <> 1 then
ref="<a href='" & scriptname
ref=ref & "?whichpage=" & 1
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>First</a> : "
Response.Write ref

ref="<a href='" & scriptname
ref=ref & "?whichpage=" & (counterstart - 1)
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Previous</a> "
Response.Write ref
end if
Response.Write "["
for counter=counterstart to counterend
If counter>=10 then
pad=""
end if
if cstr(counter) <> mypage then
ref="<a href='" & scriptname
ref=ref & "?whichpage=" & counter
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>" & pad & counter & "</a>"
else
ref="<b>" & pad & counter & "</b>"
end if
response.write ref
if counter <> counterend then response.write " "
next
Response.Write "]"
if counterend <> maxpages then
ref=" <a href='" & scriptname
ref=ref & "?whichpage=" & (counterend + 1)
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Next</a>"
Response.Write ref

ref=" : <a href='" & scriptname
ref=ref & "?whichpage=" & maxpages
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Last</a>"
Response.Write ref
end if
response.write "<br></font>"
response.write "</td>"
response.write "</table>"
end sub
%>
</body></html>

Trillithium · Apr 4, 2001

Thanx 4 the reactions. But i allready solved my question. But again, thanx..

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Scrollable querys

Trillithium

Technical User

smbure

Programmer

phuctran

Programmer

Trillithium

Technical User

Similar threads

Part and Inventory Search

Sponsor