SELECT & WHERE Query to collect records in MSSQL table

We can collect records from MSSQL database tables by using SELECT query. We can even apply SELECT query to more than one table and retrieve linked records from the table.

We will focus on how to use the query in different combinations and you can read all details about record set object, connection strings etc here.

rs1.open " select * from student   ", conn

The above command will collect all the records for the table. But this is not a efficient way to display. Let us try to display all records of class four using one where clause. Here is the query.

rs1.open " select  * from student  WHERE class='four' " , conn

Using AND

Here is the query to collect records of class four and who secured mark more than 50

rs1.open " select  * from student  WHERE class='four' and mark > 50 " , conn

Before we move further let us learn the full code where the records taken from database will be displayed inside an html table.

<%
Dim conn,rs,rs1,SQL,RecsAffected,qr,bgcolor

Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs1 =Server.CreateObject("ADODB.Recordset")

rs1.open " select  * from student  WHERE class='four' " , conn

Response.Write "<table>"

Do While Not rs1.EOF 
if(bgcolor="#f1f1f1") then 
bgcolor="#ffffff"
Else
bgcolor="#f1f1f1"
End if 
Response.Write "<tr bgcolor=" & bgcolor & "><td> " _
& rs1("name") & " </td><td> " & rs1("class") & " </td><td> " _
& rs1("mark") & " </td><td> " & rs1("sex") & "</td></tr> "

rs1.MoveNext
Loop
Response.Write "</table>"
Set rs1 = Nothing

conn.Close
Set conn = Nothing
%>

You can read more about SELECT query in our SQL section
Retrieving all the records is not a good idea so let us learn different ways to collect part of the records.
You can create the student table by using this query

CREATE TABLE [dbo].[student] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[class] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[mark] [int] NOT NULL ,
	[sex] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]

You can download the csv file for all student table data by suing this student.csv file.

Delete records from table in MSSQL database with where condition

To delete all records we can use simple delete command like this

Delete from members

This will remove all the records of members table of MSSQL database. We can also add some conditions to the query by adding a Where clause to delete a perticular record like this.

delete from member where userid ='admin2'

Random record from MSSQL table by using TOP & NEWID()

We can get random records from MSSQL tables by using TOP and NEWID() functions. Here is the SQL to get one random record each time the query is executed.

select top 1 * from member order by NEWID()

To get more than one ( say 3 ) records here is the query.

select top 3 * from member order by NEWID()

Using TOP

We can collect some records from a large number or records of a MSSQL table by using TOP clause in our query. We have to specify the field where we want to apply the TOP command and number of records we want to retrieve.

Using this command we can collect first n records ( say 5 here ) from the student table. Here is the query.

rs1.open " select  TOP 5 class, name,sex,mark  from student   " , conn

Note that the above query is to be applied to the script we have written in our SQL select section. You have to replace the above query line in appropriate place. ( we are not repeating the same script again here ) .

The command TOP only searches for 5 records and it does not go in any order. Let us try to collect to 10 students based on the mark they have secured. Here we will use another sql command ORDER BY ( Read more on ORDER BY command in our SQL section ) . Here is the command.

rs1.open " select  TOP 5 class, name,sex,mark  from student  ORDER BY mark " , conn

The above query will return 5 lowest mark records. By default the order by clause return in ascending order so to change that we have to add DESC to the query to get top 5 rank holders in student table.

rs1.open " select  TOP 5 class, name,sex,mark  from student  ORDER BY mark DESC " , conn

You can read more on order by command in our sql section.

Pattern Matching in a query by using LIKE command

We can use LIKE command inside a query with SELECT & WHERE combination to get records with some string matching. This is of great use while we are developing a search query or any other type of application where we expect to get a matching pattern to the query.

LIKE command is used for MySQL in our sql section.

Here is the query we designed to collect all the records from student table starting with letter J

rs1.open " select  * from student  WHERE name LIKE 'J%'   " , conn

Now let us find out the records ending with a single char e

rs1.open " select  * from student  WHERE name LIKE '%e'   " , conn

Now let us find out the records having two chars inside their name any where.

rs1.open " select  * from student  WHERE name LIKE '%ff%'   " , conn

This way we can use wildcard to get the required pattern combinations in our query. Now let us try to match underscore ( _ ) as a replacement for a single position.

rs1.open " select  * from student   where mark like '5_' " , conn

The above query will return all the records with mark is between 50 and 59 ( both ends inclusive ) . Here we have specified that the first digit should be 5 and second digit can be any thing. We can sue underscore for more than one place also.

Counting the records in a MSSQL Table

It is very often required to know the total records present in a table or number of records matching to our query. There is a built in RecordCount method of the RecordSet object. To use this RecordCount method in our MSSQL table we have to use scrollable cursor. If we are not using a scrollable cursor and using a forward-only cursor then RecordCount method will return -1.

There is a line opening the cursor in forward only mode.

rs1.open " select  * from student   " , conn

Change this line to

rs1.open " select  * from student   " , conn , adOpenKeySet

Now we can add the line to display the total number of records before the Do while loop line this

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

Using Count SQL command to know the total records.

Now you must have understood the purpose of using scrollable cursor to count the records. We can use the command COUNT ( read more on SQL COUNT Here )to get the total records present in our query or table. This command is a SQL part so no need to use any scrollable cursor. Here is a simple query to get the total number of records present.

rs1.open " select  count(*) as no from student   " , conn 

Response.Write "Total " & rs1("no") & " Records"

However Count command can be used along with GROUP BY command to develop powerful reports. We will discuss that in our next tutorial.

Group by query in MSSQL Table

In our student database we have seen how count query works. Now we will discuss how to display total number of records based on GROUP By SQL command. You can read more on GROUP BY command here.

Let us try to display total number of students in each class. Here we will display class name and number of students in the class.


rs1.open " select  COUNT(class) as no,class from student group by class  " , conn

Within the Do While loop we will keep this code to display the data.

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

GROUP BY command with SUM

We can find out total mark of students in each class by using SUM and GROUP BY sql commands. Here is the query and display part

rs1.open " select  SUM(mark) as no,class FROM student GROUP BY class  " , conn

GROUP BY command with AVG

Same way we can get the average mark in each class by using AVG command with GROUP BY

rs1.open " select  AVG(mark) as no,class FROM student GROUP BY class  " , conn

GROUP BY command with MAX and MIN

Same way we can find out the highest mark in each class by using MAX command and the minimun mark by using MIN query

rs1.open " select  MAX(mark) as no,class FROM student GROUP BY class  " , conn 

rs1.open " select  MIN(mark) as no,class FROM student GROUP BY class  " , conn

SELECT WHERE Query with AND OR & NOT combinations

We will try to add AND and OR combination to our SELECT query.

You can also read how AND and OR is used in SQL script for MySQL tables

Let us say we are interested in getting records of class Five only.

Here is the query.

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

Now we will collect records of the student of class five who has secured more than 80 marks.

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

Now let us try to collect all the records who have secured more than 80 marks. But we will add one more condition . Along with all these records we also want the records of class Four. Here is the query

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

Note how we have added two conditions with one OR.

Now let us add one NOT combination to get all the student records who has secured more than 80 mark but we don't want student of class Four.

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

Delete table in MSSQL database

We can delete a table from MSSQL database by using DROP table command ( Query ) .

You can test this query in your Microsoft SQL Server Management Studio

drop table member

The above command will delete the table member from the database. What happens we run the same query again. You will get an error message like this

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Cannot drop the table 'dbo.member', because it does not exist or you do not have permission.
signup/sql.asp, line 25

How to check the existence of the table before deleting to avoid the error message ? We will modify our query and add the command IF EXISTS to it.

if exists (SELECT * FROM sysobjects WHERE name = 'member')drop table member

We can also search INFORMATION_SCHEMA to check if table exists or not.

if exists (select * from INFORMATION_SCHEMA.TABLES where table_name ='member') drop table member

Connecting MSSQL Matching record New Table Display
Identity Inserted record ID Row count
ASP Home

Displaying records using SELECT & WHERE Query in MSSQL