Connection string in VBScript of ASP to connect to MSSQL

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 = "/" -->
<!-- #include virtual = "/conn.asp" -->

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



Dim conn,R,SQL,RecsAffected

Set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString = aConnectionString
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

Be the first to post comment on this article :

Post your comments , suggestion , error , requirements etc here .

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer