[ASP and MySQL] Paging

[viki1967]

Hi everyone.

I have problem whit paging ASP and MySQL.

This code not working :

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

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]

This code work:

<!-- INIZIO PARTE DI CODICE ASP --> 

<% 

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:

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:

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]

Hi there.

This is the solution: ;)

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....