Working with Recordsets

For demonstration purposes only! Please be aware of SQL Injection, and techniques to avoid.

In order to read information from a Datasource, you need to open a 'Recordset' - a set of database records based on some type of criteria, either all of the records in a table or those matching some condition or set of conditions.

To create a recordset containing all of the records in one table of the database, without sorting them:

<%
strSQLQuery = "SELECT * FROM tablename"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

To create a recordset containing only those records in one table where the field 'Name' consists of only the word 'Fred':

<%
strSQLQuery = "SELECT * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

To create a recordset containing all of the records in one table and sort them alphabetically based on the field Name starting at 'a':

<%
strSQLQuery = "SELECT * FROM tablename ORDER BY Name ASC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

To create a recordset containing all of the records in one table and sort them alphabetically based on the field 'Name' starting at 'z':

<%
strSQLQuery = "SELECT * FROM tablename ORDER BY Name DESC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

If you want to be able to be sure of selecting only one particular record from a database, each table should have a 'Unique Key' field - usually a simple auto-incrementing number field. You can then select, update or delete records in the database using this field as the criteria.

<%
strSQLQuery = "SELECT * FROM tablename WHERE RecordID = 15"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

To retrieve the values of various fields in a Recordset use:

<%
strValue = rs("FieldName")
%>

strValue can of course be any variable name, FieldName is the field name in the table that makes up the recordset. Multiple recordsets can be opened for the same database and/or table - just change the 'rs' to whatever name you want to give to each recordset.

Standard SQL queries can be used to update, add or delete records, or create recordsets based on conditions. For example:

<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred' AND Address = 'Smith St'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

<%
strSQLQuery = "DELETE * FROM tablename WHERE Name = 'Fred' OR Name = 'John'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

<%
strSQLQuery = "UPDATE tablename SET FieldName1 = " & strValue1 & " WHERE FieldName2 = " & strValue2 & ";"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

<%
strSQLQuery = "INSERT INTO tablename (FieldName1, FieldName2) VALUES (" & strValue1 & ", " & strValue2 & ")"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3
%>

recordset select update query insert delete SQL ADO ASP