SELECT & WHERE Query to collect records in MSSQL table

Displaying records using SELECT & WHERE Query in MSSQL

We can collect records from MSSQL database tables by using SELECT query. Using this query with different combinations we can display records as per our requirement. 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. Let us first learn how to use the basic SELECT query to collect all the records from a table. Here now we are trying to collect all the columns ( fields ) from the table so here is the query.

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

We can add more conditions to our sql statement by using AND combination. 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.