[ASP and MySQL] Paging

Hi everyone.

I have problem whit paging ASP and MySQL.

This code not working :

PageSize = 10
Number of records = 1
PageCount = 1/10 = 1

Code: ( text )

Function excess(argValue)
if not (int(argValue) = argValue) then argValue = int(argValue)+1
excess = argValue
end Function
pageSize = 10
if(len(Request.QueryString("pagina"))=0)then
currentPage = 1
else
currentPage = CInt(Request.QueryString("pagina"))
end if
sql_count = " SELECT * "
sql_count = sql_count & " , COUNT(*) AS CNT "
sql_count = sql_count & " , COUNT(TIPO) AS totale_tipo "
sql_count = sql_count & " FROM "
sql_count = sql_count & " tbl "
sql_count = sql_count & " GROUP BY "
sql_count = sql_count & " TIPO, DATA "
sql_count = sql_count & " ORDER BY "
sql_count = sql_count & " DATA ASC "
sql_count = sql_count & " LIMIT " & (currentPage - 1) * pageSize & ", " & pageSize
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open sql_count, cn
If Not RS.eof Then
recordCount = RS("CNT")
pageCount = excess(clng(recordCount) / clng(pageSize))
for i = 1 to pageCount
Response.Write("<a href=""pag.asp?pagina=" & i & """>" & i & "</a> ")
next
end if
RS.Close
Set RS = Nothing
cn.Close
Set cn = Nothing

Was going on ?

Can you help me?

viki1967

Familiar Sight

164 Posts

March 25th, 2008
08:12 PM
#2

Re: [ASP and MySQL] Paging

This code work:

Code: ( text )

<%
Session.LCID = 1040
response.expires = -1500
response.AddHeader "PRAGMA", "NO-CACHE"
response.CacheControl = "PRIVATE"
'STRINGA DI CONNESSIONE AL DB MYSQL
sDatabaseConnection = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& "DATABASE=test;"_
& "UID=root;PWD=test; OPTION=35;"
'CREO LA CONNESSIONE AL DB MYSQL
Set connCount = Server.Createobject("ADODB.Connection")
'APRO LA CONNESSIONE AL DB MYSQL
connCount.open sDatabaseConnection
Function excess(argValue)
if not (int(argValue) = argValue) then argValue = int(argValue)+1
excess = argValue
end Function
pageSize = 5
if(len(Request.QueryString("pagina"))=0)then
currentPage = 1
else
currentPage = CInt(Request.QueryString("pagina"))
end if
q1 = "SELECT COUNT(*) FROM tbl "
q2 = "SELECT * FROM tbl ORDER BY DATA ASC LIMIT " & (currentPage - 1) * pageSize & ", " & pageSize
recordCount = connCount.Execute(q1).Fields(0)
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open q2, connCount
If Not RS.eof Then
Do while not RS.eof
response.write RS("Nome") &" - "& RS("data") &" - "& RS("stato") &"<br><br>"
RS.movenext()
Loop
pageCount = excess(clng(recordCount) / clng(pageSize))
for i = 1 to pageCount
Response.Write("<a href=""paging.asp?pagina=" & i & """>" & i & "</a> ")
next
end if
RS.close
set RS = nothing
connCount.close
set connCount = nothing
%>

I have:

Quote:

Originally Posted by

pippo - 11/03/2008 - in sede
caio - 11/03/2008 - operatore

pippo - 12/03/2008 - operatore
caio - 12/03/2008 - ferie

pippo - 13/03/2008 - ferie
caio - 13/03/2008 - ferie

sempronio - 14/03/2008 - permesso
caio - 14/03/2008 - in sede
pippo - 14/03/2008 - in sede

pippo - 15/03/2008 - permesso
ciao - 15/03/2008 - in sede
sempronio - 15/03/2008 - in sede

pippo - 22/03/2008 - in sede
caio - 22/03/2008 - malattia

I need another one option:

Quote:

Originally Posted by

11/03/2008 - in sede - 1 rows
11/03/2008 - operatore - 1 rows

12/03/2008 - operatore - 1 rows
12/03/2008 - ferie - 1 rows

13/03/2008 - ferie - 2 rows

14/03/2008 - in sede - 2 rows
14/03/2008 - permesso - 1 rows

15/03/2008 - permesso - 1 rows
15/03/2008 - in sede - 2 rows

22/03/2008 - in sede - 1 rows
22/03/2008 - malattia - 1 rows

I'm explain you ?

viki1967

Familiar Sight

164 Posts

March 26th, 2008
08:28 PM
#3

Re: [ASP and MySQL] Paging

Hi there.

This is the solution: ;)

Code: ( text )

baseQuery = " SELECT * "
baseQuery = baseQuery & " , COUNT(Tipo)"
baseQuery = baseQuery & " AS totale_Tipo "
baseQuery = baseQuery & " FROM "
baseQuery = baseQuery & " tbl "
baseQuery = baseQuery & " GROUP BY "
baseQuery = baseQuery & " Tipo,"
baseQuery = baseQuery & " Data "
q1 = " SELECT "
q1 = q1 & " COUNT(*) FROM "
q1 = q1 & " ( SELECT DISTINCT "
q1 = q1 & " Tipo, "
q1 = q1 & " Data "
q1 = q1 & " FROM "
q1 = q1 & " tbl ) "
q1 = q1 & " tbl "
q2 = baseQuery & " ORDER BY "
q2 = q2 & " DataOperazione ASC "
q1 = q1 & " LIMIT " & (currentPage - 1) * pageSize & ", "
q1 = q1 & " " & pageSize

Bye....