Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Connecting to SQL Server with ASP

By Brian Knight,

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.

 
Total article views: 8274 | Views in the last 30 days: 2
 
Related Articles
FORUM

Definition of Session and Connections

Definition of Session and Connections

FORUM

Server level Login variable for trigger?

Server level Login variable

BLOG

Three Sessions at Spring 2012 Connections Conference

I will be a busy little beaver today, presenting three sessions at the Spring 2012 Connections Confe...

FORUM

connect to a server

connect to a server

FORUM

SQL Server ASP Session State Impact

Performance Implications of running SQL Server Session State

Tags
asp    
programming    
 
Contribute