Problem:
Connecting to MSSQL with out a DSNSolution:
* You can use the following string to connect to MSSQL with out a DSN. 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 =mssql.server.com; Initial Catalog = mydatabase; User Id = USER; Password=PASSWORD; Network Library=dbmssocn;"
If conn.errors.count = 0 Then
Response.Write "Connected OK"
End If
%>
In the example above, mssql.server.com is the MSSQL server, USER is the username and PASSWORD is the password which was provided to you in your MSSQL account setup mail.
<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source =mssql.server.com; Initial Catalog = mydatabase; User Id = USER; Password=PASSWORD; Network Library=dbmssocn;"
If conn.errors.count = 0 Then
Response.Write "Connected OK"
End If
%>
In the example above, mssql.server.com is the MSSQL server, USER is the username and PASSWORD is the password which was provided to you in your MSSQL account setup mail.
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.
- 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. *
