How Does a DSN or ODBC Connection Work with My Database?

Question:

How does a DSN or ODBC connection work with my database?

Answer:

Active Server Pages work dynamically with IIS to access data from a database and display it on a Web page. You can display data from an ODBC-compliant database, or you can use ASP to make decisions about what to display on Web pages.

Detail:

When building a database driven site, using such popular databases as Microsoft Access or SQL Server, you may use an ODBC/DSN connection to access the database or choose not to use one, implementing an ODBC/DSN-less connection in your Active Server Pages.

Whether or not you implement an ODBC connection, you must first setup the database:

If this is an Access database, you may upload your database into the www/databases directory.

If this is a SQL Server database, make a request to our Technical Support team that a SQL Server Database be setup on your account.

Once the database is established, you may connect to it using an ODBC connection or go without, using ODBC/DSN-less scripting in your ASP.

If using a DSN connection, you may request from our Technical Support team that this be setup. Take careful note of the exact DSN (Data Source Name) that you request. This is the name which you will reference your database. Once this is established, you may connect to it using the following Active Server Page ODBC/DSN example:

<%

Dim cn

Set cn=Server.CreateObject("ADODB.connection")

Cn.Open "dsn=yourdsn;uid=username;pwd=password"

SQLStatement="Select * from mytable"

Set rs=cn.execute(SQLStatement)

%>

If you choose an ODBC/DSN-less connection, you are ready to use your database without a Support assisted ODBC connection. DSN-less ASP examples are provided below, one for Access and one for SQL:

DSN-less connection to Access

 

<%

Set cn=Server.CreateObject("ADODB.connection")

cn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=f:usersusernamewwwdatabasesmydb.mdb"

SQLStatement="Select * from mytable"

Set rs=cn.execute(SQLStatement)
%>DSN-less Connection to SQL

 

<%

set Cn = Server.CreateObject("ADODB.Connection")

Cn.Open "driver=SQL Server;server=209.203.247.250;uid=username;pwd=password;database=username"

SQLStatement="Select * from mytable"

Set rs=cn.execute(SQLStatement)

%>