Displaying SQL Server records in Classic ASP

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Render a result row safely

VBScript example

 CopySet rs = cn.Execute("SELECT id, name FROM dbo.student ORDER BY id")
Do While Not rs.EOF
  Response.Write Server.HTMLEncode(CStr(rs("name"))) & "<br>"
  rs.MoveNext
Loop
rs.Close

This Classic ASP fragment requires an open SQL Server ADO connection cn. The original complete example below includes connection and cleanup steps.

Displaying SQL Server rows follows the same ADO Recordset pattern as Access: open a connection, execute a SELECT statement, loop until EOF, encode the values for HTML and release database objects.

Complete display example

 Copy<%@ Language="VBScript" %>
<% Option Explicit %>
<!-- #include virtual="/adovbs.inc" -->
<!-- #include virtual="/conn.asp" -->
<%
Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs = conn.Execute("SELECT issue_dt FROM dt ORDER BY issue_dt")
Do While Not rs.EOF
Response.Write Server.HTMLEncode(CStr(rs("issue_dt"))) & "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>

Connection.Execute() is convenient for a fixed read-only query. If the query contains visitor-controlled values, create an ADODB.Command and add parameters instead.

Prepare the query safely

Use a fixed SQL query for simple listing pages. If the query filters by a visitor value, use a parameterized ADODB.Command and validate its input.

Prevent broken HTML

Encode dynamic field values with Server.HTMLEncode(), balance opening and closing table tags, and handle empty results before showing column headings or rows.

Frequently asked questions

Are these queries for SQL Server or Access?

The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.

Can I execute these statements directly on a live database?

Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.

How should Classic ASP send input to a query?

Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

Back to top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: