Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 8271 | Views in the last 30 days: 3
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones