|
|
Connecting to database with DSN
To store the information of a Data Connection for an Access
or SQL database you can create a System Data Sourse Name (Syatem DSN). If you have
not created a System DSN check these Articles.
How to create System DSN for Microsoft SQL Server
How to create System DSN for Access database
After a System DSN is created you can open connection to that database in an ASP
page with this simple code.
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DSN=MyDSN;UID=Admin;PWD=Test"
%>
Access database
Microsoft SQL Server
If you are connecting to an Access database and your Access database don't need
UserID and Password to connect then your code will be simply like this.
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "DSN=MyDSN"
%>
Following example connect to an access database using DSN Called MyDSN
and get the data from Links table.
<%
Option Explicit
' Dimension Local variables
Dim objConn
Dim objRS
Dim strDSN
Dim strSQL
Dim intTotalColumns
Dim intCounter
' Set the ADO Constants if you are
not including
' the adovbs.inc file
Const adOpenStatic =
3
Const adLockReadOnly =
1
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS =
Server.CreateObject("ADODB.Recordset")
strDSN = "DSN=MyDSN;UID=Admin;PWD=test"
objConn.Open strDSN
strSQL = "SELECT * FROM Links"
objRS.Open strSQL, objConn, adOpenStatic,adLockReadOnly
' get the total number of columns
intTotalColumns = objRS.Fields.Count - 1
%>
<TABLE
BORDER="1" WIDTH="500">
<tr>
<%
' first display the
column names
For intCounter
= 0 To intTotalColumns
%>
<TD>
<B><%=objRS(intCounter).Name%></B>
</TD>
<%
Next
Response.write "</TR>"
' now loop through the
recordset and display the data
Do Until
objRS.EOF = True
Response.Write "<TR>"
For
intCounter = 0 To intTotalColumns
Response.Write "<td width=100 align=center>"
Response.write objRS(intCounter).Value
Response.Write "</TD>"
Next
Response.Write "</TR>"
objRS.Movenext
Loop
%>
</TABLE>
<%
' Close Recordset
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
|
|
|
|
|
|
|
