|
 |
asp_web_howto thread: Max RecordSet
Message #1 by "Sertial, Sam" <2495XX2@a...> on Fri, 16 Feb 2001 09:44:24 -0500
|
|
I want to display 50 records on a time using Move Next and Move Previous. I
got this code from Wrox begining ASP Database, but seem is not working. Any
ideas please:
maxrecord = 50
x = 0
while (NOT rs.EOF) AND (x < maxrecords)
%>
<tr>
<td width="50%"> <font face="Arial" size="2"><a
href="<%=geturl(Session("webmaster"), rs
("email").value)%>"><%=getname(rs("lname").value,rs("fname").value,
rs("handle").value, rs
("usehandle").value)%></a></font> </td>
<td width="50%"> <font face="Arial" size="2"><a
href="mailto:<%=rs("email")%>"><%=rs("email")%> </a></font>
</td>
</tr>
<%
x = x + 1
email = rs("email").value
rs.MoveNext
wend
%>
</table>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
if x = maxrecords then
%>
<form method="POST" action="searchprofiles.asp">
<input type="hidden" name="city"
value="<%=city%>"><input type="hidden" name="country" value="<%=country%>">
<input type="hidden" name="email"
value="<%=email%>"><input type="hidden" name="lname" value="<%=lname%>">
<input type="hidden" name="state"
value="<%=state%>"><p>
<input type="submit" value="Next <%=maxrecords%>
Matching Records" name="B1"></p>
</form>
<% else
%>
<p align="center"><font face="Arial" size="2">End of
List</font></p>
<p>
<% end if
end if
end if
%>
Message #2 by Vince Kavanagh <vince@6...> on Fri, 16 Feb 2001 15:05:32 +0000
|
|
Shouldn't maxrecord = 50 be maxrecords = 50
Best Regards,
Vince.
"Sertial, Sam" wrote:
> I want to display 50 records on a time using Move Next and Move Previous. I
> got this code from Wrox begining ASP Database, but seem is not working. Any
> ideas please:
> maxrecord = 50
> x = 0
> while (NOT rs.EOF) AND (x < maxrecords)
> %>
> <tr>
> <td width="50%"> <font face="Arial" size="2"><a
> href="<%=geturl(Session("webmaster"), rs
> ("email").value)%>"><%=getname(rs("lname").value,rs("fname").value,
> rs("handle").value, rs
> ("usehandle").value)%></a></font> </td>
> <td width="50%"> <font face="Arial" size="2"><a
> href="mailto:<%=rs("email")%>"><%=rs("email")%> </a></font>
> </td>
> </tr>
> <%
> x = x + 1
>
> email = rs("email").value
> rs.MoveNext
> wend
> %>
> </table>
> <%
> rs.Close
> cn.Close
> Set rs = Nothing
> Set cn = Nothing
> if x = maxrecords then
> %>
>
> <form method="POST" action="searchprofiles.asp">
> <input type="hidden" name="city"
> value="<%=city%>"><input type="hidden" name="country" value="<%=country%>">
> <input type="hidden" name="email"
> value="<%=email%>"><input type="hidden" name="lname" value="<%=lname%>">
> <input type="hidden" name="state"
> value="<%=state%>"><p>
> <input type="submit" value="Next <%=maxrecords%>
> Matching Records" name="B1"></p>
> </form>
> <% else
> %>
>
> <p align="center"><font face="Arial" size="2">End of
> List</font></p>
> <p>
> <% end if
> end if
> end if
> %>
>
Message #3 by "Morgan, Rob" <Rob.Morgan@o...> on Fri, 16 Feb 2001 10:15:53 -0500
|
|
Try something like this. It's part of a page I use, so I hope you can
figure it out.
sql = "Select email_address_key, email_address,
public_listed_flag, "
sql = sql &
"internal_listed_flag,fname,lname,mname,email_address_cd_descr "
sql = sql & "from email_address a, email_address_cd b, name
c "
sql = sql & "where a.email_address_cd_key
b.email_address_cd_key "
sql = sql & "and a.person_key = c.person_key "
sql = sql & "and c.current_flag = 'Y' "
if query <> "" then
sql = sql & "and (upper(c.LNAME) like '%" & ucase(query) &
"%' or upper(a.email_address)like '%" & ucase(query) & "%') "
end if
sql = sql & "order by c.LNAME, c.FNAME"
Set rs = Server.CreateObject("ADODB.RecordSet")
With rs
.source = sql
.ActiveConnection = Con
.CursorType = 0
.Cursorlocation = 3
.PageSize = 20
.open
End With
if page = "" then
intPage = 1
else
intPage = Cint(page)
If intPage < 1 then
IntPage = 1
else
If intPage > rs.PageCount then
intPage = rs.PageCount
end if
end if
end if
if rs.eof = false then
rs.AbsolutePage = intPage
end if
<td width="100%" bgcolor="<%=HEADER_CELL_COLOR%>" height="1"
nowrap><align="left"><a
href="<%=Request.ServerVariables("URL")%>?action=search&query=<%=query%>&pag
e=1"><img border="0" src="images/icons/icon_vcr_first.gif" alt="First
Page"></a>
<%if IntPage = 1 then%><img border="0"
src="images/icons/icon_vcr_previous_off.gif"><%else%><a
href="<%=Request.ServerVariables("URL")%>?action=search&query=<%=query%>&pag
e=<%=intPage - 1%>"><img border="0" src="images/icons/icon_vcr_previous.gif"
alt="Previous Page"></a><%end
if%> Page <%=IntPage%> of <%=rs.PageCount%>
<%If intPage = rs.PageCount Then%> <img border="0"
src="images/icons/icon_vcr_next_off.gif"><%else%> <a
href="<%=Request.ServerVariables("URL")%>?action=search&query=<%=query%>&pag
e=<%=intPage + 1%>"><img border="0" src="images/icons/icon_vcr_next.gif"
alt="Next Page"></a><%end if%> <a
href="<%=Request.ServerVariables("URL")%>?action=search&query=<%=query%>&pag
e=<%=rs.PageCount%>"><img border="0" src="images/icons/icon_vcr_last.gif"
alt="Last Page"></a>
</td>
<%if rs.eof = false then%>
<table border="0" width="100%" cellspacing="1" cellpadding="2"
bgcolor="<%=TABLE_BG_COLOR%>" height="1">
<tr>
<td bgcolor="<%=HEADER_CELL_COLOR%>">Action</td>
<td align="center" bgcolor="<%=HEADER_CELL_COLOR%>">Name</td>
<td align="center" bgcolor="<%=HEADER_CELL_COLOR%>">Type</td>
<td align="center" bgcolor="<%=HEADER_CELL_COLOR%>">Email</td>
<td align="center" bgcolor="<%=HEADER_CELL_COLOR%>">Public</td>
<td align="center" bgcolor="<%=HEADER_CELL_COLOR%>">Internal</td>
</tr>
<%do while rs.eof = false%>
<tr>
<td bgcolor="<%=FORM_CELL_COLOR%>"> <a
href="<%=Request.ServerVariables("URL")%>?action=delete&key=<%=rs("email_add
ress_KEY")%>"><img border="0" src="images/icons/icon_delete.gif"
alt="Delete"></a> <a
href="<%=Request.ServerVariables("URL")%>?action=edit&key=<%=rs("email_addre
ss_KEY")%>"><img border="0" src="images/icons/icon_edit.gif"
alt="Edit"></a></td>
<td
bgcolor="<%=FORM_CELL_COLOR%>"><%=rs("lname")%>, <%=rs("fname")%>
<%=rs("mname")%></td>
<td
bgcolor="<%=FORM_CELL_COLOR%>"> <%=rs("email_address_cd_descr")%></td>
<td
bgcolor="<%=FORM_CELL_COLOR%>"> <%=rs("email_address")%></td>
<td
bgcolor="<%=FORM_CELL_COLOR%>"> <%=rs("public_listed_flag")%></td>
<td
bgcolor="<%=FORM_CELL_COLOR%>"> <%=rs("Internal_listed_flag")%></td>
</tr>
<%rs.movenext
loop%>
</table>
<%else%>
<p align="center">
No records found
<%End if%>
-----Original Message-----
From: Sertial, Sam [mailto:2495XX2@a...]
Sent: Friday, February 16, 2001 9:44 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Max RecordSet
I want to display 50 records on a time using Move Next and Move Previous. I
got this code from Wrox begining ASP Database, but seem is not working. Any
ideas please:
maxrecord = 50
x = 0
while (NOT rs.EOF) AND (x < maxrecords)
%>
<tr>
<td width="50%"> <font face="Arial" size="2"><a
href="<%=geturl(Session("webmaster"), rs
("email").value)%>"><%=getname(rs("lname").value,rs("fname").value,
rs("handle").value, rs
("usehandle").value)%></a></font> </td>
<td width="50%"> <font face="Arial" size="2"><a
href="mailto:<%=rs("email")%>"><%=rs("email")%> </a></font>
</td>
</tr>
<%
x = x + 1
email = rs("email").value
rs.MoveNext
wend
%>
</table>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
if x = maxrecords then
%>
<form method="POST" action="searchprofiles.asp">
<input type="hidden" name="city"
value="<%=city%>"><input type="hidden" name="country" value="<%=country%>">
<input type="hidden" name="email"
value="<%=email%>"><input type="hidden" name="lname" value="<%=lname%>">
<input type="hidden" name="state"
value="<%=state%>"><p>
<input type="submit" value="Next <%=maxrecords%>
Matching Records" name="B1"></p>
</form>
<% else
%>
<p align="center"><font face="Arial" size="2">End of
List</font></p>
<p>
<% end if
end if
end if
%>
Message #4 by "Drew, Ron" <RDrew@B...> on Tue, 20 Feb 2001 09:13:37 -0500
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C09B47.511C8220
Content-Type: text/plain;
charset="iso-8859-1"
Kind of long, but this works for me. Using a html to start by having the
user fill in what they want to view. You can change this for your app but
the procedure works. Change the 8 to 50 in the GetData function.
Ron
................................start.......................................
...
<!--<%@ Language=VBScript %>-->
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\ado\msado15.dll" -->
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'creates and returns a connection
function CreateConnection()
on error resume next
dim cnDB
set cnDB = Server.CreateObject("ADODB.Connection")
sQryConn
"Provider=SQLOLEDB;SERVER=BDFINTRA;UID=sa;DATABASE=sales;User
Id=sa;PASSWORD=;"
cnDB.CursorLocation =adUseClient
cnDB.Open (sQryConn )
if Err <> 0 then
set cnDB = nothing
Response.Write("ERROR CONNECTING TO DATABASE, PLEASE LOG OUT
AND TRY AGAIN")
else
set CreateConnection = cnDB
end if
end function
%>
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Gets the Parameters from Form
function GetParams()
Dim strvendor, strmonth, stryear, strinvoice, strindate, strinpo,
strinamount, strindesc, strinacct
strvendor = Request.Form("vendor")
strmonth = Request.Form("month")
stryear = Request.Form("year")
strinvoice = Request.Form("invoice")
strindate = Request.Form("indate")
strinpo = Request.Form("inpo")
strinamount = Request.Form("inamount")
strindesc = Request.Form("indesc")
strinacct = Request.Form("inacct")
Response.Write "<b>Parameters: </b>"
Response.Write "<b>Vendor> </b>"
Response.Write strvendor
Response.Write "<b>Month> </b>"
Response.Write strmonth
Response.Write "<b>Year> </b>"
Response.Write stryear
Response.Write "<b>Invoice> </b>"
Response.Write strinvoice
Response.Write "<b>Date> </b>"
Response.Write strindate
Response.Write "<b>PO> </b>"
Response.Write strinpo
Response.Write "<b>Amount> </b>"
Response.Write strinamount
Response.Write "<b>Desc> </b>"
Response.Write strindesc
Response.Write "<b>Account> </b>"
Response.Write strinacct
end function
%>
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Gets the Data using the Parameters
function
GetData(PageNum,strvendor,strmonth,stryear,strinvoice,strindate,strinpo,stri
namount,strindesc,strinacct)
strvendor = Request.Form("vendor")
strmonth = Request.Form("month")
stryear = Request.Form("year")
strinvoice = Request.Form("invoice")
strindate = Request.Form("indate")
strinpo = Request.Form("inpo")
strinamount = Request.Form("inamount")
strindesc = Request.Form("indesc")
strinacct = Request.Form("inacct")
dim myData, myRS, myAdoConnection, testcnt, hdg
dim pagesize,PageCount
Set myRS=server.CreateObject("ADODB.Recordset")
' pagesize is the number of table lines you want to see
pagesize=8
if PageNum<1 then
PageNum=1
end if
Select Case strvendor
Case 1
hdg = "Complete Computer Products"
Case 2
hdg = "Executive Development Partnership"
Case 3
hdg = "Maintenance and Repair"
Case 4
hdg = "Consulting"
end Select
'database lookup
set myAdoConnection = CreateConnection()
sQuery = "select * from misinvoices "
sQuery = sQuery & "where inid = " & strvendor
if not strinacct = "" then
sQuery = sQuery & " AND inacct = " & strinacct
end if
if not strindesc = "" then
sQuery = sQuery & " AND indesc LIKE " & "'%" & strindesc & "%'"
end if
if not strinamount = "" then
sQuery = sQuery & " AND inamount = " & strinamount
end if
if not strinvoice = "" then
sQuery = sQuery & " AND invoice > " & "'" & strinvoice & "'"
end if
if not strindate = "" then
sQuery = sQuery & " AND indate > " & strindate
end if
if not strinpo = "" then
sQuery = sQuery & " AND inpo = " & strinpo
end if
if not strmonth = "" then
sQuery = sQuery & " AND inmonth = " & strmonth
end if
if not stryear = "" then
sQuery = sQuery & " AND inyear = " & stryear
end if
' For debugging uncomment the next 2 lines
' Response.Write sQuery
' exit function
myRS.Open sQuery, myAdoConnection , adOpenStatic,adLockReadOnly
if myRS.EOF or myRS.BOF then
GetData ="<b><br>... No Data ...</b><br>"
' GetData = GetData & sQuery
exit function
end if
myRS.MoveLast
if myRS.RecordCount <0 then
GetData ="No Data"
GetData=GetData & sQuery
exit function
else
myRS.MoveFirst
end if
myData = "<center><font size=6><b>" & hdg &
"</b></font></center><br>"
myData = myData & "<table border='1' width='100%'
bgcolor='#FFFF9C'>" 'start table
myData = myData & "<tr bgcolor='#C6EFF7'>"
myData = myData & "<td width='5%'>" & "Invoice" 'row 2 Heading
myData = myData & "<td width='5%'>" & "Date"
myData = myData & "<td width='5%'>" & "PO"
myData = myData & "<td width='15%'>" & "Amount"
myData = myData & "<td width='50%'> " & "Description"
myData = myData & "<td width='5%'> " & "Month"
myData = myData & "<td width='10%'> " & "Account"
myData = myData & "<td width='5%'> " & "Year"
myData = myData & "</tr>"
myRS.PageSize=pagesize
myRS.AbsolutePage=PageNum
for i=1 to pagesize
if Not myRS.EOF then
myData = myData & "<tr>"
myData = myData & "<td width='5%'>" &
myRS("invoice") & "<br>" & "</td>" 'row 1
myData = myData & "<td width='5%'>" & myRS("indate")
& "<br>" & "</td>"
myData = myData & "<td width='5%'>" & myRS("inpo") &
"<br>" & "</td>"
myData = myData & "<td width='15%'>" &
myRS("inamount") & "<br>" & "</td>"
myData = myData & "<td width='50%'> " &
myRS("indesc") & "<br>" &"</td>"
myData = myData & "<td width='5%'> " &
myRS("inmonth") & "<br>" &"</td>"
myData = myData & "<td width='10%'> " &
myRS("inacct") & "<br>" &"</td>"
myData = myData & "<td width='5%'> " &
myRS("inyear") & "<br>" &"</td>"
myData = myData & "</tr>"
myRS.MoveNext
end if
next
pagecount=myRS.PageCount
myData = myData & "</table>"
myData = myData & "<BR>Page " & PageNum & " of " &
myRS.PageCount
myRS.Close
set myRS=nothing
GetData = myData
end function
%>
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Assigns the Next and Previous Pages
dim curpage,nextpage
curpage=Request.QueryString("page")
if curpage="" then
curpage=1
end if
nextpage=Request("submit")
if nextpage="Previous" then
curpage=session("lastpage")-1
end if
if nextpage="Next" then
curpage=session("lastpage")+1
end if
session("lastpage")=curpage
%>
<html>
<head>
<title>MIS Invoice Viewer w/Filter</title>
</head>
<body bgcolor="#00ffff" text="#000000" link="#0000ff" vlink="#ff00ff">
<!--<H><strong><center>MIS Invoice Viewer</center></strong></H> <br>-->
<form name="dataviewer" action="viewinv.asp" method="POST">
<%
Response.Write GetParams()
Response.Write "<BR>" &
GetData(curpage,strvendor,strmonth,stryear,strinvoice,strindate,strinpo,stri
namount,strindesc,strinacct)
Response.Write "<br><br><input type='submit' name='submit'
value='Previous'>"
Response.Write "<input type='submit' name='submit' value='Next'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='vendor' value='" & strvendor
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='invoice' value='" & strinvoice
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='indate' value='" & strindate
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='inpo' value='" & strinpo &"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='inamount' value='" &
strinamount &"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='inacct' value='" & strinacct
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='indesc' value='" & strindesc
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='month' value='" & strmonth
&"'>"
Response.Write "<INPUT TYPE='HIDDEN' Name='year' value='" & stryear &"'>"
Response.Write "<p align=left><b><a href='invoicemenu.htm'>Go Back to
Invoice Main Menu</a><b></p>"
%>
</form>
</body>
</html>
.......................................end..................................
.............
-----Original Message-----
From: Sertial, Sam [mailto:2495XX2@a...]
Sent: Friday, February 16, 2001 9:44 AM
To: ASP Web HowTo
Subject: [asp_web_howto] Max RecordSet
I want to display 50 records on a time using Move Next and Move Previous. I
got this code from Wrox begining ASP Database, but seem is not working. Any
ideas please:
maxrecord = 50
x = 0
while (NOT rs.EOF) AND (x < maxrecords)
%>
<tr>
<td width="50%"> <font face="Arial" size="2"><a
href="<%=geturl(Session("webmaster"), rs
("email").value)%>"><%=getname(rs("lname").value,rs("fname").value,
rs("handle").value, rs
("usehandle").value)%></a></font> </td>
<td width="50%"> <font face="Arial" size="2"><a
href="mailto:<%=rs("email")%>"><%=rs("email")%> </a></font>
</td>
</tr>
<%
x = x + 1
email = rs("email").value
rs.MoveNext
wend
%>
</table>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
if x = maxrecords then
%>
<form method="POST" action="searchprofiles.asp">
<input type="hidden" name="city"
value="<%=city%>"><input type="hidden" name="country" value="<%=country%>">
<input type="hidden" name="email"
value="<%=email%>"><input type="hidden" name="lname" value="<%=lname%>">
<input type="hidden" name="state"
value="<%=state%>"><p>
<input type="submit" value="Next <%=maxrecords%>
Matching Records" name="B1"></p>
</form>
<% else
%>
<p align="center"><font face="Arial" size="2">End of
List</font></p>
<p>
<% end if
end if
end if
%>
$subst('Email.Unsub')
|
|
|
View Cart
