Paginação Asp Para Banco Mysql

Nesh · Agosto 15, 2006

Bom dia a todos,

Cara estou fazendo um site asp com banco mysql, já conseguir fazer inserção e busca tranquilo com os dois mais quando tentei fazer a paginação não teve capeta que fizesse funcionar.

Alguém poderia me ajudar a fazer uma paginação que funcione com os dois elementos?

Até mais!

cyberalexxx · Agosto 15, 2006

No MySQL voce não conseguirá fazer paginação usando as propriedades do recordset, voce tera que fazer na unha usando a propriedade LIMIT quando for fazer o SELECT no banco.

não tenho nenhum exemplo meu agora comigo, mais vai um ai retirado da WEB (todos os direitos ao devido autor)

<%
'# Written by snowboardr on www.pscode.com
'# If you like this code, please comment on planet source code!
'# And vote if its worthy enough?
'# Who doesn't want those awesome .net charts?

'# Installing MySQL :: tutorial available at PSC:
'# http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=7739&lngWId=4
'#

Dim sDatabaseConnection
sDatabaseConnection = "DRIVER={MySQL ODBC 3.51 Driver};"_
      & "SERVER=localhost;"_
      & "DATABASE=test;"_
      & "UID=root;PWD=; OPTION=35;"

%>
<html>
<head>
<title>Paging</title>
<style type="text/css">
<!--
.sgrey {


font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
font-weight: bold;
color: #999999;
text-decoration: none;
}
.elinks {
color: #003399;
text-decoration: none;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
font-weight: bold;
}
-->
</style>
</head>
<body>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="1">
 <tr bgcolor="#EEEEEE">
   <td colspan="4"><table width="100%"  border="0" cellspacing="0" cellpadding="0">
       <tr>
         <td><div align="left"><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif" class="bluet"><strong>New Projects</strong></font></div>
             <div align="center"></div></td>
       </tr>
     </table>
       <div align="center"></div>
       <div align="left"></div>
       <div align="center"> </div></td>
 </tr>
 <tr bgcolor="#CCCCCC" class="greyt">
   <td colspan="4"><img width="0" height="0"></td>
 </tr>
 <tr bgcolor="#EEEEEE" class="greyt">
   <td colspan="4"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">Project Subject
   </font>      <div align="center"></div></td>
 </tr>
 <%
& #39;############################################################################
##########
'# COUNT RECORDS FIRST
& #39;############################################################################
##########
   Dim sQueryStatus
   
   Dim intRecordsPerPage
   Dim intPages
   Dim sLimitPart 'LIMIT 0,1  -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
   Dim sStartQuery
   Dim sMaxStart
   Dim sProjectPrev
   Dim sProjectNext
   intRecordsPerPage = 10  'How many records to show per page

  Dim sqlProjectCount
  Dim connCount, rsCount
  Dim sProjectCount
  sqlProjectCount = "SELECT COUNT(pid) FROM projects"
  'Response.Write(sqlProjectCount)
  Set connCount = Server.Createobject("ADODB.Connection")
  Set rsCount = Server.CreateObject("ADODB.Recordset")
  connCount.open sDatabaseConnection
  Set rsCount = connCount.Execute(sqlProjectCount)
  If rsCount.eof then
   sProjectCount = 0
    Else
   sProjectCount = rsCount.Fields(0)  'number of records
  End If
  connCount.close
  set connCount=nothing
  set rsCount=nothing
  'Response.Write(sProjectCount)

& #39;############################################################################
##########
'# SIMPLE PAGING
& #39;############################################################################
##########
  sStartQuery = Request.QueryString("start")

  'Get total pages
  If intRecordsPerPage < sProjectCount then
     intPages = sProjectCount / intRecordsPerPage
  End If

  'here we modify the number if it has a decimal, a better solution maybe would be to use formatnumber
  Dim instrIntPages
  instrIntPages = Instr(intPages,".")

  'If after the decimal there is a 0 then we need to add a page
  If instrIntPages > 0 then
   intPages = Left(intPages,instrIntPages) + 1
  End If

  'Lets create the limit for the sql
  'LIMIT 0,1  -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
  If sStartQuery <> "" AND isNumeric(sStartQuery) then
    sLimitPart = "LIMIT " & sStartQuery & "," & intRecordsPerPage
     Else
    sLimitPart = "LIMIT " & "0," & intRecordsPerPage
  End If

  'Lets figure out what the max start number is
  sMaxStart = (intPages*intRecordsPerPage)-intRecordsPerPage

  If sStartQuery <> "" AND isNumeric(sStartQuery) then
    sStartQuery = CINT(sStartQuery)
     Else
    sStartQuery = 0
  End If


  'Now lets create our previous / next buttons and disable them if they are not needed.
  If sMaxStart < 0 then sMaxStart = 0

  If sStartQuery = "" or sStartQuery = "0" then 'PREVIOUS DISABLED
   sProjectPrev = "<a span class='sgrey'>Previous</span>"
    ElseIf sStartQuery > 0 then 'PREVIOUS ENABLED
     sProjectPrev = "<a class='elinks' href='?start=" & sStartQuery - intRecordsPerPage & "'>Previous</span>"
  End If

  'Next
  If sMaxStart = sStartQuery then 'NEXT DISABLED
   sProjectNext = "<a span class='sgrey'>Next</span>"
    ElseIf sStartQuery  < sMaxStart then 'NEXT ENABLED
     sProjectNext= "<a class='elinks' href='?start=" & sStartQuery + intRecordsPerPage & "'>Next</span>"
  End If


  'Now its time to select our projects
  Dim connP, rsP
  Dim sqlProjects

  Dim sPid
  Dim sSubject

  sqlProjects = "SELECT "_
  & "pid,"_
  & "psubject "_
  & " FROM projects ORDER by pid ASC " & sLimitPart


  'Response.Write(sqlProjects)
  Set connP = Server.Createobject("ADODB.Connection")
  Set rsP = Server.CreateObject("ADODB.Recordset")
  connP.open sDatabaseConnection
  Set rsP = connP.Execute(sqlProjects)
   If NOT rsP.eof then
   Do while not rsP.eof
   sPid = rsP("pid")
   sSubject = rsP("psubject")
   %>
 <tr>
   <td colspan="4" bgcolor="#FFFFFF"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=sSubject%></font></td>
 </tr>
 <%
   rsP.movenext
   loop
   %>
 <tr>
   <td colspan="4" bgcolor="#FFFFFF" class="sgrey"><div align="right">
       <table width="100%"  border="0" cellspacing="0" cellpadding="0">
         <tr bgcolor="#EEEEEE">
           <td width="29%" height="23">
             <div align="left" class="greyt">Total: <%=sProjectCount%></div></td>
           <td width="71%"><div align="center">
               <%  Dim queryPageOn
    queryPageOn = Request.QueryString("page")
    If NOT isNumeric(queryPageOn) AND NOT sStartQuery = "0" then
     queryPageOn = "1"
    End If
     queryPageOn = CINT(queryPageOn)
    Response.Write(sProjectPrev & "&nbsp;&nbsp;")
    '# Create Page Numbers & Links
      Dim iPages, iPagesTemp
      For iPages=1 to intPages
      iPagesTemp = iPages-1  
       If NOT queryPageOn=iPages then
      Response.write "<a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a>&nbsp; "
         Else
      Response.write "<span class='greyt'>[</span><a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a><span class='greyt'>]</span>&nbsp; "
     End If
      Next
      Response.Write("&nbsp;&nbsp;"&sProjectNext)
      %>
           </div></td>
         </tr>
       </table>
   </div></td>
 </tr>
 <%Else%>
 <tr>
   <td bgcolor="#FFFFFF" class="sred"> <font color="#FF0000" face="Verdana, Arial, Helvetica, sans-serif">No projects under this category at this time. </font></td>
   <td width="22%" bgcolor="#FFFFFF"><div align="center"></div></td>
   <td bgcolor="#FFFFFF">&nbsp;</td>
 </tr>
 <%End IF
   connP.close
   set connP=nothing
   set rsP=nothing
& #39;############################################################################
#############
%>
</table>
</body>
</html>

Nesh · Agosto 31, 2006

Valeu cyberalexxx pela dica.

desculpe a demora de responder mais estava sem net a um tempinho, problemas com o velox como sempre.

Vou testar o codigo e retorno

Entrar

Paginação Asp Para Banco Mysql

Pergunta

Nesh

Link para o comentário

Compartilhar em outros sites

2 respostass a esta questão

Posts Recomendados

cyberalexxx

Link para o comentário

Compartilhar em outros sites

Nesh

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns

Navegação

Atividades