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.

Question:
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.

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

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

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.