Classic ASP global.asa SQL Server 2008 connection

问题:

I have been given a web application written in Classic ASP to port from Windows 2003 Server (SQL Server 2000 and IIS 6) to Windows 2008 Server (SQL Server 2008 and IIS 7.5).

The site uses a GLOBAL.ASA file to define global variables, one of which is the connection string (cnn) to connect to SQL Server.

Below is the (old) connection string from GLOBAL.ASA:

Sub Application_OnStart
    Dim cnnDem, cnnString
    Set cnnDem = Server.CreateObject("ADODB.Connection")
    cnnDem.CommandTimeout = 60
    cnnDem.Mode = admodeshareexclusive
    cnnString = "Provider=SQLOLEDB; Data Source=192.xxx.x.xx; User  Id=xxxx; Password=xxxxx; default catalog=xxxxxxx;"
    Application("conString")=cnnString
    Call cnnDem.Open(cnnString)
    Application("cnn") = cnnDem
End Sub

The .ASP pages then use the cnn value like this:

strSQL = "Select * From tblUtilities order by companyname"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, Application("cnn"), adOpenKeyset

However I could not get the connection string to connect – I whittled it down to a “Failed to Login” error message (no matter what Login ID I tried).

I edited the GLOBAL.ASA file as follows and it works.

Sub Application_OnStart
    Dim cnnDem, cnnString
    Set cnnDem = Server.CreateObject("ADODB.Connection")
    cnnDem.CommandTimeout = 60
    cnnString = "Provider=SQLNCLI10.1;User Id=xxxx; Password=xxxxx;Initial Catalog=xxxxxxx;Data Source=xxxxxx\SQLEXPRESS;"
    Application("conString")=cnnString
    Application("cnn")=cnnString
    Call cnnDem.Open(cnnString)
End Sub

The main difference is that cnn now contains the connection string, where as previously cnn was an object referring to ADOBD.Connection.

The question I have is what impact (if any) will this have on the application. I have done some basic (local) testing and everything looks ok at the moment. But I am wondering if there might be multi-user issues (or something of that nature) that might arise when this site is deployed again.

回答1:

I keep the Connection String in Global.asa but create the connection in a separate function loaded as needed. An Application connection object may not be aware of temporary network issues that may close that connection, and then future attempts to use the connection will not be successful.

Hope this makes sense.

回答2:

One of the best and easiest way to connect to create a Database Connection String is to crease a new ASP file in the root directory or elsewhere and include the Connection string into it:

//Global.asp //

<%
Dim connectionString
connectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=YourSQLServer;UID=sa;PWD=*******;DATABASE=YourDataBase"
%>

Then create an include statement in each file that you would like to call this connection.

<!-- #include virtual="global.asp" -->

Then, where you need to setup your connection call, simply use your code to connect to the Database:

<%
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open =   ConnectionString
Set rsReports = Server.CreateObject("ADODB.Recordset")
strSQL = "Select * From Customers"
rsReports.Open strSQL, adoCon
%>