Display the Contents of MSSQL database on your website using ASP.NET

[euk-paul]

Hello ,

Here I will explain the steps to connect to a database using a Data Source Name (DSN)


1) Lets assume that you have a MSSQL database with Your data in it . Give the database user db_owner, db_datareader and db_datawriter permission.

2) Create DSN from start==>programs==>administrative tools==>data sources==>system dsn==>
add ==> Select SQL server and click finish. A wizard will be opened , put in the DSN name lets name it datasource , the sql server which you want to connect to , Click Next select the option With SQL server authentication using a login ID and password entered by the user . Click next,next and finish the DSN creation wizard .

3) The DSN will be used in our script to connect to the database . Create a ASP file under you website root folder say displaydata.asp and use the following VBscript which is the default language of ASP.NET


<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim StartTime, EndTime

StartTime = Timer

Dim objCN ' ADO Connection object
Dim objRS ' ADO Recordset object
Dim strsql ' SQL query string
Dim strTemp ' a temporary string

' Create a connection object
Set objCN = Server.CreateObject("ADODB.Connection")

' Connect to the data source
objCN.ConnectionString = "DSN=datasource;UID=dbuser;PWD=passw0rd"
objCN.Open

' Prepare a SQL query string
strsql = "SELECT * FROM tablename"

' Execute the SQL query and set the implicitly created recordset
Set objRS = objCN.Execute(strsql)

' Write out the results in a table by concatenating into a string
Response.write "<table>"

Do While Not objRS.EOF
strTemp = strTemp & "<tr><td>" & objRS("Column Name") & "</td>"
strTemp = strTemp & "<td>" & objRS("Column Name") & "</td>"
strTemp = strTemp & "<td>" & objRS("Column Name") & "</td>"
'strTemp = strTemp & "<td>" & objRS("Column Name ") & "</td></tr>"
objRS.MoveNext
Loop
Response.write strTemp
Response.write "</table>"

Set objCN = Nothing
Set objRS = Nothing

EndTime = Timer
Response.write "<p>processing took "&(EndTime-StartTime)&" seconds<p>&nbsp;"
%>

Test Results

Regards,

Euk-Paul.