RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Our Top 10 Tips for Classic ASP : Page 11

Tidy up your server-side programming techniques with these 10 great tricks for classic ASP.

Dodging the Dinosaur of DSN

Still connecting to databases using an ODBC system or file DSNs? Get with it, man! Don't be a dinosaur--instead, use the much faster OLEDB Provider technique to connect to your database without using a DSN. No more pleading with your ISP (or your DBA/Webmaster) to create a System DSN for you. And no more configuration changes when you move Web files.

I see many examples of using a data source name (DSN) to connect to a database. I would like to access a database without using a DSN. Can I do this with ASP? Could you show some sample connection code? I would like to pass the Driver, Server Name, UID, PWD, and Database in a connection string and not depend on a DSN on a machine.

If you are using SQL Server 7, use this code as your connection string:

strConnString = "DSN='';DRIVER={SQL SERVER};" & _ 
"UID=myuid;PWD=mypwd;" & _ 

The most important parameter is the DRIVER= portion. If you want to bypass ODBC and use SQL Server using OLEDB (this is supposed to be faster), use this syntax:

strConnString ="Provider=SQLOLEDB.1;Password=mypassword;" & _ 
"Persist Security Info=True;User ID=myuid;" & _ 
"Initial Catalog=mydbname;" & _ 
"Data Source=myserver;Connect Timeout=15" 

Big Tip:
If you require a connection string but are unfamiliar with the syntax required by the OLE DB provider, use the either the Data Environment designer or the ADO Data Control in Visual Basic to create one, and copy it for use with the ADO Connection object. In the Immediate window, type: ? dataenvironment1.connection1.ConnectionString to get the actual string.

Note: The syntax for Microsoft Access is different.

For more information on using the non-DSN connections with Access, check out the tip, Syntax for DSN-Less Connection for MS Access

Rama Ramachandran is the Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including Professional Visual InterDev 6 Programming and Professional Data Access (Wrox). Rama Ramachandran teaches Visual Basic and Web development at Fairfield University and University of Connecticut.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date