AIHOST™ ASP Tutorial 5

ASP Tutorial

What is ODBC? What is a DSN?
ODBC stands for Open Database Connection (or Connectivity). ODBC makes it easy for users to create ASP scripts that will connect to a database. Normally, each ODBC will have a DSN (Data Source Name). The DSN is a complete index of a series of information about the database. In essence, it's a variable that is used between the server and the ASP script. The DSN will be one word that means the specified driver type, file name, and location of the database.

There are several types of ODBC connections. They are as follows:
System DSN
This Data Source name is a data source created on the web server by the administrator of the server. This is the most popular type of DSN and generally a lot more reliable.
File DSN
A connection that your script makes itself each time access to the database is required, specifying the path to and name of the database. The database must reside on the server in a directory that your script can access for this to work. The File DSN is setup through the Windows Control Panel.

Please review the terms below. These terms will be used in this section.

<%
set rs=Server.CreateObject("adodb.Recordset")
connect="DSN=dsnname"
rs.open connect
%> This is one way to connect to a DSN for a database that is MS Access, or MS Excel. This connects to the database.

Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")
Conn.Open dsn, dbuser, dbpass
sqlstmt = "SELECT * FROM example"
rs.Open sqlstmt, Conn This is how you would connect to DSN-less connections

<%
Session.timeout = 15
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "DSNName"
Set Session("MyDB_conn") = conn
%> This is another way to connect to a DSN for a database that is MS Access or MS Excel. This connects to the database.

<%
conn.close
set conn = Nothing
%> This syntax should reside at the end of your ASP code to ensure that you close your connection to the database.

To execute the following example script below, you will need to create either a MS Access, or MS Excel database, and create a DSN. Here is a short description of what the database should look like. In this example, we will use MS Access.

Table name: example

Field Data Type

ID AutoNumber

Name Text

Email Text

Phone Text

Input a few lines of data into the table. We'll say that 5 or 6 entries are sufficient. Create a DSN named 'example'. Once you have created the DSN, create the following script.

------------- Copy the following into test_dsn.asp -------------
<HTML>
<HEAD>
<TITLE>Testing ODBC / DSN</TITLE>
</HEAD>
<BODY>
<%
set rs=Server.CreateObject("adodb.Recordset")
' Set the DSN equal to your DSN name
conn="DSN=example"
' Query the database using an SQL statement and dump
' the information into a variable named sqlstmt.
sqlstmt = "SELECT * from example"

' Open the record set by connecting
' to the database using the DSN. Then, execute the:
' SQL statement.
rs.open sqlstmt, conn

' This is an error routine. We would suggest using this:
' in the future by simply copying and pasting
' it into your code.

If err.number>0 then
response.write "VBScript Errors Occured:" & ""
response.write "Error Number=" & err.number & ""
response.write "Error Descr.=" & err.description & ""
response.write "Help Context=" & err.helpcontext & ""
response.write "Help Path=" & err.helppath & ""
response.write "Native Error=" & err.nativeerror & ""
response.write "Source=" & err.source & ""
response.write "SQLState=" & err.sqlstate & ""
' If you don't receive any errors, then:
' you must have connected to the database successfully
Else
response.write "You have successfully connected to the database."
End If

' Close your connection and ensure you're finished using the rs variable.
rs.close
set rs=Nothing
%>
</BODY>
</HTML>