환경 : 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 |
<% '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 '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 Recordset("m_id") Response.write Recordset("m_name") Response.write Recordset("m_email") Response.write "<br>" 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 %> |