SQL Server SELECT and WHERE queries 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.

Filter and order rows

SQL example

 CopySELECT id, name, class, mark
FROM dbo.student
WHERE class = 'Four' AND mark >= 50
ORDER BY mark DESC, id;

A simple combination of SELECT, WHERE, AND, and ORDER BY. Use a parameterized ADO command when a condition comes from user input.

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

 Copy<%
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

 CopyCREATE 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.

Use query parameters for user input

Static SQL examples demonstrate syntax. When using values from Request.Form or Request.QueryString, replace string concatenation with ADODB.Command parameters. SQL Server ADO with OLE DB generally binds ? placeholders by order.

Destructive queries need care

The DELETE and DROP examples below are SQL statements, not commands to run automatically on a live application. Test them against a disposable database and use an explicit, validated WHERE clause when deleting individual 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: