Two Ways To Connect To SQL Server 2000 Via ASP

[ganeshbala]

Two Ways To Connect To SQL Server 2000 Via ASP - Using an OLEDB connection string

When using a connection string, four of its parameters are actually used by ActiveX Data Objects (ADO). The others are passed onto the provider, which handles the authentication amongst other tasks.

Take a look at the example below. It uses an OLEDB connection string to connect to a Microsoft SQL Server 200 database from within ASP:

<%

Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = Northwind; User Id = sa; Password="

If conn.errors.count = 0 Then

Response.Write "Connected OK"

End If

%>

In the example above, the connection string contains five name/value pairs. The details of each of these are shown below

    * Provider: The provider value tells ADO which data provider it should call to give us access to the data that we need. "SQLOLEDB" is the best provider to use for Microsoft SQL Server 2000 databases. If we left out the provider value, then ADO would automatically default to the "MSDASQL" provider, which is Microsoft's OLEDB provider for ODBC compatible data repositories.
    * Data Source: The data source value tells our provider the IP Address or netbios name of the computer on which our database is available. In our example above, I have used the value "(local)". This value tells the provider that our database resides on the local machine, and to use local procedure calls instead of remote procedure calls. Using this data source value makes data access faster because database function calls are not bounced across the network and back to the SQL Server like they are normally.
    * Initial Catalog: The initial catalog value is just a fancy name for the database that the provider should connect us to by default.
    * User Id: The login Id of the SQL Server user account that the provider should use during the authentication process.
    * Password: The password of the SQL Server use account that the provider should use during the authentication process.

As with system DSN's, OLEDB connection strings have both advantages and disadvantages associated with using them. These are shown below:

Advantages:

    * Using OLEDB connection strings provides faster access to data when compared to system DSN's.
    * The parameters for the connection string can be stored in a separate file. This file can be included into multiple ASP scripts, meaning that only one change is necessary if we want to modify the connection strings parameters.

Disadvantages:

    * If your web site gets hacked and you have hard-coded the details of the connection string into your pages, then unauthorized persons can mock up an ASP page to access your database, and potentially delete important data.
    * For beginners, it can often be hard to remember the syntax of a connection string. This has been the point of confusion for many developers trying to get a database connection to work properly.