Home arrow ASP arrow Page 3 - 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 system DSN's (contd.)
(Page 3 of 5 )

Click on the next button to continue with the wizard. If you have already designed a new database or just prefer not to use the master database once you're actually connected to SQL Server (highly recommended: the master database contains several important extended stored procedures and tables that should be treated with care), then change the default database name from "master" to "Northwind", for example.

The "Attach Database Filename" text box allows you to attach a database file to your DSN. We won't use this method in our example. Make sure the "Use ANSI quoted identifiers" and "Use ANSI nulls, paddings and warnings" check boxes are checked. They tell SQL server to make sure that in any SQL queries, identifiers containing special characters or match keywords must be enclosed in identifier quote characters. Click the next button to move onto the last tab of the system DSN wizard.

All of the options on this tab can be left as is, accept one. If you have specialized currency symbols, dates or regional settings, then make sure that the "Use regional settings when outputting currency, numbers, dates and times" check box is checked. This will save you the effort of having to convert dates and other fields to your countries local format every time they are used.

That's all we need to do to create a system DSN. Click on the finish button. The "Verify DSN Settings" dialog appears. If you'd like to test our newly created system DSN, then click on the "Test Data Source…" button. If not, simply click OK.

Testing our new system DSN

To connect to your database using a system DSN, it's simply a matter of instantiating a new ADO connection object from within your ASP script and calling its open method with a reference to your system DSN, like this:


Dim conn

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

conn.Open "DSN=TestDSN"

If conn.errors.count = 0 Then

Response.write "Connected OK"

End If


There are both advantages and disadvantages that result from using system DSN's to connect to a database. They are shown below:

  • System DSN's are available to any authenticated windows user.
  • Details of the database connection are not stored in any physical file or ASP script, and are therefore more secure.
  • Because a system DSN is stored in the registry, it can be modified through the "Data Sources (ODBC)" tab just as easily as it can be created.
  • Because system DSN's are stored in the registry, every time we use them to connect to our database from within ASP, the ASP engine has to do a registry lookup to get the DSN's connection and authentication credentials.
  • System DSN's are slower than using an OLEDB connection string directly. This is only slightly noticeable (approximately a 15% speed differential) when more than 10 users are concurrently connected.

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-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials