Home arrow ASP arrow Page 4 - Two Ways To Connect To SQL Server 2000 Via ASP

Two Ways To Connect To SQL Server 2000 Via ASP

ASP allows even the newest developer to connect to databases with ease. In fact, the ease with which you can connect to a wide range of databases from within an ASP script is one of the reasons why ASP has become so popular. In this article Joe teaches us how to connect to SQL Server 2000 using system DSN's and an OLEDB connection string.

Author Info:
By: Joe O'Donnell
Rating: 5 stars5 stars5 stars5 stars5 stars / 278
January 13, 2002
  1. · Two Ways To Connect To SQL Server 2000 Via ASP
  2. · Using system DSN's
  3. · Using system DSN's (contd.)
  4. · Using an OLEDB connection string
  5. · Conclusion

print this article

Two Ways To Connect To SQL Server 2000 Via ASP - Using an OLEDB connection string
(Page 4 of 5 )

Now that we've discussed how to use a system DSN to connect to a database via ASP, let's talk about using an OLEDB connection string. A connection string is simply a semi-colon delimited string of text that contains database connection parameters, each represented as a name/value pair.

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:

  • 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.
  • 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.

blog comments powered by Disqus

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials