ADO ODBC Driver – SQL Server Connection Example


Microsoft Activex Data Object – ADO

Active Labels: Form Links Using Labels

Using the SQL Server ODBC Connection String Method

Here are two common connection methods for SQL/Server, one for standard security and one for Trusted Connections.

Dim conn as ADODB.Connection
Dim rst as ADODB.Recordset
Dim sql as string

Regular/standard security: Conn.Open “Driver={SQL Server};” & _
“Server=your_sqlserver_name;” & _
“Database=your_database_name;” & _
“UID=username;” & _

Trusted connection security:
Conn.Open “Driver={SQL Server};” & _
“Server=your_sqlserver_name;” & _
“Database=your_database_name;” & _
“UID=;” & _

To have the computer ask for username and password: Conn.Properties(“Prompt”) = adPromptAlways
Conn.Open “Driver={SQL Server};” & _
“Server=your_sqlserver_name;” & _
“Database=your_database_name;” & _

your_sqlserver_name = Name of the SQL Server instance.

your_database_name = The name of your database, such as Customer_Orders.

username = the username you have identified in the database, or the default ‘Admin’ username.

password = the password you have setup in the database or nothing  “Pwd=;” for no password.

‘————–  here we use the connection

sql = “select sales_rep, dollars from sales_by_rep_chart”

Set rst = Server.CreateObject(“ADODB.Recordset”)
rst.Open sql, conn, 3, 3
Set Session(“mysession“) = rst
response.write rst!sales_rep


Where mysession is any name for the session.

When done with the recordset don’t forget to close it:

set conn=nothing