ClassicASP 게시판 목록(List) 만들기 & 쿼리를 이용한 페이징
페이지 정보
본문
2010-04-27 : 글번호 가상번호 보이도록 코드 수정
2011-03-22 : 검색후 페이지 색인오류 수정
2011-04-06 : 검색로직에서 wc 변수값 누락 심각오류 수정
2013-02-08 : dim 변수 추가
2013-05-06 : 디비 컨넥션 함수 일치화 dbconn , dbcon --> conn
2014-08-13 : 문법오류 수정 , 파일명/순차필드 명을 변수화
2016-08-30 : 디비 커넥션 함수 일치화 conn --> dbconn
2016-12-09 : 상단 공통값 분리
2017-03-11 : 하단 페이징의 i 를 page_i 로 변경 , db --> dbconn 으로 변경
2017-03-15 : wc 쿼리의 where 문 오류 수정
2018-06-19 : page를 PageNo 로 수정
<%
Option Explicit
set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.Open "Provider=SQLOLEDB.1;Password=비밀번호;Persist Security Info=True; User ID=아이디; Initial Catalog=디비이름; Data Source=디비주소"
dim bo_use_category, bo_category_list
dim bo_table
dim mb_id
dim wr_num, wr_reply, wr_parent, wr_is_comment, wr_comment, wr_comment_reply, ca_name, wr_option, wr_subject, wr_content, wr_link1, wr_link1_hit, wr_link2, wr_link2_hit, wr_hit, wr_goood, wr_nogood, wr_name, wr_password, wr_email, wr_homepage, wr_datetime, wr_file, wr_last, wr_ip, wr_facebook_user, wr_twitter_user, wr_1, wr_2, wr_3, wr_4, wr_5, wr_6, wr_7, wr_8, wr_9, wr_10
dim tel1, tel2, tel3
dim sql, rs, page_i
dim strBody, objMail
bo_table = sqlcheck(request("bo_table"))
if bo_table = "" then
bo_table = "oneQnA"
end if
dim intPage, strListURL, wc
dim PageNo, searchStr, SearchPart
dim sql, rs, pagesize, block, recordCount, pagecount, id_num
dim First_Page, End_Page
' [주의1] 아래 request 문은 SQL injection 방지를 위해서 코드 제어해야함
' https://www.happyjung.com/lecture/480
' [주의2] request 값을 post, get 넘김에 따라서 받아들이는 것에 대한 선언이 필요
' https://www.happyjung.com/lecture/63
dim table_name, board_name, idx_name
'################### 공통1 ###################
table_name = "디비테이블명" ' 테이블이름
board_name = "게시판파일명.asp" ' 게시판 파일이름
idx_name = "idx" ' 순차 필드명
PageNo= Request("PageNo")
SearchStr = Request.Form("SearchStr")
SearchPart = Request.Form("SearchPart")
if PageNo="" then
PageNo=1
end if
wc = " where gubun = 'News' "
if SearchPart="" then SearchPart="name"
'################### 공통1 ###################
'################### 공통2 ###################
PageSize= 10 '## 한페이지에 나타낼 글 수
block = 10 '## 페이지 네비게이션(페이징)의 수
'################### 공통2 ###################
if SearchStr<>"" then
wc = " where " & SearchPart & " like '%"& SearchStr & "%'"
end if
sql = "select count("& idx_name &") as recCount from "& table_name & wc '### 저장된 글 수를 가져온다.
set rs = dbconn.Execute(sql)
recordCount = rs(0) '### 저장된 전체 글 수
rs.close
pagecount = int((recordCount-1)/PageSize) +1 '### 페이지가 나눠진 수
id_num = recordCount - (PageNo -1) * PageSize '### 글 번호 순차적으로 처리하는 부분
'### 글을 가져오는 부분
if SearchStr="" then
sql = "select top "& PageSize&" * FROM "& table_name
if int(PageNo) > "1" then
if wc = "" then
sql = sql & " where "
else
sql = sql & wc &" and "
end if
sql = sql &" "& idx_name &" not in "
sql = sql &" (select top "& ((PageNo - 1) * PageSize) & " " & idx_name &" FROM "& table_name & wc
sql = sql &" order by "& idx_name &" desc)"
end if
else
sql = "select top "& PageSize&" * from "& table_name
'if int(PageNo) > "1" then
if wc = "" then
sql = sql & " where "
else
sql = sql & wc &" and "
end if
sql = sql &" "& idx_name &" not in "
sql = sql &" (select top "& ((PageNo- 1) * pagesize) & " " & idx_name &" from "& table_name & wc
sql = sql &" order by "& idx_name &" desc)"
'else
'end if
end if
sql = sql &" order by "& idx_name &" desc"
'response.write "<br>" & sql
set rs = dbconn.execute(sql)
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>홈페이지</title>
<style>
body, td { font-size: 12px; }
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" bordercolor="0">
<tr>
<form action="<%=board_name%>" method="post" name="search" id="search">
<td>
<select name="SearchPart" class="input_box">
<option value="name" <% if SearchPart = "name" then Response.Write("selected") end if %>>이름 </option>
<option value="gubun_text" <% if SearchPart = "gubun_text" then Response.Write("selected") end if %>>증명서 </option>
</select>
<input name="SearchStr" type="text" class="input_box" value="<%=SearchStr %>" size="30">
<input name="submit" type="button" value="찾 기">
</td>
</form>
<td align="right">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top" style="padding:0 5px 0 0;"></td>
<td valign="top"><a href="<%=board_name%>">목록</a></td>
</tr>
</table>
</td>
</tr>
</table>
<br />
총 <%=recordcount%>개의 자료가 있습니다. (<%=PageNo%> Page / <%=PageSize%> Page)<br />
<br />
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>번호</td>
<td>이름</td>
<td>신청일자</td>
</tr>
<tr>
<td colspan="7" bgcolor="#dadada" style="line-height:1px;"></td>
</tr>
<%
if rs.bof or rs.eof then
%>
<tr>
<td height="24" colspan="7" align="center" bgcolor="#FFFFFF"><b>게시글</b>이 없습니다.</td>
</tr>
<%
else
do until rs.eof
%>
<tr>
<td><%=id_num%></td>
<td><%=rs("name")%></td>
<td><%=rs("wdate")%></td>
</tr>
<%
rs.movenext
id_num = id_num-1
loop
end if
%>
</table>
<div align="center">
<% '페이지 네비게이션 시작
if rs.bof then
else
if Int(PageNo) <> 1 then
%>
<a href="<%=board_name%>?PageNo=<%=PageNo-1%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">[prv]</font></a>
<%
end if
First_Page = Int((PageNo-1)/Block)*Block+1
If First_Page <> 1 Then
%>
[<a href="<%=board_name%>?PageNo=1&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">1</font></a>] ..
<%
end if
If PageCount - First_Page < Block Then
End_Page = PageCount
Else
End_Page = First_Page + Block - 1
End If
For i = First_Page To End_Page
If Int(PageNo) = page_i Then
%>
[<font color="#FF0000" style="font-size:8pt;"><b><%=page_i%></b></font>]
<%
Else
%>
[<a href="<%=board_name%>?PageNo=<%=PageNo%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;"><%=page_i%></font></a>]
<%
End If
Next
If End_Page <> PageCount Then
%>
.. [<a href="<%=board_name%>?PageNo=<%=PageCount%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="000000" style="font-size:8pt;"><%=PageCount%></font></a>]
<%
end if
If Int(PageNo) <> PageCount Then
%>
<a href="<%=board_name%>?PageNo=<%=PageNo+1%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">[next]</font></a>
<%
End If
End If
'페이지 네비게이션 끝
%>
</div>
<%
rs.Close
dbconn.Close
Set rs = Nothing
Set dbconn = Nothing
%>
</body>
</html>
참고자료
http://taeyo.net/Columns/View.aspx?SEQ=226&PSEQ=15&IDX=7
2011-03-22 : 검색후 페이지 색인오류 수정
2011-04-06 : 검색로직에서 wc 변수값 누락 심각오류 수정
2013-02-08 : dim 변수 추가
2013-05-06 : 디비 컨넥션 함수 일치화 dbconn , dbcon --> conn
2014-08-13 : 문법오류 수정 , 파일명/순차필드 명을 변수화
2016-08-30 : 디비 커넥션 함수 일치화 conn --> dbconn
2016-12-09 : 상단 공통값 분리
2017-03-11 : 하단 페이징의 i 를 page_i 로 변경 , db --> dbconn 으로 변경
2017-03-15 : wc 쿼리의 where 문 오류 수정
2018-06-19 : page를 PageNo 로 수정
<%
Option Explicit
set dbconn = Server.CreateObject("ADODB.Connection")
dbconn.Open "Provider=SQLOLEDB.1;Password=비밀번호;Persist Security Info=True; User ID=아이디; Initial Catalog=디비이름; Data Source=디비주소"
dim bo_use_category, bo_category_list
dim bo_table
dim mb_id
dim wr_num, wr_reply, wr_parent, wr_is_comment, wr_comment, wr_comment_reply, ca_name, wr_option, wr_subject, wr_content, wr_link1, wr_link1_hit, wr_link2, wr_link2_hit, wr_hit, wr_goood, wr_nogood, wr_name, wr_password, wr_email, wr_homepage, wr_datetime, wr_file, wr_last, wr_ip, wr_facebook_user, wr_twitter_user, wr_1, wr_2, wr_3, wr_4, wr_5, wr_6, wr_7, wr_8, wr_9, wr_10
dim tel1, tel2, tel3
dim sql, rs, page_i
dim strBody, objMail
bo_table = sqlcheck(request("bo_table"))
if bo_table = "" then
bo_table = "oneQnA"
end if
dim intPage, strListURL, wc
dim PageNo, searchStr, SearchPart
dim sql, rs, pagesize, block, recordCount, pagecount, id_num
dim First_Page, End_Page
' [주의1] 아래 request 문은 SQL injection 방지를 위해서 코드 제어해야함
' https://www.happyjung.com/lecture/480
' [주의2] request 값을 post, get 넘김에 따라서 받아들이는 것에 대한 선언이 필요
' https://www.happyjung.com/lecture/63
dim table_name, board_name, idx_name
'################### 공통1 ###################
table_name = "디비테이블명" ' 테이블이름
board_name = "게시판파일명.asp" ' 게시판 파일이름
idx_name = "idx" ' 순차 필드명
PageNo= Request("PageNo")
SearchStr = Request.Form("SearchStr")
SearchPart = Request.Form("SearchPart")
if PageNo="" then
PageNo=1
end if
wc = " where gubun = 'News' "
if SearchPart="" then SearchPart="name"
'################### 공통1 ###################
'################### 공통2 ###################
PageSize= 10 '## 한페이지에 나타낼 글 수
block = 10 '## 페이지 네비게이션(페이징)의 수
'################### 공통2 ###################
if SearchStr<>"" then
wc = " where " & SearchPart & " like '%"& SearchStr & "%'"
end if
sql = "select count("& idx_name &") as recCount from "& table_name & wc '### 저장된 글 수를 가져온다.
set rs = dbconn.Execute(sql)
recordCount = rs(0) '### 저장된 전체 글 수
rs.close
pagecount = int((recordCount-1)/PageSize) +1 '### 페이지가 나눠진 수
id_num = recordCount - (PageNo -1) * PageSize '### 글 번호 순차적으로 처리하는 부분
'### 글을 가져오는 부분
if SearchStr="" then
sql = "select top "& PageSize&" * FROM "& table_name
if int(PageNo) > "1" then
if wc = "" then
sql = sql & " where "
else
sql = sql & wc &" and "
end if
sql = sql &" "& idx_name &" not in "
sql = sql &" (select top "& ((PageNo - 1) * PageSize) & " " & idx_name &" FROM "& table_name & wc
sql = sql &" order by "& idx_name &" desc)"
end if
else
sql = "select top "& PageSize&" * from "& table_name
'if int(PageNo) > "1" then
if wc = "" then
sql = sql & " where "
else
sql = sql & wc &" and "
end if
sql = sql &" "& idx_name &" not in "
sql = sql &" (select top "& ((PageNo- 1) * pagesize) & " " & idx_name &" from "& table_name & wc
sql = sql &" order by "& idx_name &" desc)"
'else
'end if
end if
sql = sql &" order by "& idx_name &" desc"
'response.write "<br>" & sql
set rs = dbconn.execute(sql)
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>홈페이지</title>
<style>
body, td { font-size: 12px; }
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" bordercolor="0">
<tr>
<form action="<%=board_name%>" method="post" name="search" id="search">
<td>
<select name="SearchPart" class="input_box">
<option value="name" <% if SearchPart = "name" then Response.Write("selected") end if %>>이름 </option>
<option value="gubun_text" <% if SearchPart = "gubun_text" then Response.Write("selected") end if %>>증명서 </option>
</select>
<input name="SearchStr" type="text" class="input_box" value="<%=SearchStr %>" size="30">
<input name="submit" type="button" value="찾 기">
</td>
</form>
<td align="right">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top" style="padding:0 5px 0 0;"></td>
<td valign="top"><a href="<%=board_name%>">목록</a></td>
</tr>
</table>
</td>
</tr>
</table>
<br />
총 <%=recordcount%>개의 자료가 있습니다. (<%=PageNo%> Page / <%=PageSize%> Page)<br />
<br />
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>번호</td>
<td>이름</td>
<td>신청일자</td>
</tr>
<tr>
<td colspan="7" bgcolor="#dadada" style="line-height:1px;"></td>
</tr>
<%
if rs.bof or rs.eof then
%>
<tr>
<td height="24" colspan="7" align="center" bgcolor="#FFFFFF"><b>게시글</b>이 없습니다.</td>
</tr>
<%
else
do until rs.eof
%>
<tr>
<td><%=id_num%></td>
<td><%=rs("name")%></td>
<td><%=rs("wdate")%></td>
</tr>
<%
rs.movenext
id_num = id_num-1
loop
end if
%>
</table>
<div align="center">
<% '페이지 네비게이션 시작
if rs.bof then
else
if Int(PageNo) <> 1 then
%>
<a href="<%=board_name%>?PageNo=<%=PageNo-1%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">[prv]</font></a>
<%
end if
First_Page = Int((PageNo-1)/Block)*Block+1
If First_Page <> 1 Then
%>
[<a href="<%=board_name%>?PageNo=1&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">1</font></a>] ..
<%
end if
If PageCount - First_Page < Block Then
End_Page = PageCount
Else
End_Page = First_Page + Block - 1
End If
For i = First_Page To End_Page
If Int(PageNo) = page_i Then
%>
[<font color="#FF0000" style="font-size:8pt;"><b><%=page_i%></b></font>]
<%
Else
%>
[<a href="<%=board_name%>?PageNo=<%=PageNo%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;"><%=page_i%></font></a>]
<%
End If
Next
If End_Page <> PageCount Then
%>
.. [<a href="<%=board_name%>?PageNo=<%=PageCount%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="000000" style="font-size:8pt;"><%=PageCount%></font></a>]
<%
end if
If Int(PageNo) <> PageCount Then
%>
<a href="<%=board_name%>?PageNo=<%=PageNo+1%>&SearchPart=<%=SearchPart%>&SearchStr=<%=SearchStr%>" onFocus="this.blur()"><font color="#000000" style="font-size:8pt;">[next]</font></a>
<%
End If
End If
'페이지 네비게이션 끝
%>
</div>
<%
rs.Close
dbconn.Close
Set rs = Nothing
Set dbconn = Nothing
%>
</body>
</html>
참고자료
http://taeyo.net/Columns/View.aspx?SEQ=226&PSEQ=15&IDX=7
댓글목록
등록된 댓글이 없습니다.