SQLServerCentral Article

Connecting to SQL Server with ASP

,

Whenever you connect to a website that uses Active Server Pages (ASP),

IIS will immediately compile the global.asa to retrieve any global

variables that need to be used. The easiest way to connect to SQL Server through ASP is by using a global.asa file.

You can also connect in each file if you wish to enable things like connection pooling. For purposes of this article, we will only

discuss session connections which would only be used on a small development servers and is only to get you started. More advanced

connection pooling options will be discussed in a later article.

Two main types of global variables that can be used in the global.asa file are Application and Session.

Application variables are shared throughout the entire application and are shared across for anyone connecting to your website.

These should be used sparingly because of this. Session variables are a little more flexible. Each connection to your website can have its

own session and it's not shared. The downside to this of course, is that each user visiting your site will use its own SQL connection, and you will need to close

each connection in your code.

When you create a ASP project through Visual Interdev, it will create a sample global.asa file for you. If you're starting from scratch though, the first line should begin with :

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

This will set where the queries will process. If we use the Session

object, then you're next section of code will

call the object and set the global variables:

Sub Session_OnStart
Session("dbaconn_ConnectionTimeout") = 30 Session("dbaconn_CommandTimeout") = 60 Session("dbaconn_CursorLocation") = 2 Session("dbaconn_RuntimePassword") = "password" Session("dbaconn_RuntimeUserName") = "username" Session("dbaconn_ConnectionString") = "Driver=SQL Server;
User Id=sa;PASSWORD="&Session("dbaconn_RuntimePassword")&";
SERVER="&Session("DefaultServer")&";UID=sa;database=Northwind" Session("Datapath") = "d:\mssql7\data\" End Sub

The Session("dbaconn_ConnectionTimeout") variable will set the

maximum amount of time that you will allow a user to connect.

The Session("dbaconn_CommandTimeout") in contrast will set

how long you will allow a query to run.

You can set cursor location at the global level with the Session("dbaconn_CursorLocation")

variable. Cursor location can also be set at the query level.

The Session("dbaconn_RuntimePassword") and Session

("dbaconn_RuntimeUserName") variables are optional variables

which we will use in our connection string.

We did this so that we can change the password and

username dynamicly through a website. We will cover

changing this in another article. It is not

recommended that you use the SA username for this due

to security issues.

Session("dbaconn_ConnectionString") is required to connect to SQL Server. The example provided is a DSNless connection. You will call

this variable in all sql queries that you run throughout your website.

You can hardcode the username and password in this section. We chose to in our connection string make the

username and password dynamicly assigned.

If you get nothing else from this article, you must learn how to call a query dynamicly. To query

dynamicly, you must concatenate the query opening with "& and close with &". For example : "&Session("dbaconn_RuntimePassword")&"

The Session("Datapath") variable is an example of a user defined variable. If you wish to expose a variable

to the rest of the website.

The last step is to close the global.asa file with :

</SCRIPT>

The global.asa file provides you an easy way to connect to SQL Server

dynamicly without placing a connection string on the top of all files.

In the above example, you established a connection to SQL Server through the global.asa file.

In future articles we will cover how to query and run DBA commands from a website.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating