icecarim
01-19-2010, 04:50 PM
i have a record paging code that i every time i click next the records of my table in my database shows.. please help me!! tnx!!! im realy confuse!! im just new in asp and mysql...<!-- #INCLUDE file="connection.asp.asp" -->
<%
'# 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 5.1 Driver};"_
'& "SERVER=localhost;"_
'& "DATABASE=dhvcat;"_
'& "UID=root;PWD=1234; 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>
<%
'######################################################################################
'# COUNT RECORDS FIRST
'######################################################################################
Dim sQueryStatus
dim subject
subject = request.Form("subject")
Dim intRecordsPerPage
Dim intPages
Dim sLimitPart 'LIMIT 0,1 -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
Dim sStartQuery
Dim sMaxStart
Dim sProjectPrev
Dim sProjectNext
intRecordsPerPage = 5
'How many records to show per page
Dim sqlProjectCount
Dim connCount, rsCount
Dim sProjectCount
dim tempcount
sqlProjectCount = "SELECT COUNT(ID) FROM tblstudentsubject WHERE INSTR(tblstudentsubject.sUbcOde,'"& subject &"')"
'Response.Write(sqlProjectCount)
'Set connCount = Server.Createobject("ADODB.Connection")
Set rsCount = Server.CreateObject("ADODB.Recordset")
'connCount.open sDatabaseConnection
Set rsCount = adoConn.Execute(sqlProjectCount)
If rsCount.eof then
sProjectCount = 0
Else
rscount.MoveFirst
sProjectCount = cint(rsCount.Fields(0)) 'number of records
End If
adoConn.close
set adoConn=nothing
set rsCount=nothing
'Response.Write(sProjectCount)
'######################################################################################
'# SIMPLE PAGING
'######################################################################################
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 "_
& "ID,"_
& "studIDnUm, "_
& "LnAme, "_
& "FnAme, "_
& "MI, "_
& "sUbcOde, "_
& "sEctioN "_
& " FROM tblstudentsubject WHERE INSTR(tblstudentsubject.sUbcOde,'"& subject &"') " & sLimitPart
'Response.Write(sqlProjects)
Set connStud = Server.Createobject("ADODB.Connection")
Set rsP = Server.CreateObject("ADODB.Recordset")
connStud.open connstring
Set rsP = connStud.Execute(sqlProjects)
If NOT rsP.eof then
Do while not rsP.eof
sPid = rsP("studIDnUm")
lname= rsP("LnAme")
fname = rsP("FnAme")
middle = rsP("MI")
sSubject = rsP("sUbcOde")
sec = rsP("sEctioN")
%>
<tr>
<td bgcolor="#FFFFFF"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=sPid%></font></td>
<td><input type="text" size="3" value="<%=lname &","& fname &" "& middle%>"></td>
<td><input type="text" size="3" value="<%=sSubject%>"></td>
<td><input type="text" size="3" value="<%=sec%>"></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 & " ")
'# 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> "
Else
Response.write "<span class='greyt'>[</span><a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a><span class='greyt'>]</span> "
End If
Next
Response.Write(" "&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"> </td>
</tr>
<%End IF
connStud.close
set connStud=nothing
set rsP=nothing
'#########################################################################################
%>
</table>
</body>
</html>
:confused:
<%
'# 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 5.1 Driver};"_
'& "SERVER=localhost;"_
'& "DATABASE=dhvcat;"_
'& "UID=root;PWD=1234; 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>
<%
'######################################################################################
'# COUNT RECORDS FIRST
'######################################################################################
Dim sQueryStatus
dim subject
subject = request.Form("subject")
Dim intRecordsPerPage
Dim intPages
Dim sLimitPart 'LIMIT 0,1 -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
Dim sStartQuery
Dim sMaxStart
Dim sProjectPrev
Dim sProjectNext
intRecordsPerPage = 5
'How many records to show per page
Dim sqlProjectCount
Dim connCount, rsCount
Dim sProjectCount
dim tempcount
sqlProjectCount = "SELECT COUNT(ID) FROM tblstudentsubject WHERE INSTR(tblstudentsubject.sUbcOde,'"& subject &"')"
'Response.Write(sqlProjectCount)
'Set connCount = Server.Createobject("ADODB.Connection")
Set rsCount = Server.CreateObject("ADODB.Recordset")
'connCount.open sDatabaseConnection
Set rsCount = adoConn.Execute(sqlProjectCount)
If rsCount.eof then
sProjectCount = 0
Else
rscount.MoveFirst
sProjectCount = cint(rsCount.Fields(0)) 'number of records
End If
adoConn.close
set adoConn=nothing
set rsCount=nothing
'Response.Write(sProjectCount)
'######################################################################################
'# SIMPLE PAGING
'######################################################################################
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 "_
& "ID,"_
& "studIDnUm, "_
& "LnAme, "_
& "FnAme, "_
& "MI, "_
& "sUbcOde, "_
& "sEctioN "_
& " FROM tblstudentsubject WHERE INSTR(tblstudentsubject.sUbcOde,'"& subject &"') " & sLimitPart
'Response.Write(sqlProjects)
Set connStud = Server.Createobject("ADODB.Connection")
Set rsP = Server.CreateObject("ADODB.Recordset")
connStud.open connstring
Set rsP = connStud.Execute(sqlProjects)
If NOT rsP.eof then
Do while not rsP.eof
sPid = rsP("studIDnUm")
lname= rsP("LnAme")
fname = rsP("FnAme")
middle = rsP("MI")
sSubject = rsP("sUbcOde")
sec = rsP("sEctioN")
%>
<tr>
<td bgcolor="#FFFFFF"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=sPid%></font></td>
<td><input type="text" size="3" value="<%=lname &","& fname &" "& middle%>"></td>
<td><input type="text" size="3" value="<%=sSubject%>"></td>
<td><input type="text" size="3" value="<%=sec%>"></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 & " ")
'# 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> "
Else
Response.write "<span class='greyt'>[</span><a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a><span class='greyt'>]</span> "
End If
Next
Response.Write(" "&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"> </td>
</tr>
<%End IF
connStud.close
set connStud=nothing
set rsP=nothing
'#########################################################################################
%>
</table>
</body>
</html>
:confused: