Codefixer - Connecting to a database with a DSN in ASP

If you do not know how to set up a system DSN (Data Source Name) don't worry as our next tutorial will go through this.

For our example below lets assume that our DSN points to an Access database called 'News.mdb' and that we will be selecting records from the table 'tblNews'.

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Connecting to a database with a DSN</title>
</head>
<body>
<%
'declare your variables
Dim connection, dsn, recordset, sql

'declare the SQL statement that will query the database
sql="SELECT * FROM tbNews"

'initialise the dsn variable
dsn="dsn=mydsn" 'mydsn will be the name you have chosen for your dsn

'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 dsn

'Open the recordset object, execute the SQL
recordset.Open sql,connection

'now lets see if there are any records returned
If Not recordset.Eof Then
response.write "There are records."
Else
response.write "There are no records
End If

'close the connection and recordset objects and free up resources
recordset.Close
Set recordset = Nothing
connection.Close
Set connection = Nothing
%>
</body>
</html>

Remember that 'mydsn' above is name of the DSN.
Note you can change the table name from 'tblNews' to the name of your table and point the DSN to whatever database you like.
One other thing to remember is that you can set up a DSN on your own machine though if you are using a hosting company you may have to ask the webmaster.