SQL Server SELECT and WHERE queries in Classic ASP

This page collects the original SQL Server SELECT/WHERE examples used by the Classic ASP tutorial and updates their syntax, ordering and safety notes. Fixed tutorial values are shown inline; values that come from visitors should be passed through an ADODB.Command parameter.

SELECT rows

rs1.open "SELECT id, name, class, mark, sex FROM student ORDER BY id", conn

Use explicit column lists in production code when you know which fields the page needs.

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE class='Four' ORDER BY id", conn

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE class='Four' AND mark > 50 ORDER BY mark DESC", conn

Display a filtered Recordset

<%
Dim conn, rs1, bgcolor
Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs1 = Server.CreateObject("ADODB.Recordset")

rs1.Open "SELECT name, class, mark, sex FROM student WHERE class='Four' ORDER BY id", conn

Response.Write "<table>"
Do While Not rs1.EOF
  If bgcolor = "#f1f1f1" Then
    bgcolor = "#ffffff"
  Else
    bgcolor = "#f1f1f1"
  End If
  Response.Write "<tr style=""background:" & bgcolor & """><td>" & _
    Server.HTMLEncode(CStr(rs1("name"))) & "</td><td>" & _
    Server.HTMLEncode(CStr(rs1("class"))) & "</td><td>" & _
    Server.HTMLEncode(CStr(rs1("mark"))) & "</td><td>" & _
    Server.HTMLEncode(CStr(rs1("sex"))) & "</td></tr>"
  rs1.MoveNext
Loop
Response.Write "</table>"

rs1.Close
Set rs1 = Nothing
conn.Close
Set conn = Nothing
%>

Sample student table

CREATE TABLE dbo.student (
  id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_student PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(20) NOT NULL,
  mark INT NOT NULL,
  sex VARCHAR(20) NULL
);

The original student.csv practice data remains available.

DELETE rows

DELETE FROM members;

DELETE FROM member WHERE userid = 'admin2';

Random rows with TOP and NEWID()

SELECT TOP (1) * FROM member ORDER BY NEWID();

SELECT TOP (3) * FROM member ORDER BY NEWID();

TOP with deterministic ordering

rs1.open "SELECT TOP (5) class, name, sex, mark FROM student ORDER BY id", conn

rs1.open "SELECT TOP (5) class, name, sex, mark FROM student ORDER BY mark, id", conn

rs1.open "SELECT TOP (5) class, name, sex, mark FROM student ORDER BY mark DESC, id", conn

LIKE pattern matching

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE name LIKE 'J%'", conn

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE name LIKE '%e'", conn

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE name LIKE '%ff%'", conn

The old numeric LIKE '5_' example is clearer as a numeric range:

rs1.open "SELECT id, name, class, mark, sex FROM student WHERE mark BETWEEN 50 AND 59", conn

RecordCount and COUNT(*)

rs1.open "SELECT id, name, class, mark, sex FROM student ORDER BY id", conn

rs1.open "SELECT id, name, class, mark, sex FROM student ORDER BY id", conn, adOpenKeySet

Response.Write "Total " & rs1.RecordCount & " Records"

rs1.open "SELECT COUNT(*) AS no FROM student", conn
Response.Write "Total " & rs1("no") & " Records"

RecordCount depends on cursor/provider support. If you only need the number of rows, COUNT(*) is normally clearer.

GROUP BY with aggregates

rs1.open "SELECT class, COUNT(*) AS no FROM student GROUP BY class ORDER BY class", conn

Response.Write rs1("class") & rs1("no") & "<br>"

rs1.open "SELECT class, SUM(mark) AS total_mark FROM student GROUP BY class ORDER BY class", conn

rs1.open "SELECT class, AVG(mark) AS avg_mark FROM student GROUP BY class ORDER BY class", conn

rs1.open "SELECT class, MAX(mark) AS max_mark FROM student GROUP BY class ORDER BY class", conn
rs2.open "SELECT class, MIN(mark) AS min_mark FROM student GROUP BY class ORDER BY class", conn

AND, OR and NOT conditions

rs1.open " SELECT  * FROM student  WHERE class='Five'  " , conn

rs1.open " SELECT  * FROM student  WHERE class='Five' AND mark > 80 " , conn

rs1.open " SELECT  * FROM student  WHERE class='Four' OR mark > 80 " , conn

rs1.open " SELECT  * FROM student  WHERE NOT(class='Four') AND mark > 80 " , conn

DROP TABLE safely

DROP TABLE dbo.member;

SQL Server error: Cannot drop table 'dbo.member' because it does not exist, you do not have permission, or another object depends on it.

DROP TABLE IF EXISTS dbo.member;

IF OBJECT_ID(N'dbo.member', N'U') IS NOT NULL
  DROP TABLE dbo.member;

DROP TABLE IF EXISTS is available in modern SQL Server. The OBJECT_ID() pattern remains useful when maintaining older SQL Server-compatible scripts.

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: