Database Access with ADO

ADO (ActiveX Data Objects) provides a high-level interface for accessing data from ASP pages. This code sample demonstrates common database operations.

Connection and Query

<%@ Language="VBScript" %>
<%
Option Explicit

Dim conn, rs, strSQL

' Open connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=SQLSERVER01;" & _
    "Initial Catalog=FlamenetDB;Integrated Security=SSPI;"

' Execute a query
strSQL = "SELECT TOP 10 UserID, Username, Email, CreatedDate " & _
    "FROM Users WHERE IsActive = 1 ORDER BY CreatedDate DESC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, conn, 3, 1  ' adOpenStatic, adLockReadOnly
%>

<table border="1" cellpadding="4">
  <tr>
    <th>User ID</th>
    <th>Username</th>
    <th>Email</th>
    <th>Joined</th>
  </tr>
  <% Do While Not rs.EOF %>
  <tr>
    <td><%= rs("UserID") %></td>
    <td><%= Server.HTMLEncode(rs("Username")) %></td>
    <td><%= Server.HTMLEncode(rs("Email")) %></td>
    <td><%= FormatDateTime(rs("CreatedDate"), 2) %></td>
  </tr>
  <% rs.MoveNext : Loop %>
</table>

<%
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
%>

Parameterized Query (Preventing SQL Injection)

<%
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1  ' adCmdText
cmd.CommandText = "SELECT * FROM Users WHERE Username = ? AND IsActive = 1"
cmd.Parameters.Append cmd.CreateParameter("@user", 200, 1, 50, strUsername)

Set rs = cmd.Execute()
If Not rs.EOF Then
    Response.Write "Found user: " & Server.HTMLEncode(rs("Username"))
Else
    Response.Write "User not found."
End If

rs.Close : Set rs = Nothing
Set cmd = Nothing
%>

Insert with Identity Retrieval

<%
Dim strInsertSQL, rsID, lngNewID
strInsertSQL = "INSERT INTO Posts (Title, Body, AuthorID) " & _
    "VALUES (?, ?, ?); SELECT SCOPE_IDENTITY() AS NewID"

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = strInsertSQL
cmd.Parameters.Append cmd.CreateParameter("@title", 200, 1, 255, strTitle)
cmd.Parameters.Append cmd.CreateParameter("@body", 201, 1, -1, strBody)
cmd.Parameters.Append cmd.CreateParameter("@author", 3, 1, , lngAuthorID)

Set rsID = cmd.Execute()
Set rsID = rsID.NextRecordset  ' Skip to the SELECT result
lngNewID = rsID("NewID")
Response.Write "New post created with ID: " & lngNewID
%>