To retrieve data from a database in ASP, you'll need to follow these basic steps:
These fundamental steps form the backbone of any database operation in Classic ASP. Let's look at a practical example that retrieves product information from the Northwind database.
The following example shows how to retrieve all product names and their prices from the Products table, ordered by product name, and display them to the client:
<%@ Language=VBScript %>
<%
Option Explicit
Response.Expires = 0
Dim objConn, objRS, strQuery
Dim strConnection
' Create connection object
Set objConn = Server.CreateObject("ADODB.Connection")
' Build connection string
strConnection = "DSN=Northwind;Database=Northwind;"
strConnection = strConnection & "UID=sa;PWD=;"
' Open the connection
objConn.Open strConnection
' Build SQL query
strQuery = "SELECT ProductName, UnitPrice FROM Products "
strQuery = strQuery & "ORDER BY ProductName"
' Execute query and get recordset
Set objRS = objConn.Execute(strQuery)
%>
<HTML>
<HEAD>
<TITLE>Northwind Products</TITLE>
</HEAD>
<BODY>
<H2>All products stored in the Products table, ordered by product name:</H2>
<%
' Loop through recordset
While Not objRS.EOF
Response.Write objRS("ProductName") & " ("
Response.Write FormatCurrency(objRS("UnitPrice")) & ")<BR>"
objRS.MoveNext
Wend
' Clean up
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>
</BODY>
</HTML>Let's break down each section of this code:
Lines 1-3: The script instructs the ASP engine to use VBScript as the default language and ensures that every variable is declared using Option Explicit. This helps catch typos and undeclared variables.
Line 4: Sets the page expiration to 0, ensuring the browser doesn't cache the page. Without this line, the browser would cache the page and clients would see outdated data if the database content changed between page calls.
Lines 5-6: Declare the variables we'll use for the connection object, recordset object, query string, and connection string.
Lines 7-10: Create an ADO Connection object and open a connection to the Northwind database using a Data Source Name (DSN).
Lines 11-13: Build and execute a SQL query that selects product names and prices, ordered alphabetically.
Lines 20-24: Loop through the recordset, displaying each product name and its formatted price.
Lines 26-29: Clean up by closing the recordset and connection, then setting them to Nothing to free memory.
Instead of using a DSN, you can use a connection string directly. This is more portable and doesn't require configuring a DSN on the server:
<%
Dim objConn, objRS
Dim strConnection, strQuery
Set objConn = Server.CreateObject("ADODB.Connection")
' Connection string without DSN (SQL Server)
strConnection = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
' For Access database
' strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & Server.MapPath("Northwind.mdb")
objConn.Open strConnection
strQuery = "SELECT ProductName, UnitPrice, UnitsInStock FROM Products " & _
"WHERE UnitsInStock > 0 ORDER BY ProductName"
Set objRS = objConn.Execute(strQuery)
%>For more control over your data access, you can create a Recordset object separately:
<%
Dim objConn, objRS
Dim strConnection, strQuery
' Create and open connection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
objConn.Open strConnection
' Create recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
' Build query
strQuery = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock " & _
"FROM Products ORDER BY ProductName"
' Open recordset with specific cursor and lock types
objRS.Open strQuery, objConn, 3, 1 ' adOpenStatic, adLockReadOnly
%>
<HTML>
<BODY>
<TABLE BORDER="1" CELLPADDING="5" CELLSPACING="0">
<TR>
<TH>Product ID</TH>
<TH>Product Name</TH>
<TH>Unit Price</TH>
<TH>Units in Stock</TH>
</TR>
<%
While Not objRS.EOF
%>
<TR>
<TD><%= objRS("ProductID") %></TD>
<TD><%= objRS("ProductName") %></TD>
<TD><%= FormatCurrency(objRS("UnitPrice")) %></TD>
<TD><%= objRS("UnitsInStock") %></TD>
</TR>
<%
objRS.MoveNext
Wend
%>
</TABLE>
<P>Total records: <%= objRS.RecordCount %></P>
<%
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>
</BODY>
</HTML>You can filter data based on user input or specific criteria:
<%
Dim objConn, objRS
Dim strConnection, strQuery
Dim minPrice
' Get minimum price from query string
minPrice = Request.QueryString("minPrice")
If Not IsNumeric(minPrice) Then minPrice = 0
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
objConn.Open strConnection
' Build parameterized query
strQuery = "SELECT ProductName, UnitPrice, CategoryID FROM Products " & _
"WHERE UnitPrice >= " & CDbl(minPrice) & " " & _
"ORDER BY UnitPrice DESC"
Set objRS = objConn.Execute(strQuery)
%>
<HTML>
<BODY>
<H2>Products with price >= <%= FormatCurrency(minPrice) %></H2>
<%
If objRS.EOF Then
Response.Write "<P>No products found matching your criteria.</P>"
Else
While Not objRS.EOF
Response.Write "<P><B>" & objRS("ProductName") & "</B>: "
Response.Write FormatCurrency(objRS("UnitPrice")) & "</P>"
objRS.MoveNext
Wend
End If
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>
</BODY>
</HTML>For better performance and security, you can use stored procedures:
<%
Dim objConn, objRS, objCmd
Dim strConnection
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
objConn.Open strConnection
' Create command object
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "GetProductsByCategory"
objCmd.CommandType = 4 ' adCmdStoredProc
' Add parameters
objCmd.Parameters.Append objCmd.CreateParameter("@CategoryID", 3, 1, , 1)
' Execute stored procedure
Set objRS = objCmd.Execute
%>
<HTML>
<BODY>
<H2>Products by Category</H2>
<UL>
<%
While Not objRS.EOF
Response.Write "<LI>" & objRS("ProductName") & " - "
Response.Write FormatCurrency(objRS("UnitPrice")) & "</LI>"
objRS.MoveNext
Wend
%>
</UL>
<%
objRS.Close
Set objCmd = Nothing
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
</BODY>
</HTML>In modern ASP.NET, you'd use ADO.NET with similar principles:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
public partial class Products : Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadProducts();
}
}
private void LoadProducts()
{
string connectionString = "Server=ServerName;Database=Northwind;" +
"User Id=sa;Password=yourpassword;";
string query = "SELECT ProductName, UnitPrice FROM Products " +
"ORDER BY ProductName";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string productName = reader["ProductName"].ToString();
decimal unitPrice = Convert.ToDecimal(reader["UnitPrice"]);
Response.Write($"{productName} ({unitPrice:C})<br/>");
}
}
}
}
}
}Using DataGrid or GridView in ASP.NET:
using System.Data;
using System.Data.SqlClient;
protected void LoadProductsGrid()
{
string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
string query = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock " +
"FROM Products ORDER BY ProductName";
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(query, conn))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridViewProducts.DataSource = dt;
GridViewProducts.DataBind();
}
}
}In modern ASP.NET Core, you'd typically use Entity Framework Core:
using Microsoft.EntityFrameworkCore;
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public decimal UnitPrice { get; set; }
public int UnitsInStock { get; set; }
}
public class NorthwindContext : DbContext
{
public DbSet<Product> Products { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=ServerName;Database=Northwind;User Id=sa;Password=yourpassword;");
}
}
// Controller action
public IActionResult Index()
{
using (var context = new NorthwindContext())
{
var products = context.Products
.OrderBy(p => p.ProductName)
.ToList();
return View(products);
}
}Always include error handling in your database code:
<%
On Error Resume Next
Dim objConn, objRS
Dim strConnection, strQuery
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
objConn.Open strConnection
If Err.Number <> 0 Then
Response.Write "<P style='color: red;'>Database connection error: " & Err.Description & "</P>"
Response.End
End If
strQuery = "SELECT ProductName, UnitPrice FROM Products ORDER BY ProductName"
Set objRS = objConn.Execute(strQuery)
If Err.Number <> 0 Then
Response.Write "<P style='color: red;'>Query execution error: " & Err.Description & "</P>"
objConn.Close
Set objConn = Nothing
Response.End
End If
%>
<HTML>
<BODY>
<H2>Product List</H2>
<%
While Not objRS.EOF And Err.Number = 0
Response.Write objRS("ProductName") & " - "
Response.Write FormatCurrency(objRS("UnitPrice")) & "<BR>"
objRS.MoveNext
Wend
If Err.Number <> 0 Then
Response.Write "<P style='color: red;'>Error reading data: " & Err.Description & "</P>"
End If
' Clean up
If Not objRS Is Nothing Then
If objRS.State = 1 Then objRS.Close
Set objRS = Nothing
End If
If Not objConn Is Nothing Then
If objConn.State = 1 Then objConn.Close
Set objConn = Nothing
End If
%>
</BODY>
</HTML>For better performance, use connection pooling by keeping your connection strings consistent:
<%
' Good practice: Store connection string in Application variable
If Application("ConnString") = "" Then
Application.Lock
Application("ConnString") = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
Application.UnLock
End If
' Use the stored connection string
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("ConnString")
' Your database operations here
objConn.Close
Set objConn = Nothing
%>Never include passwords directly in your ASP files. Use include files or application variables:
<!-- db_config.asp -->
<%
Function GetConnectionString()
GetConnectionString = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=Northwind;User ID=sa;Password=yourpassword;"
End Function
%>
<!-- Your main ASP file -->
<!--#include file="db_config.asp"-->
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open GetConnectionString()
' Your code here
objConn.Close
Set objConn = Nothing
%>Accessing the Northwind database involves understanding these fundamental concepts: opening connections, executing queries, reading data, and properly cleaning up resources. Whether you're working with Classic ASP or modern ASP.NET, these principles remain consistent. Always remember to close your connections, handle errors gracefully, and never expose sensitive connection information in your code. By following these best practices, you'll create robust database-driven applications that perform well and remain secure.
The Northwind database is a sample Microsoft Access and SQL Server database used for tutorials and demonstrations. It contains tables for customers, products, orders, and more, making it ideal for learning database operations in ASP and ASP.NET.
In Classic ASP, use ADODB.Connection with a connection string like 'Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=Northwind;User ID=sa;Password=yourpassword;'. Create the object with Server.CreateObject('ADODB.Connection'), open it, and execute queries.
ADODB is COM-based for Classic ASP, using Connection and Recordset objects. ADO.NET is .NET-based for ASP.NET, using SqlConnection, SqlCommand, and SqlDataReader for better performance, type safety, and integration with .NET features.