ASP MsSQL database connection string

ASP and MSSQL is one of the popular combinations to develop web based applications. Here by using VBScript ADO ( ActiveX Data Object) we will establish the connection from our ASP script to MSSQL database. Here the connection string part we will discuss. You care read more details on the ADO objects here.

We will create our connection string and keep in a separate asp file. This asp file ( let us name it conn.asp ) we will place in our server root directory and call it ( or include ) from different pages of our application like this.

<!-- #include virtual = "/conn.asp" -->

So all the files having any type of transaction with the database, can just use the connection string by calling the file like this. This way at a common place the login information of the MSSQL database is stored this way it is easy to maintain. Imagine an application having 100 asp pages where every page has database connection string at the top. If we shift the hosting account to a new server then we have to update the login information of the new database in all the 100 pages. So it is better to keep the connection string at a common place for better maintainability.

Here is the code inside our conn.asp page.

<%
Dim aConnectionString
aConnectionString = "Provider=SQLOLEDB;Data " _
& "Source=source_name;Database=db_name;" _
& "UID=userid; PWD=password;"
%>

We have to replace source_name, db_name, userid and password with our details.

Now let us see how to use this connection string at a application level page. Here is the code to use this.

<%@ Language=VBScript %>
<% Option Explicit %>
<!-- #include virtual = "/adovbs.inc" -->
<!-- #include virtual = "/conn.asp" -->

<!doctype html public "-//w3c//dtd html 3.2//en">

<html>

<head>
<title></title>
</head>

<body>
<%
Dim conn,R,SQL,RecsAffected

Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeReadWrite
conn.ConnectionString = aConnectionString
conn.Open

If mssql db is not running or in case any problem in id or pw then this error message we will get

Microsoft OLE DB Provider for SQL Server (0x80004005)

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Learn how to connect to MS Access database
Learn how to connect php and MySQL
SELECT records Matching record New Table
Display Identity Inserted record ID Row count
DateTime Functions of MSSQL database
ASP Home

Connection string in VBScript of ASP to connect to MSSQL