|
 |
asp_databases thread: recordcount
Message #1 by "Frode Strømme" <fstroemm@o...> on Sat, 26 May 2001 04:17:34
|
|
I've got a working moviebase search script, and now I want to display only
20 at one page. If over 20 then a page 2 is created.
Something like...
If Not Rs.Eof Then
recordcount = Rs.recordcount
If Int(recordcount) > 20 then
'Apply paging
Rs.PageSize = 20 'Max Record to display
Rs.AbsolutePage = Currentpage 'Display this variable in the Hyperlink
'for page navigation
Else
'Show Page normally
I've tried implementing this without luck.
Anything else I need to set/know about?
Thanks.
- Frode.
My code:
<%@Language=VBScript%>
<%Response.Buffer=True%>
<%
Dim searchStr, MyConn, RS
searchStr = Replace(Request.Form("strSearch"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\datastores\khitdvd.mdb"
SQL="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller], [khitpris].[Pris] " & _
"FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
INNER JOIN khitpris ON [khitfilm].[FilmID] =[khitpris].[FilmID] " & _
" WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
& "%' ORDER BY " & Request("choice")
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center> No Records found.</center>"
Else
Response.Write "<table align=""center"" valign=""top""
border=""1"">"
While Not RS.EOF
Response.Write "<tr><td>"
Response.Write "<a href='khitdisplay.asp?dispID=" & _
Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
& "</a>"
Response.Write "</td></tr>"
Response.Write "<tr><td>"
Response.Write RS("Pris")
Response.Write "</td></tr>"
RS.MoveNext
WEND
Response.Write "</table>"
End If
Else
Response.Write "<center>Enter a string first.</center>"
End IF
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
Message #2 by "Frode Strømme" <fstroemm@o...> on Sat, 26 May 2001 14:51:24
|
|
I have another problem with this code too..
If a movie have two actors instead of one, it shows the movielink twice.
How can I avoid it showing up twice on search?
Thanks.
- Frode.
> My code:
>
> <%@Language=VBScript%>
> <%Response.Buffer=True%>
> <%
> Dim searchStr, MyConn, RS
> searchStr = Replace(Request.Form("strSearch"), "'", "''")
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
>
> MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
> DBQ=C:\datastores\khitdvd.mdb"
>
>
> SQL="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
> [Skuespiller], [khitpris].[Pris] " & _
> "FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
> [SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
> INNER JOIN khitpris ON [khitfilm].[FilmID] =[khitpris].[FilmID] " & _
> " WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
> & "%' ORDER BY " & Request("choice")
>
>
> Set RS = MyConn.Execute(SQL)
>
> If searchStr <> "" Then
> If RS.BOF AND RS.EOF Then
> Response.Write "<center> No Records found.</center>"
> Else
>
> Response.Write "<table align=""center"" valign=""top""
> border=""1"">"
>
> While Not RS.EOF
> Response.Write "<tr><td>"
> Response.Write "<a href='khitdisplay.asp?dispID=" & _
> Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
> & "</a>"
> Response.Write "</td></tr>"
> Response.Write "<tr><td>"
> Response.Write RS("Pris")
> Response.Write "</td></tr>"
> RS.MoveNext
> WEND
>
> Response.Write "</table>"
> End If
>
> Else
> Response.Write "<center>Enter a string first.</center>"
>
> End IF
>
> RS.Close
> MyConn.Close
> Set RS = Nothing
> Set MyConn = Nothing
Message #3 by "Ken Schaefer" <ken@a...> on Sun, 27 May 2001 13:58:19 +1000
|
|
www.adOpenStatic.com/experiments/recordsetpaging.asp
shows the fastest way to do this. Sorry, but the principle behind recordset
paging is not explaining. I'm doing a faq on this. Otherwise, you can head
over to www.learnasp.com and find the page there on adOpenStatic recordset
paging.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Frode StrXmme" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, May 26, 2001 4:17 AM
Subject: [asp_databases] recordcount
: I've got a working moviebase search script, and now I want to display only
: 20 at one page. If over 20 then a page 2 is created.
:
: Something like...
:
: If Not Rs.Eof Then
: recordcount = Rs.recordcount
:
: If Int(recordcount) > 20 then
: 'Apply paging
: Rs.PageSize = 20 'Max Record to display
: Rs.AbsolutePage = Currentpage 'Display this variable in the Hyperlink
: 'for page navigation
: Else
: 'Show Page normally
:
:
: I've tried implementing this without luck.
: Anything else I need to set/know about?
:
: Thanks.
:
: - Frode.
:
Message #4 by "Ken Schaefer" <ken@a...> on Sun, 27 May 2001 13:58:45 +1000
|
|
SELECT DISTINCT
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Frode StrXmme" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, May 26, 2001 2:51 PM
Subject: [asp_databases] Re: recordcount
: I have another problem with this code too..
: If a movie have two actors instead of one, it shows the movielink twice.
: How can I avoid it showing up twice on search?
:
: Thanks.
Message #5 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Sat, 26 May 2001 18:59:44 +0200
|
|
Hi!
You can use the DISTINCT directive in your SQL statement for the movie
links.
Example: ="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel],
[khitskue].
[Skuespiller], [khitpris].[Pris] " & _
"FROM (khitfilm INNER JOIN (khitskue INNER etc etc...
Hth,
Hakan
-----Original Message-----
From: Frode Strømme [mailto:fstroemm@o...]
Sent: den 26 maj 2001 14:51
To: ASP Databases
Subject: [asp_databases] Re: recordcount
I have another problem with this code too..
If a movie have two actors instead of one, it shows the movielink twice.
How can I avoid it showing up twice on search?
Thanks.
- Frode.
> My code:
>
> <%@Language=VBScript%>
> <%Response.Buffer=True%>
> <%
> Dim searchStr, MyConn, RS
> searchStr = Replace(Request.Form("strSearch"), "'", "''")
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
>
> MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
> DBQ=C:\datastores\khitdvd.mdb"
>
>
> SQL="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
> [Skuespiller], [khitpris].[Pris] " & _
> "FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
> [SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
> INNER JOIN khitpris ON [khitfilm].[FilmID] =[khitpris].[FilmID] " & _
> " WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
> & "%' ORDER BY " & Request("choice")
>
>
> Set RS = MyConn.Execute(SQL)
>
> If searchStr <> "" Then
> If RS.BOF AND RS.EOF Then
> Response.Write "<center> No Records found.</center>"
> Else
>
> Response.Write "<table align=""center"" valign=""top""
> border=""1"">"
>
> While Not RS.EOF
> Response.Write "<tr><td>"
> Response.Write "<a href='khitdisplay.asp?dispID=" & _
> Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
> & "</a>"
> Response.Write "</td></tr>"
> Response.Write "<tr><td>"
> Response.Write RS("Pris")
> Response.Write "</td></tr>"
> RS.MoveNext
> WEND
>
> Response.Write "</table>"
> End If
>
> Else
> Response.Write "<center>Enter a string first.</center>"
>
> End IF
>
> RS.Close
> MyConn.Close
> Set RS = Nothing
> Set MyConn = Nothing
Message #6 by "Frode Strømme" <fstroemm@o...> on Sun, 27 May 2001 23:18:23
|
|
Thanks, but DISTINCT didn't have any effect on my query. Seems like I
still get a duplicate match for each actor (skuespiller) in my movie
database search.
Possible that I need to somehow restructure my database?
SQL="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller], [khitpris].[Pris] " & _
"FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
INNER JOIN khitpris ON [khitfilm].[FilmID] = [khitpris].[FilmID] " & _
" WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
& "%' ORDER BY " & Request("choice")
- Frode.
> Hi!
>
> You can use the DISTINCT directive in your SQL statement for the movie
> links.
> Example: ="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel],
> [khitskue].
> [Skuespiller], [khitpris].[Pris] " & _
> "FROM (khitfilm INNER JOIN (khitskue INNER etc etc...
>
>
> Hth,
>
>
> Hakan
Message #7 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Mon, 28 May 2001 09:31:59 +0200
|
|
Hi!
Yes, I noticed that you have three tables in your first SELECT
DISTINCT(khitfilm, khitskue and khitpris) and that is why your DISTINCT
won´t return unique values from your khitfilm. Instead, it will show the
value of a movie for each and every actor in it and that is not what you
want. You might want to look over your normalization and your query design
from the beginning.
To give you a clue (a sample from the Northwind database)
This query won´t return unique values:
SELECT DISTINCT Suppliers.SupplierID, Suppliers.CompanyName,
Products.ProductName
FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID
Products.SupplierID;
This one will:
SELECT DISTINCT Suppliers.SupplierID, Suppliers.CompanyName
FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID
Products.SupplierID;
If you include fields from tables with a one-to-many relationship it will
return every row from the many-table that meets the conditions. That is how
a RDBMS work. Maybe someone out there knows another way and can correct me
on this. I hope I am not totally wrong here....or else I will make myself
invisible here for a long time and read on...;-)
Hakan
-----Original Message-----
From: Frode Strømme [mailto:fstroemm@o...]
Sent: den 27 maj 2001 23:18
To: ASP Databases
Subject: [asp_databases] Re: recordcount
Thanks, but DISTINCT didn't have any effect on my query. Seems like I
still get a duplicate match for each actor (skuespiller) in my movie
database search.
Possible that I need to somehow restructure my database?
SQL="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller], [khitpris].[Pris] " & _
"FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
INNER JOIN khitpris ON [khitfilm].[FilmID] = [khitpris].[FilmID] " & _
" WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
& "%' ORDER BY " & Request("choice")
- Frode.
> Hi!
>
> You can use the DISTINCT directive in your SQL statement for the movie
> links.
> Example: ="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel],
> [khitskue].
> [Skuespiller], [khitpris].[Pris] " & _
> "FROM (khitfilm INNER JOIN (khitskue INNER etc etc...
>
>
> Hth,
>
>
> Hakan
Message #8 by "Frode Strømme" <fstroemm@o...> on Mon, 28 May 2001 17:04:17
|
|
Hi again.
Too bad I'm not good at database normalization :)
I got something like this:
TableActor
----------
pkey ActID
Actors
TableMovies
-----------
pkey MovID
Movies
TableMovAct
-----------
fkey MovID
fkey ActID
Then do:
SELECT TableMovies.MovID, TableMovies.Movies, TableActor.Actors FROM ...
This will give duplicate matches even with using DISTINCT.
How could I set up the database so I don't get a movie match for each
actor found?
Thanks.
- Frode.
> Hi!
>
> Yes, I noticed that you have three tables in your first SELECT
> DISTINCT(khitfilm, khitskue and khitpris) and that is why your DISTINCT
> won´t return unique values from your khitfilm. Instead, it will show the
> value of a movie for each and every actor in it and that is not what you
> want. You might want to look over your normalization and your query
design
> from the beginning.
>
> -----Original Message-----
> SQL="SELECT DISTINCT [khitfilm].[FilmID], [khitfilm].[Tittel],
[khitskue].
> [Skuespiller], [khitpris].[Pris] " & _
> "FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
> [SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
> INNER JOIN khitpris ON [khitfilm].[FilmID] = [khitpris].[FilmID] " & _
> " WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
> & "%' ORDER BY " & Request("choice")
>
Message #9 by "Frode Strømme" <fstroemm@o...> on Mon, 28 May 2001 23:46:05
|
|
To avoid a movielink show twice when I have more than one actor, would it
be possible to make a variable and add the match to that. Then check if it
exists on next record?
I though perhaps something like this:
Although this one don't work, So I guess my varTmp = Request("FilmID")
don't make much sense.
While Not RS.EOF
Response.Write "<tr bgcolor=#f3f3dc><td class=""displnk"">"
Response.Write "<a class =""displnk"" href='khitdisplay.asp?
dispID=" & _
Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
& "</a>"
Response.Write "</td></tr>"
varTmp = Request("FilmID")
RS.MoveNext
If Not RS.EOF Then
DO While varTmp = Request("FilmID")
RS.MoveNext
loop
End If
WEND
> -----Original Message-----
> From: Frode Strømme [mailto:fstroemm@o...]
> Sent: den 26 maj 2001 14:51
> To: ASP Databases
> Subject: [asp_databases] Re: recordcount
>
> I have another problem with this code too..
> If a movie have two actors instead of one, it shows the movielink twice.
> How can I avoid it showing up twice on search?
>
> Thanks.
>
> - Frode.
>
> > My code:
> >
> > <%@Language=VBScript%>
> > <%Response.Buffer=True%>
> > <%
> > Dim searchStr, MyConn, RS
> > searchStr = Replace(Request.Form("strSearch"), "'", "''")
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> >
> > MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
> > DBQ=C:\datastores\khitdvd.mdb"
> >
> >
> > SQL="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
> > [Skuespiller], [khitpris].[Pris] " & _
> > "FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
[khitskue].
> > [SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
> > INNER JOIN khitpris ON [khitfilm].[FilmID] =[khitpris].[FilmID] " & _
> > " WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
> > & "%' ORDER BY " & Request("choice")
> >
> >
> > Set RS = MyConn.Execute(SQL)
> >
> > If searchStr <> "" Then
> > If RS.BOF AND RS.EOF Then
> > Response.Write "<center> No Records found.</center>"
> > Else
> >
> > Response.Write "<table align=""center"" valign=""top""
> > border=""1"">"
> >
> > While Not RS.EOF
> > Response.Write "<tr><td>"
> > Response.Write "<a href='khitdisplay.asp?dispID=" & _
> > Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
> > & "</a>"
> > Response.Write "</td></tr>"
> > Response.Write "<tr><td>"
> > Response.Write RS("Pris")
> > Response.Write "</td></tr>"
> > RS.MoveNext
> > WEND
> >
> > Response.Write "</table>"
> > End If
> >
> > Else
> > Response.Write "<center>Enter a string first.</center>"
> >
> > End IF
> >
> > RS.Close
> > MyConn.Close
> > Set RS = Nothing
> > Set MyConn = Nothing
>
Message #10 by =?iso-8859-1?Q?H=E5kan_Frennesson?= <hakan@c...> on Tue, 29 May 2001 11:10:12 +0200
|
|
Hi!
Well, it depends on what you want to see. If you want to see information
from the Movie table, it will never give you distinct values. How about this
one:
SELECT DISTINCT TableMovAct.ActID, TableActor.somefield FROM TableActor
INNER JOIN TableMovAct ON TableActor.ActID= TableMovAct.ActID:
Will this help you?
Hakan
-----Original Message-----
From: Frode Strømme [mailto:fstroemm@o...]
Sent: den 28 maj 2001 17:04
To: ASP Databases
Subject: [asp_databases] Re: recordcount
Hi again.
Too bad I'm not good at database normalization :)
I got something like this:
TableActor
----------
pkey ActID
Actors
TableMovies
-----------
pkey MovID
Movies
TableMovAct
-----------
fkey MovID
fkey ActID
Then do:
SELECT TableMovies.MovID, TableMovies.Movies, TableActor.Actors FROM ...
This will give duplicate matches even with using DISTINCT.
How could I set up the database so I don't get a movie match for each
actor found?
Thanks.
- Frode.
> Hi!
>
> Yes, I noticed that you have three tables in your first SELECT
> DISTINCT(khitfilm, khitskue and khitpris) and that is why your DISTINCT
> won´t return unique values from your khitfilm. Instead, it will show the
> value of a movie for each and every actor in it and that is not what you
> want. You might want to look over your normalization and your query
design
> from the beginning.
Message #11 by "Drew, Ron" <RDrew@B...> on Tue, 29 May 2001 17:30:29 -0400
|
|
Long answer but it works for me....
<!--<%@ 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=myserver;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>
-----Original Message-----
From: Frode Strxmme [mailto:fstroemm@o...]
Sent: Saturday, May 26, 2001 12:18 AM
To: ASP Databases
Subject: [asp_databases] recordcount
I've got a working moviebase search script, and now I want to display only
20 at one page. If over 20 then a page 2 is created.
Something like...
If Not Rs.Eof Then
recordcount = Rs.recordcount
If Int(recordcount) > 20 then
'Apply paging
Rs.PageSize = 20 'Max Record to display
Rs.AbsolutePage = Currentpage 'Display this variable in the Hyperlink
'for page navigation
Else
'Show Page normally
I've tried implementing this without luck.
Anything else I need to set/know about?
Thanks.
- Frode.
My code:
<%@Language=VBScript%>
<%Response.Buffer=True%>
<%
Dim searchStr, MyConn, RS
searchStr = Replace(Request.Form("strSearch"), "'", "''")
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\datastores\khitdvd.mdb"
SQL="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller], [khitpris].[Pris] " & _
"FROM (khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON [khitskue].
[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].[FilmID])
INNER JOIN khitpris ON [khitfilm].[FilmID] =[khitpris].[FilmID] " & _
" WHERE " & Request("choice") & " LIKE '%" & Request("strSearch")
& "%' ORDER BY " & Request("choice")
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center> No Records found.</center>"
Else
Response.Write "<table align=""center"" valign=""top""
border=""1"">"
While Not RS.EOF
Response.Write "<tr><td>"
Response.Write "<a href='khitdisplay.asp?dispID=" & _
Server.URLEncode(RS("FilmID")) & "'>" & Server.HTMLEncode(RS("Tittel"))
& "</a>"
Response.Write "</td></tr>"
Response.Write "<tr><td>"
Response.Write RS("Pris")
Response.Write "</td></tr>"
RS.MoveNext
WEND
Response.Write "</table>"
End If
Else
Response.Write "<center>Enter a string first.</center>"
End IF
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
|
|
|
View Cart
