환경 : MSSQL 2017 Standard
간단하게 테이블을 만들고 데이터를 입력해서 웹에서 출력해보자 ( asp )
1. 데이터베이스를 선택하고 새 쿼리를 실행해서 아래와 같이 테이블을 만들고 데이터를 입력하자
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [xinet] GO CREATE TABLE member ( [idx] [int] IDENTITY(1,1) NOT NULL, [m_id] [varchar](20) NOT NULL, [m_name] [varchar](20) NULL, [m_email] [varchar](100) NULL, ) ON [PRIMARY] GO SET IDENTITY_INSERT member ON INSERT member ([idx], [m_id], [m_name], [m_email]) VALUES ('1', 'admin', '관리자','master@xinet.kr') INSERT member ([idx], [m_id], [m_name], [m_email]) VALUES ('2', 'jsh', '시골청년','jsh@xinet.kr') INSERT member ([idx], [m_id], [m_name], [m_email]) VALUES ('3', 'test', '테스트용도','test@xinet.kr') INSERT member ([idx], [m_id], [m_name], [m_email]) VALUES ('4', 'manager', '부관리자','manager@xinet.kr') SET IDENTITY_INSERT member OFF |
2. 간단하게 select 로 쿼리를 진행하면 아래와 같이 데이터가 출력되는 것을 확인 할 수 있다.
|
1 2 3 4 5 |
SELECT TOP (1000) [idx] ,[m_id] ,[m_name] ,[m_email] FROM [xinet].[dbo].[member] |
3. 이제 웹상에서 해당 소스를 간단하게 asp로 작성해서 출력해보자
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
<% 'declare the variables Dim Connection Dim ConnString Dim Recordset Dim SQL 'define the connection string, specify database driver ConnString="DRIVER={SQL Server};SERVER=192.168.1.10;UID=xinet;" & _ "PWD=xinet123456;DATABASE=xinet" 'declare the SQL statement that will query the database SQL = "SELECT * FROM member" 'create an instance of the ADO connection and recordset objects Set Connection = Server.CreateObject("ADODB.Connection") Set Recordset = Server.CreateObject("ADODB.Recordset") 'Open the connection to the database Connection.Open ConnString 'Open the recordset object executing the SQL statement and return records Recordset.Open SQL,Connection Response.write "<table border=""1"" bordercolor=""#A9A9A9"" cellspacing=""0"" width=""700"" >" Response.write "<tr><th style=""width:200"">I D</th>" Response.write "<th style=""width:200""> NAME </th>" Response.write "<th tyle=""width:300""> E-MAIL </th>" Response.write "</tr>" 'first of all determine whether there are any records If Recordset.EOF Then Response.Write("No records returned.") Else 'if there are records then loop through the fields Do While NOT Recordset.Eof Response.write "<tr>" Response.write "<td style=""text-align:center"">" Response.write Recordset("m_id") Response.write "</td>" Response.write "<td style=""text-align:center"">" Response.write Recordset("m_name") Response.write "<td style=""text-align:center"">" Response.write Recordset("m_email") Response.write "</td>" response.write "</tr>" Recordset.MoveNext Loop End If 'close the connection and recordset objects to free up resources Recordset.Close Set Recordset=nothing Connection.Close Set Connection=nothing response.write "</td></tr></table>" %> |
4. 정상적으로 웹에서 페이지 출력이 완료된다
추가적으로 우편번호 상위 1000개 테이블 출력 ASP 소스
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
<% 'declare the variables Dim Connection Dim ConnString Dim Recordset Dim SQL 'define the connection string, specify database driver ConnString="DRIVER={SQL Server};SERVER=192.168.1.10;UID=xinet;" & _ "PWD=xinet123456;DATABASE=xinet" 'declare the SQL statement that will query the database SQL = "SELECT TOP (1000) * FROM zipcode" 'create an instance of the ADO connection and recordset objects Set Connection = Server.CreateObject("ADODB.Connection") Set Recordset = Server.CreateObject("ADODB.Recordset") 'Open the connection to the database Connection.Open ConnString 'Open the recordset object executing the SQL statement and return records Recordset.Open SQL,Connection Response.write "<table border=""1"" bordercolor=""#A9A9A9"" cellspacing=""0"" width=""900"" >" Response.write "<tr><th style=""width:100"">ZIPCODE</th>" Response.write "<th style=""width:100""> SIDO</th>" Response.write "<th tyle=""width:100""> GUGUN</th>" Response.write "<th tyle=""width:150""> DONG</th>" Response.write "<th tyle=""width:200""> NLDG</th>" Response.write "<th tyle=""width:150""> BUNJI</th>" Response.write "</tr>" 'first of all determine whether there are any records If Recordset.EOF Then Response.Write("No records returned.") Else 'if there are records then loop through the fields Do While NOT Recordset.Eof Response.write "<tr>" Response.write "<td style=""text-align:center"">" Response.write Recordset("ZIPCODE") Response.write "</td>" Response.write "<td style=""text-align:center"">" Response.write Recordset("SIDO") Response.write "<td style=""text-align:center"">" Response.write Recordset("GUGUN") Response.write "<td style=""text-align:center"">" Response.write Recordset("DONG") Response.write "<td style=""text-align:center"">" Response.write Recordset("BLDG") Response.write "<td style=""text-align:center"">" Response.write Recordset("BUNJI") Response.write "</td>" response.write "</tr>" Recordset.MoveNext Loop End If 'close the connection and recordset objects to free up resources Recordset.Close Set Recordset=nothing Connection.Close Set Connection=nothing response.write "</td></tr></table>" %> |