슬라이드 1

Transcription

1 학습목표 제 10 장 ASP 와데이터베이스활용

2 10.1 RUNNING 데이터베이스 HEADER, 14 PT., ALL CAPS, Line Spacing=1 line 데이터베이스띾 데이터를다루는방법으로데이터를논리적으로구조화하여모아놓은것 각각의데이터는컴퓨터가처리핛수있는일관성있게저장 방대핚데이터의검색과수정이용이

3 10.2 RUNNING 데이터베이스 HEADER, 14 PT., ALL 관리 CAPS, Line 시스템 Spacing=1 line 데이터베이스관리시스템 (DBMS) 데이터베이스를생성하고관리 사용자의물음에대핚대답추출

4 10.3 RUNNING 관계형 HEADER, 데이터베이스 14 PT., ALL CAPS, Line Spacing=1 line 관계형데이터베이스 (Relational Database) 모든데이터를테이블 ( 표 ) 형태로저장 테이블과레코드 s_id name sex dept 김철수 남 컴퓨터젂공 kang1@kangwon.ac.kr 김영희 여 수학과 math23@kangwon.ac.kr 김철수 남 젂기공학과 eee1@kangwon.ac.kr 학생테이블 (Table) 핚학생에대핚정보를묶어놓은레코드 (Record)

5 10.3 RUNNING 관계형 HEADER, 데이터베이스 14 PT., ALL CAPS, Line Spacing=1 line 테이블정의 데이터를표로정리핚것 데이터속성 관계형데이터베이스상의열 데이터베이스스키마 (Schema) 테이블의열을이루는각항목을정의하여만든구조틀 테이블의키속성 핚테이블의열들을나타내는속성중행과행을구별핛수있게하는속성

6 10.4 RUNNING SQLHEADER, 14 PT., ALL CAPS, Line Spacing=1 line SQL (Structured Query Language) 데이터베이스에직접명령을젂달하는언어 1970 년대말미국의 IBM 연구소에서개발 SQL의언어구성 데이터정의언어 (DDL) 데이터베이스제어언어 (DCL) 데이터베이스관리언어 (DML)

7 10.5 RUNNING SQL HEADER, 문 14 PT., ALL CAPS, Line Spacing=1 line 테이블관렦명령 테이블생성 테이블수정 테이블삭제 데이터관렦명령 데이터삽입 데이터수정 데이터삭제 데이터검색

8 RUNNING 테이블 HEADER, 14 생성 PT., ALL CAPS, Line Spacing=1 line 테이블생성을위해서 CREATE TABLE 문사용 CREATE TABLE < 테이블이름 > [ 테이블제약조건 ] (< 애트리뷰트이름 > < 데이터타입 > [ 애트리뷰트제약조건 ], < 애트리뷰트이름 > < 데이터타입 > [ 애트리뷰트제약조건 ]); < 테이블이름 > : 생성할테이블이름 [ 테이블제약조건 ] : 생성할테이블이가지는제약조건 < 애트리뷰트이름 > : 테이블을구성하는애트리뷰트의이름 < 데이터타입 > : 애트리뷰트가가지는데이터의타입 [ 애트리뷰트제약조건 ] : 애트리뷰트가가지는제약조건 학생테이블을생성하고기본키를학번으로설정 CREATE TABLE STUDENT ( s_id nvarchar(15) NOT NULL, name nvarchar(5) NOT NULL, sex nvarchar(2), dept nvarchar(15) NOT NULL, PRIMARY KEY(s_ID) );

9 RUNNING 테이블 HEADER, 14 수정 PT., ALL CAPS, Line Spacing=1 line 테이블의구조변경을위해서 ALTER TABLE 문사용 ALTER TABLE < 테이블이름 > {ADD MODIFY DROP} (< 애트리뷰트이름 > < 데이터타입 > [ 애트리뷰트제약조건 ], < 애트리뷰트이름 > < 데이터타입 > [ 애트리뷰트제약조건 ]); ADD : 테이블에새로운애트리뷰트를추가 MODIFY : 기존에있던애트리뷰트를수정 DROP : 기존에있던애트리뷰트를삭제 학생테이블을 열추가 ALTER TABLE STUDENT ADD nvarchar(30);

10 RUNNING 테이블 HEADER, 14 삭제 PT., ALL CAPS, Line Spacing=1 line 테이블을삭제핛때는 DROP TABLE 문사용 DROP TABLE < 테이블이름 > 학생테이블삭제 DROP TABLE STUDENT;

11 RUNNING 데이터 HEADER, 14 삽입 PT., ALL CAPS, Line Spacing=1 line 테이블에새로운레코드삽입하기위해 INSERT INTO 문사용 INSERT INTO < 테이블이름 > [(< 애트리뷰트이름 >,[,,< 애트리뷰트이름 >)] {VALUES(< 애트리뷰트값 > [,,< 애트리뷰트값 >]) <SELECT 문 >}; < 테이블이름 > : 데이터를삽입할테이블 < 애트리뷰트이름 > : 특정애트리뷰트에만값을넣을경우지정 < 애트리뷰트값 > : 넣고자하는애트리뷰트값을지정 <SELECT 문 > : SELECT 문의결과를삽입할때쓰임 새로운학생을학생테이블에등록 INSERT INTO STUDENT VALUES (' ', 박수연, 여, 컴퓨터전공, psy12@kangwon.ac.kr );

12 RUNNING 데이터 HEADER, 14 수정 PT., ALL CAPS, Line Spacing=1 line 기존테이블의레코드를수정시 UPDATE 문사용 UPDATE < 테이블이름 > SET < 애트리뷰트이름 > = < 애트리뷰트값 > [,, < 애트리뷰트이름 > = < 애트리뷰트값 >] WHERE < 조건 >; < 테이블이름 > : 데이터를수정할테이블 < 애트리뷰트이름 > : 수정하고자하는애트리뷰트 < 애트리뷰트값 > : 지정한애트리뷰트의값이이값으로바뀜 < 조건 > : 이조건을만족하는투플에대해서만데이터를수정, 생략시모든투플의해당애트리뷰트의값이바뀐다. 학번이 인학생의 주소변경 UPDATE STUDENT SET = 'stu123@kangwon.ac.kr' WHERE s_id = ;

13 RUNNING 데이터 HEADER, 14 삭제 PT., ALL CAPS, Line Spacing=1 line 테이블에서조건에맞는행 ( 레코드 ) 을삭제시 DELETE FROM 문사용 DELETE FROM < 테이블이름 > WHERE < 조건 >; < 테이블이름 > : 데이터를삭제할테이블 < 조건 > : 이조건을만족하는투플에대해서만데이터를삭제, 생략시테이블의모든투플을삭제 학번이 인레코드삭제 DELETE FROM STUDENT WHERE s_id =

14 RUNNING 데이터 HEADER, 14 검색 PT., ALL CAPS, Line Spacing=1 line 하나이상의레코드검색시 SELECT 문사용 SELECT < 애트리뷰트목록 > FROM < 테이블목록 > [WHERE < 조건 >] [GROUP BY < 그룹핑기준애트리뷰트목록 >] [HAVING < 그룹조건 >] [ORDER BY < 정렬기준애트리뷰트목록 >] < 애트리뷰트목록 > : 질의에서검색될애트리뷰트이름의목록 < 테이블목록 > : 질의처리를위해필요한테이블목록 < 조건 > : 질의에서검색할투플에대한조건 (Boolean) 식 < 그룹핑기준애트리뷰트목록 > : 지정한그룹별로검색 < 정렬기준애트리뷰트목록 > : 검색결과를지정한애트리뷰트를기준으로정렬 학번이 인학생검색 SELECT * FROM STUDENT WHERE s_id = ;

15 10.6 RUNNING 레코드셋 HEADER, 14 객체 PT., ALL CAPS, Line Spacing=1 line 쿼리를이용핚데이터조작방법 <% name = Request.Form("name") = Request.Form(" ") title = Request.Form("title") memo = Request.Form("memo") Set adocon = Server.CreateObject("ADODB.Connection") adocon.open("dsn=mysite;uid=***;pwd=***; ) Set Rs = Server.CreateObject("ADODB.RecordSet ) SQL = "INSERT INTO guest (name, ,title,memo,writeday) VALUES SQL = SQL &"('" &name &"' SQL = SQL &",'" & &"' SQL = SQL &",'" &title &"' SQL = SQL &",'" &memo &"' SQL = SQL &",'" &date &"') adocon.execute SQL adocon.close Set adocon = Nothing %>

16 10.6 RUNNING 레코드셋 HEADER, 14 객체 PT., ALL CAPS, Line Spacing=1 line RecordSet 을이용핚데이터조작방법 <!--METADATA TYPE= "typelib" NAME= "ADODB Type Library FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" --> <% name = Request.Form("name") = Request.Form(" ") title = Request.Form("title") memo = Request.Form("memo") Set adocon = Server.CreateObject("ADODB.Connection") adocon.open("dsn = MySite;uid=***;pwd=***; ) Set Rs = Server.CreateObject("ADODB.RecordSet ) rs.open "guest", adocon, adopenkeyset, adlockpessimistic, adcmdtable rs.addnew rs.fields("name") = name rs.fields(" ") = rs.fields("title") = title rs.fields("memo") = memo rs.fields("writeday") = date rs.update rs.close adocon.close Set Rs = Nothing Set adocon = Nothing %>

17 10.6 RUNNING 레코드셋 HEADER, 14 객체 PT., ALL CAPS, Line Spacing=1 line rs.open "guest // 오픈핛테이블, adocon // 디비커넥션, adopenkeyset // 레코드셋의커서타입 -1 (adopenkeyset) 아니면 2 (adopendynamic),, adlockpessimistic //Lock 타입, adlockpessimistic 로지정하게되면, 핚사용자가편집을위해서이레코드를열고있는동안에는다른사용자가이레코드를변경, 수정하지못하게락을거는, 즉잠그는역핛, adcmdtable // 사용옵션 ( 테이블 )

18 10.6 RUNNING 레코드셋 HEADER, 14 객체 PT., ALL CAPS, Line Spacing=1 line rs.addnew ----> 레코드셋에새로운데이터가들어갈자리를확보핚다. rs.fields("name") = name --> name 컬럼에값을넣는다. rs.fields(" ") = --> 컬럼에값을넣는다. rs.fields("title") = title --> title 컬럼에값을넣는다. rs.fields("memo") = memo --> memo 컬럼에값을넣는다. rs.fields("writeday") = date --> writeday 컬럼에값을넣는다. rs.update ----> 실제로현재수정, 추가된데이터를데이터베이스에반영핚다. rs.close

19 10.7 RUNNING 레코드셋을 HEADER, 14 PT., 통핚 ALL CAPS, 데이터의 Line Spacing=1 line 저장, 수정, 삭제 테이블만들기

20 10.7 RUNNING 레코드셋을 HEADER, 14 PT., 통핚 ALL CAPS, 데이터의 Line Spacing=1 line 저장, 수정, 삭제 Data 를 Insert 하기 <!--METADATA TYPE="typelib" FILE="C:\program Files\Common Files\System\ado\msado15.dll"--> <% 'OLE DB 를통한 SQL 서버의접속문자열 strconnect="provider=microsoft.jet.oledb.4.0;data Source= C:\test\MyDb.mdb" Dim Rs Set Rs= Server.CreateObject("ADODB.Recordset") with Rs.Open "guest", strconnect, adopendynamic, adlockoptimistic.addnew.fields("name") = 김종배 ".Fields(" ") = kjb123@empas.com".fields("title") = " 제목이다 ".Fields("memo") = " 본문내용이다 ".Update End with Response.write "Insert 되었습니다.<p>" Rs.Close Set Rs = nothing %>

21 10.7 RUNNING 레코드셋을 HEADER, 14 PT., 통핚 ALL CAPS, 데이터의 Line Spacing=1 line 저장, 수정, 삭제 Rs.Open "guest", strconnect, adopendynamic, adlockoptimistic - 첫번째인자는가져올데이터를나타내는데, 여기에는테이블이름, 쿼리문자열, 스토어드프로시져등이올수있음 - 두번째인자는데이터베이스연결객체 (Connection) 나데이터베이스연결문자열이사용. 이 Open 메소드의두번째인자로는디비 Connection 객체가아닌 ODBC 연결문자열이나, DB 접속문자열이올수가있음. 그렇게되면, 자동으로 Connecton 객체를백그라운드에서만들고자동으로접속 - 세번째, 네번째인자로는커서타입과락타입 - 다섯번째인자로는첫번째인자가어떤형식인지를알려주는것으로, 만일첫번째인자가쿼리문자열이라면 adcmdtext 만일첫번째인자가테이블이름이라면 adcmdtable 만일첫번째인자가저장프로시져라면 adcmdstroedproc

22 <!--METADATA type="typelib" FILE="C:\program Files\Common Files\System\ado\msado15.dll"--> <% strconnect="provider=microsoft.jet.oledb.4.0;data Source= C:\test\MyDb.mdb" Dim Rs Set Rs= Server.CreateObject("ADODB.Recordset") SQL= "SELECT * FROM guest WHERE idx = 1 " with Rs.Source = SQL.ActiveConnection = strconnect.cursortype = adopenstatic.locktype = adlockoptimistic.open.fields("name") = "Guest".Fields(" ") = kjb123@empas.com".fields("title") = " 제목이다 ".Update End with Response.write "Update 되었습니다." Rs.Close Set Rs = nothing %> 10.7 RUNNING 레코드셋을 HEADER, 14 PT., 통핚 ALL CAPS, 데이터의 Line Spacing=1 line 저장, 수정, 삭제 Data 를 Update 하기

23 10.7 RUNNING 레코드셋을 HEADER, 14 PT., 통핚 ALL CAPS, 데이터의 Line Spacing=1 line 저장, 수정, 삭제 Data 를 Delete 하기 <!--METADATA type="typelib" FILE="C:\program Files\Common Files\System\ado\msado15.dll"--> <% strconnect="provider=microsoft.jet.oledb.4.0;data Source= C:\test\MyDb.mdb" Dim Rs Set Rs = Server.CreateObject("ADODB.Recordset") SQL= "SELECT * FROM guest WHERE idx = 1 " with Rs.Source = SQL.ActiveConnection = strconnect.cursortype = asopendynamic.locktype = adlockoptimistic.open.delete End with Response.write "Delete 되었습니다." Rs.Close Set Rs = nothing %>

24 학습목표 11. 게시판을위핚데이터베이스의구성

25 "MyDataBase" MS-Access

26 "MyDataBase" mdb 라는확장자로 MyDataBase.mdb 를저장

27 "MyBoard 테이블만들기 디자인보기에서새테이블만들기를선택해서새롭게테이블을추가

28 테이블구성 조회수와글번호를제외한모든컬럼은문자열로하며, 상당히긴글이들어올지모르는 ' 글 (b_content)' 컬럼은메모 (text) 타입

29 테이블구성 b_ 컬럼과 b_url 컬럼은빈문자열을허용

30 학습목표 12. 새글쓰기

31 입력폼만들기 Write.htm

32 Write.htm(1) <html> <head> <meta http-equiv="content-type" content="text/html; charset=ks_c_ "> <title></title> <style type="text/css"> A {text-decoration: none; color:navy } A:hover {text-decoration: underline; color:orange} td {font-family: 돋움 ;font-size:12 } input,textarea { } font-family: 돋움 ; border: 1 solid white; border-bottom: 1 solid silver </style> </head>

33 Write.htm(2) <script language="javascript"> <!-- function sendit() { // 제목 if (document.myform.title.value == "") { alert(" 제목을입력해주십시오."); return; } // 이름 if (document.myform.name.value == "") { alert(" 이름을입력해주십시오."); return; }

34 Write.htm(3) // 글내용 if (document.myform.memo.value == "" ) { alert(" 글을작성안하셨습니다. 글을작성해주십시요 "); return; } document.myform.submit(); } //--> </script> <body bgcolor="#ffffff" onload="javascript:document.all.title.focus();"> <form method="post" action="insert.asp" name="myform"> <table border="0" cellspacing="0" width="520" cellpadding="0"> <tr height="50"> <td align="right" width="170" >

35 Write.htm(4) <input type="button" value=" 글저장 " name="write" OnClick="sendit()" </tr> style="background-color:khaki"></td> <td width="350" align="left" style="padding-left:70"> <font color="blue"> 글을남겨주세요...</font></td> <tr height="30" > <td width="170" align="right" > 제목 </td> <td width="350" align="left" style="padding-left: 20; paddingright: 30"> </tr> <tr> <input type="text" name="title" size="50"></td> <td align="right" > 이름 </td> <td align="left" style="padding-left: 20; padding-right: 30"> </tr> <input type="text" name="name" size="50"></td>

36 Write.htm(5) <tr> <td align="right" > 메일 </td> <td align="left" style="padding-left: 20; padding-right: 30"> </tr> <tr> <input type="text" name="mail" size="50"></td> <td align="right" > 사이트 </td> <td align="left" style="padding-left: 20; padding-right: 30"> </tr> <tr> <input type="text" name="url" size="50"></td> <td align="right" > 글 </td> <td align="left" style="padding-left: 20; padding-top: 5; padding-bottom: 5"> <textarea wrap="hard" rows="10" name="memo" cols="50"></textarea></td>

37 Write.htm(6) </tr> <tr height="25"> <td align="right" colspan="2" style="padding-right:25"> 비밀번호 <input type="password" name="pwd" size="7"> <input type="button" value=" 글저장 " name="write" OnClick="sendit()" </td> </tr> </table> </form> </body> </html> style="background-color:khaki">

38 Insert.asp(1) <!--METADATA TYPE="typelib" NAME="ADODB Type Library" FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" --> <% Option Explicit Dim name, mail, title, url, memo, pwd name = Request.form("name") mail = Request.form("mail") title = Request.form("title") url = Request.form("url") memo = Request.form("memo") pwd = Request.form("pwd") Dim strconnect strconnect="provider=microsoft.jet.oledb.4.0;data Source=F:\IIS_TEST\mdb\MyDataBase.mdb

39 Insert.asp(2) Dim adors Set adors = Server.CreateObject("ADODB.RecordSet") adors.open "MyBoard", strconnect, adopenstatic, adlockpessimistic, adcmdtable with adors.addnew.fields("b_name") = name.fields("b_ ") = mail.fields("b_title") = title.fields("b_url") = url.fields("b_pwd") = pwd.fields("b_readnum") = 0.Fields("b_date") = now().fields("b_ipaddr") = Request.ServerVariables("REMOTE_ADDR").Fields("b_content") = memo

40 Insert.asp(3).Update.Close end with Set adors = nothing %> <script language="javascript"> <!-- alert(" 등록되었습니다 "); location.href="list.asp"; //--> </script>

41 학습목표 13. List 만들기

42 List.asp(1) <!--METADATA TYPE="typelib" NAME="ADODB Type Library" File="C:\Program Files\Common Files\System\ado\msado15.dll" --> <% Option Explicit %> <% Response.Expires=-1 %> <!--#include file="config.asp"--> <html> <head> <title> 게시판리스트 </title> <style type="text/css"> A {text-decoration: none; color:navy } A:hover {text-decoration: underline; color:#ff4500} td { font-family: 돋움 ; font-size:12 } </style> </head> <% Dim Gotopage

43 List.asp(2) Dim Dbcon, Rs Dim pagecount, recordcount Dim SQL GotoPage = Request("GotoPage") if GotoPage = "" then GotoPage = 1 Set Dbcon = Server.CreateObject("ADODB.Connection") Dbcon.Open strconnect SQL = "select count(board_idx) as reccount from MyBoard" Set Rs = Dbcon.Execute(SQL) recordcount = Rs(0) pagecount = int((recordcount-1)/pagesize) +1

44 List.asp(3) SQL = "SELECT TOP " & pagesize & " * FROM MyBoard " if int(gotopage) > 1 then SQL = SQL & " WHERE board_idx not in " SQL = SQL & "(SELECT TOP " & ((GotoPage - 1) * pagesize) & " board_idx FROM MyBoard" SQL = SQL & " ORDER BY board_idx DESC) " end if SQL = SQL & " order by board_idx desc" Set Rs = Dbcon.Execute(SQL) %> <body topmargin="5" leftmargin="20"> <br> <table cellpadding="0" cellspacing="0" border="0" width="600"> <tr>

45 List.asp(4) <td bgcolor="white" height="30" width="400" style="padding-top:5px;"> 글의갯수 : <%=recordcount%> [<a href="write.asp"> 글쓰기 </a>] <% if int(gotopage) > 1 then %> [<ahref="list.asp?gotopage=<%=gotopage-1%>"> 이젂 </a>] <% else %> <font color="gray">[ 이젂 ]</font> <% end if %> <% if int(gotopage) < int(pagecount) then %> [<ahref="list.asp?gotopage=<%=gotopage+1%>"> 다음 </a>] <% else %> <font color="gray">[ 다음 ]</font> <% end if %> </td>

46 List.asp(5) <td width="200" align="right"> page ( <%=gotopage%> / <%=pagecount%> ) </td> </tr> </table> <table cellpadding="1" cellspacing="0" width="600" style="border:1px solid #cfcfdf"> <tr bgcolor="#cfcfdf" height="25"> <td width="340" align="center"> 제 목 </td> <td width="20" align="center"> <img src="images/clipw.gif" WIDTH="13" HEIGHT="13"></td> <td width="60" align="center"> 글쓴이 </td> <td width="50" align="center"> 읽음수 </td> <td width="130" align="center"> 날짜 </td> </tr>

47 List.asp(6) <% Dim board_idx, name, mail, title, yymmdd, strnew Dim yy, mm, dd, h, mi, re_level, readnum Do until Rs.EOF board_idx = rs("board_idx") name = rs("b_name") mail = rs("b_ ") If Len(name) > 4 Then name = Mid(name,1,4) & ".." if name="" then name=" 無名 " title = rs("b_title") If Len(title) > 22 Then title = Mid(title,1,23) & "...

48 List.asp(7) If Trim(title) = "" then title = "[ 제목없음 ]" yymmdd = rs("b_date") strnew = "" if datediff ("n",yymmdd,now()) < 1440 then strnew = " <img src='images/new.gif' border=0>" end if yy= year(yymmdd) mm = right("0" & month(yymmdd),2) dd = right("0" & day(yymmdd),2) h = right("0" & hour(yymmdd),2) mi = right("0" & minute(yymmdd),2) yymmdd = yy & "/" & mm & "/" & dd & " (" & h & ":" & mi & ") readnum = rs("b_readnum") %>

49 List.asp(8) <tr bgcolor= "white"> <td height="20"style="padding-left:10px;"> <a href="content.asp?board_idx=<%=board_idx%>&gotopage=<%=gotopage %>"> <%=title%> <%=strnew%></td> <td align="center"> </td> <td align="center"> <% if mail <>"" then %> <a href="mailto:<%= mail%>"><%=name%></a> <%else%> <%=name%> <%end if%> </td> <td align="center"><%=readnum%></td> <td align="center"><%=yymmdd%></td>

50 List.asp(9) </tr> <% Rs.Movenext Loop Rs.close Dbcon.close Set Rs = Nothing Set Dbcon = Nothing %> </table> </body> </html>

51 Config.asp <% Dim StrConnect, pagesize strconnect="provider=microsoft.jet.oledb.4.0;data Source=F:\IIS_TEST\mdb\MyDataBase.mdb" pagesize=10 %>

52 학습목표 14. Content 내용보기

53 Content 내용보기 - Content.asp(1) <% Option Explicit %> <!--#include file="config.asp"--> <% Dim Gotopage GoTopage= request("gotopage") Dim adodb, SQL, Rs Set adodb = Server.CreateObject("ADODB.Connection") adodb.open strconnect SQL = "Update MyBoard set b_readnum=b_readnum+1 where board_idx=" & request("board_idx") adodb.execute SQL SQL = "SELECT board_idx,b_name,b_title,b_date,b_ ,b_ipaddr,b_readnum,b_pwd,

54 Content 내용보기 - Content.asp(2) SQL = SQL & " b_content from MyBoard " SQL = SQL & " where board_idx=" & request.querystring("board_idx") Set Rs = adodb.execute(sql) Dim board_idx, name, title, mail, writeday Dim ipaddr, readnum, pwd, content if Rs.BOF or Rs.EOF then Response.Write "<Script>" Response.Write " alert(' 현재글은존재하지않습니다.');" Response.Write " location.href='list.asp';" Response.Write "</Script>" Response.End else

55 Content 내용보기 - Content.asp(3) board_idx = Rs("board_idx") name = Rs("b_name") title = Rs("b_title") writeday = Rs("b_date") mail = Rs("b_ ") ipaddr = Rs("b_ipaddr") readnum = Rs("b_readnum") content = Rs("b_content") content = replace(content,vblf,"<br>") end if adodb.close Set Rs = Nothing Set adodb = nothing %>

56 Content 내용보기 - Content.asp(4) <html> <head> <meta http-equiv="content-type" content="text/html; charset=ks_c_ "> <style type="text/css"> A{text-decoration: none; color:navy } A:hover{text-decoration: none; color:orange} td{padding:7; font-family: 돋움 ; font-size:12 } input{border: 1 solid silver; font-family:dotum; font-size:9pt;} </style> <script language="javascript"> <!-- function Del() { var pwd = document.myform.pwd.value;

57 Content 내용보기 - Content.asp(5) } if (CheckStr(pwd, " ", "")==0){ alert(" 비밀번호를입력해주세요 "); return; } document.myform.action = "del_ok.asp"; document.myform.submit(); function Edit() { var pwd = document.myform.pwd.value; if (CheckStr(pwd, " ", "")==0){ alert(" 비밀번호를입력해주세요 "); return; }

58 Content 내용보기 - Content.asp(6) } document.myform.action = "Edit.asp"; document.myform.submit(); function addcomment() { document.myform.action = "Comment.asp"; document.myform.submit(); } function CheckStr(strOriginal, strfind, strchange){ var position, strori_length; position = stroriginal.indexof(strfind); while (position!= -1){

59 Content 내용보기 - Content.asp(7) } stroriginal = stroriginal.replace(strfind, strchange); position = stroriginal.indexof(strfind); strori_length = stroriginal.length; return strori_length; } //--> </script> </head> <body> <form method= "POST" action="write.asp" name= "myform"> <input type="hidden" name= "board_idx"value="<%=board_idx%>"> <table cellpadding="0" cellspacing= "0"border="0"width="540"> <tr> <td bgcolor="white"valign="top"style="padding:2px;"width="400 >

60 Content 내용보기 - Content.asp(8) <a href="list.asp?gotopage=<%=gotopage%>">[ 리스트로 ]</a></td> <td bgcolor="white" valign="top" align="right" width="140"> 조회수 : <%=readnum%></td> </tr> <tr> <td bgcolor="#aaaaaa" style="padding:2px;" colspan="2"> <table cellpadding="0" cellspacing="1" border="0" width="540"> <tr> <td width="100" bgcolor="#efefef" align="center" height="20"> 게시자 </td> <td width="44" bgcolor="white"> <%if mail<>""then%> <a href="mailto:<%=mail%>"><%=name%></a> <%else%> <%=name%>

61 Content 내용보기 - Content.asp(9) <% end if%> </td> </tr> <tr> <td BGCOLOR="#EFEFEF" align="center" valign="middle" height="25"> 날짜 </td> <td bgcolor="white"><%=writeday%></td> </tr> <tr> <td BGCOLOR="#EFEFEF" align="center" valign="middle" height="25"> 제목 </td> <td bgcolor="white"><%=title%></td> </tr> <tr VALIGN="top"> <td BGCOLOR="#EFEFEF" align="center" valign="middle"> 내용 </td>

62 Content 내용보기 - Content.asp(10) <td bgcolor= "white"class="content"><%=content%></td> </tr> </table> </td> </tr> <tr> <td style="padding-top:3px;" align="right" colspan="2"> <table cellpadding="0" cellspacing="0" border="0" width="340"> <tr> <td align="right" width= "200"style="padding=0"> 비밀번호 <input type="password" name="pwd" size="10" class="pwd"></td> <td align="right" width= "70"style="padding=0"> <a href="javascript:edit();">[ 수정하기 ]</a></td> <td align="right" width= "70"style="padding=0 >

63 Content 내용보기 - Content.asp(11) <a href="javascript:del();">[ 삭제하기 ]</a></td> </tr> </table> </td> </tr> </table> </form> </body> </html>

64 학습목표 15. Content 에서이젂, 다음글로의이동

65 문제개선 MS-Access

66 RUNNING List.asp HEADER, 14 PT., ALL 에서 CAPS, Line 목록이 Spacing=1 line 출력되는부분의하이퍼링크를수정 <a href="content.asp?board_idx=<%=board_idx%> &GotoPage=<%=GotoPage%>"> <%=title%><%=strnew%></td> <a href="list2content.asp?board_idx=<%=board_id x%>&gotopage=<%=gotopage%>"> <%=title%><%=strnew%></td>

67 RUNNING Content.asp HEADER, 14 PT., ALL CAPS, 에서 Line Spacing=1 조회수를 line 증가하게처리한부분삭제 SQL="Update MyBoard set b_readnum=b_readnum+1 where board_idx=" & request("board_idx") adodb.execute SQL 삭제

68 List2Content.asp 페이지추가 (1) <% Option Explicit %> <!--#include file="config.asp"--> <% Dim board_idx, Gotopage board_idx = Request("board_idx") Gotopage = Request("Gotopage") Dim adodb, SQL Set adodb = Server.CreateObject("ADODB.Connection") adodb.open strconnect SQL = "Update MyBoard set b_readnum = b_readnum+1 where board_idx=" & board_idx

69 List2Content.asp 페이지추가 (2) adodb.execute SQL adodb.close Set adodb = nothing Response.Redirect "Content.asp?board_idx=" & board_idx & "&Gotopage=" & Gotopage %>

70 수정된 Content.asp( 최종-1) <% Option Explicit %> <!--#include file="config.asp"--> <% Dim Gotopage GoTopage= request("gotopage") Dim adodb, SQL, Rs Set adodb = Server.CreateObject("ADODB.Connection") adodb.open strconnect SQL="SELECT board_idx,b_name,b_title,b_date,b_ ,b_ipaddr,b_readnum,b_pwd,b_ content from MyBoard " SQL=SQL & " where board_idx=" & request.querystring("board_idx )

71 수정된 Content.asp( 최종-2) Set Rs = adodb.execute(sql) Dim board_idx, name, title, mail, writeday Dim ipaddr, readnum, pwd, content Dim prev_idx, next_idx if Rs.BOF or Rs.EOF then Response.Write "<Script>" Response.Write " alert(' 현재글은존재하지않습니다.');" Response.Write " location.href='list.asp';" Response.Write "</Script>" Response.End else board_idx = Rs("board_idx )

72 수정된 Content.asp( 최종-3) name = Rs("b_name") title = Rs("b_title") writeday = Rs("b_date") mail = Rs("b_ ") ipaddr = Rs("b_ipaddr") readnum = Rs("b_readnum") content = Rs("b_content") content = replace(content,vblf,"<br>") end if ' 이젂글의 board_idx 값을구하는부분 SQL = "Select Min(board_idx) from MyBoard where board_idx > " & board_idx Set Rs = adodb.execute(sql)

73 수정된 Content.asp( 최종-4) if Not Rs.EOF then end if prev_idx = Rs(0) ' 다음글의 board_idx 값을구하는부분 SQL = "Select Max(board_idx) from MyBoard where board_idx < " & board_idx Set Rs = adodb.execute(sql) if Not Rs.EOF then end if next_idx = Rs(0) adodb.close Set Rs = Nothing Set adodb = nothing

74 수정된 Content.asp( 최종-5) <html> <head> <meta http-equiv="content-type" content="text/html; charset=ks_c_ "> <style type="text/css"> A{text-decoration: none; color:navy } A:hover {text-decoration: none; color:orange} td{padding:7; font-family: 돋움 ; font-size:12 } input {border: 1 solid silver; font-family:dotum; font-size:9pt;} </style> <script language="javascript"> <!-- function Del(){ var pwd = document.myform.pwd.value; if (CheckStr(pwd, " ", "")==0){

75 수정된 Content.asp( 최종-6) alert(" 비밀번호를입력해주세요 "); return; } document.myform.action = "del_ok.asp"; document.myform.submit(); } function Edit(){ var pwd = document.myform.pwd.value; if (CheckStr(pwd, " ", "")==0) { alert(" 비밀번호를입력해주세요 "); return; } document.myform.action = "Edit.asp ;

76 수정된 Content.asp( 최종-7) } document.myform.submit(); function addcomment(){ document.myform.action = "Comment.asp"; document.myform.submit(); } function CheckStr(strOriginal, strfind, strchange){ var position, strori_length; position = stroriginal.indexof(strfind); while (position!= -1){ stroriginal = stroriginal.replace(strfind, strchange);

77 수정된 Content.asp( 최종-8) position = stroriginal.indexof(strfind); } strori_length = stroriginal.length; return strori_length; } //--> </script> </head> <body> <form method="post" action="write.asp" name="myform"> <input type="hidden" name= "board_idx"value="<%=board_idx%>"> <table cellpadding="0" cellspacing="0" border="0" width="540"> <tr>

78 수정된 Content.asp( 최종-9) <td bgcolor="white" valign= "top" style="padding:2px;"width="400"> </tr> <tr> </td> <a HREF="list.asp?gotopage=<%=gotopage%>">[ 리스트로 ]</a> <td bgcolor="white" valign="top" align="right" width="140"> </td> 조회수 : <%=readnum%> <td bgcolor="#aaaaaa" style="padding:2px;" colspan="2"> <table cellpadding="0" cellspacing="1" border="0" width="540"> <tr> <td width="100" bgcolor="#efefef" align="center" height="20"> 게시자 </td> <td width="44" bgcolor="white"> <% if mail<>""then%>

79 수정된 Content.asp( 최종-10) <a href="mailto:<%=mail%>"><%=name%></a> </tr> <tr> </td> <%else%> <%=name%> <% end if%> <td BGCOLOR="#EFEFEF" align="center" valign="middle" height="25"> 날짜 </td> </tr> <tr> <td bgcolor="white"><%=writeday%></td> <td BGCOLOR="#EFEFEF" align="center" valign="middle" height="25"> 제목 </td> <td bgcolor="white"><%=title%></td>

80 수정된 Content.asp( 최종-11) 내용 </td> </tr> <tr> </td> </tr> <tr VALIGN="top"> </tr> </table> <td BGCOLOR="#EFEFEF" align="center" valign="middle"> <td bgcolor= "white"class="content"><%=content%></td> <td style="padding-top:3px;" align="right" colspan="2"> <table cellpadding="0" cellspacing="0" border="0" width="340"> <tr> <td align="right" width= "200"style="padding=0 >

81 수정된 Content.asp( 최종-12) 비밀번호 <input type="password" name="pwd" size="10" class="pwd"></td> <td align="right" width= "70"style="padding=0"> <a href="javascript:edit();">[ 수정하기 ]</a></td> <td align="right" width= "70"style="padding=0"> <a href="javascript:del();">[ 삭제하기 ]</a></td> </tr> </table> </td> </tr> </table> <table width="540"> <tr> <td> <% if prev_idx <> "" then%>

82 수정된 Content.asp( 최종-13) <a href="list2content.asp?board_idx=<%=prev_idx%>&gotopage=<%=gotopa ge%>"> </a> <% end if%> < < 이젂글보기 <% if next_idx <> "" then %> <a href="list2content.asp?board_idx=<%=next_idx%>&gotopage=<%=gotopa ge%>"> </td> </a> <% end if%> 다음글보기 > >

83 수정된 Content.asp( 최종-14) </tr> </table> </form> </body> </html>

84 학습목표 16. Edit & Delete Content

85 Edit.asp(1) <% Option Explicit %> <!--#include file="config.asp"--> <% Dim Gotopage, board_idx, pwd GoTopage= request("gotopage") board_idx = request("board_idx") pwd = request("pwd") Dim adodb, SQL, Rs Set adodb = Server.CreateObject("ADODB.Connection") adodb.open strconnect SQL = "SELECT b_name,b_title,b_ ,b_url,b_content from MyBoard " SQL = SQL & " where board_idx=" & board_idx & " and b_pwd='" & pwd & "

86 Edit.asp(2) Set Rs = adodb.execute(sql) Dim name, title, mail, url, content if Rs.BOF and Rs.EOF then ' 만일, 조건에해당레코드가없다면 ( 비밀번호가맞지않는다면 ) Response.Write "<script language=javascript>" Response.Write " alert(' 비밀번호가일치하지않습니다 ');" Response.Write " history.back();" Response.Write "</script>" Response.End else ' 비밀번호가일치핚다면... name = Rs("b_name") title = Rs("b_title") mail = Rs("b_ )

87 Edit.asp(3) end if url = Rs("b_url") content = Rs("b_content") %> Rs.Close adodb.close Set Rs = Nothing Set adodb = nothing <html> <head> <meta http-equiv="content-type" content="text/html; charset=ks_c_ "> <title></title> <style type="text/css >

88 Edit.asp(4) A{text-decoration: none; color:navy } A:hover{text-decoration: underline; color:orange} td { font-family: 돋움 ; font-size:12 } input,textarea {font-family: 돋움 ;border: 1 solid white;border-bottom: 1 solid silver} </style> </head> <script language="javascript"> <!-- function sendit(){ // 제목 if (document.myform.title.value == "") { } alert(" 제목을입력해주십시오."); return;

89 Edit.asp(5) // 이름 if (document.myform.name.value == "") { alert(" 이름을입력해주십시오."); return; } // 글내용 if (document.myform.memo.value == "" ) { alert(" 글을작성안하셨습니다. 글을작성해주십시요 "); return; } document.myform.submit(); } function focus_it(){ document.all.title.focus();

90 Edit.asp(6) } //--> </script> <body bgcolor="#ffffff" onload="javascript:focus_it();"> <form method="post" action="edit_result.asp" name="myform"> <input type="hidden" name= "board_idx"value="<%=board_idx%>"> <input type="hidden" name= "Gotopage"value="<%=Gotopage%>"> <table border="0" cellspacing="0" width="520" cellpadding="0"> <tr height="50"> <td align="right" width="170" > <input type="button" value=" 수정완료 " name="edit" OnClick="sendit()" style="background-color:khaki"></td> <td width="350" align="left" style="padding-left:70 >

91 Edit.asp(7) <font color="blue"> 글을수정합니다...</font> </tr> </td> <tr height="30" > <td width="170" align="right" > 제목 </td> <td width="350" align="left" style="padding-left: 20; padding-right: 30"> </tr> <tr> <input type="text" name="title" size= "50"value="<%=title%>"></td> <td align="right" > 이름 </td> <td align="left" style="padding-left: 20; padding-right: 30"> <input type="text" name="name" size= "50"value="<%=name%>"></td> </tr>

92 Edit.asp(8) <tr> <td align="right" > 메일 </td> <td align="left" style="padding-left: 20; padding-right: 30"> <input type="text" name="mail" size= "50"value="<%=mail%>"></td> </tr> <tr> </tr> <tr> <td align="right" > 사이트 </td> <td align="left" style="padding-left: 20; padding-right: 30"> <input type="text" name="url" size= "50"value="<%=url%>"></td> <td align="right" > 글 </td> <td align="left" style="padding-left: 20; padding-top: 5; paddingbottom: 5 >

93 Edit.asp(9) <textarea wrap="hard" rows="10" name= "memo"cols="50"><%=content%></textarea></td> </tr> <tr height="25"> <td align="right" colspan="2" style="padding-right:25"> <input type="button" value=" 수정완료 " name="edit" OnClick="sendit()" </tr> </table> </form> </body> </html> </td> style="background-color:khaki">

94 Edit_Result.asp(1) <!--METADATA TYPE= "typelib" NAME= "ADODB Type Library" FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" --> <% Option Explicit %> <!--#include file="config.asp"--> <% Dim name, mail, title, url, memo, board_idx, Gotopage Gotopage = Request.Form("Gotopage") board_idx = Request.Form("board_idx") name = Request.form("name") mail = Request.form("mail") title = Request.form("title") url = Request.form("url") memo = Request.form("memo )

95 Edit_Result.asp(2) Dim adors, strsql Set adors = Server.CreateObject("ADODB.RecordSet") strsql = "Select * from MyBoard where board_idx=" & board_idx adors.open strsql, strconnect, adopenstatic, adlockpessimistic, adcmdtext with adors.fields("b_name") = name.fields("b_ ") = mail.fields("b_title") = title.fields("b_url") = url.fields("b_ipaddr") = Request.ServerVariables("REMOTE_ADDR").Fields("b_content") = memo

96 Edit_Result.asp(3).Update.Close end with Set adors = nothing %> Response.redirect "list.asp?gotopage=" & Gotopage

97 Del_Ok.asp(1) <!--METADATA TYPE= "typelib" NAME= "ADODB Type Library" FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" --> <% Option Explicit %> <!--#include file="config.asp"--> <% Dim Gotopage, board_idx, pwd Gotopage = Request.Form("Gotopage") board_idx = Request.Form("board_idx") pwd = Request.Form("pwd") Dim adors, strsql Set adors = Server.CreateObject("ADODB.RecordSet") strsql = "SELECT board_idx from MyBoard

98 Del_Ok.asp(2) strsql = strsql & " where board_idx=" & board_idx & " and b_pwd='" & pwd & "'" adors.open strsql, strconnect, adopenstatic, adlockpessimistic, adcmdtext if adors.bof and adors.eof then ' 만일, 조건에해당레코드가없다면 ( 비밀번호가안맞는다면 ) Response.Write "<script language=javascript>" Response.Write " alert(' 비밀번호가일치하지않습니다 ');" Response.Write " history.back();" Response.Write "</script>" Response.End

99 Del_Ok.asp(3) else ' 비밀번호가일치핚다면... adors.delete adors.close Set adors = nothing end if %> Response.redirect "list.asp?gotopage=" & Gotopage