SmartSurat.com # Smart Education Zone

The RecordSet Object

The following sample provides an example of the use of these properties:

<%
Set dbTest = server.Create0bject("ADODB.Connection")
dbPath="DRIVER={Microsoft Access Driver (*.MDB)}; DBQ=" & server.MapPath ("Test.mdb )
dbTest.Open dbPath
Set rstMyCursor = server.CreateObject("ADODB.Recordset")
SQLString = "SELECT * FROM tblMyTable"
rstMyCursor.Open SQLString, dbTest, 3 ' rstMyCursor is a Keyset cursor

intPageSize = Cint(Request.Form("txtPageSize"))

If intPageSize = 0 Then
ntPageSize = 1
End If

rstMyCursor.PageSize = intPageSize

Select Case Request.Form("btnNavigate")
            Case "Previous"
                         If Session("intPage") > 1 Then
                                 Session("intPage") = Session("intPage") -1
                         Else

                                  Session ( "intPage" ) = 1
                         End If
            Case "Next"
                         If rstMyCursor.AbsolutePage < rstMyCursor.PageCount Then
                               Session("intPage") = Session("intPage") + 1
                         Else
                               Session("intPage") rstMyCursor.PageCount

                         End If
            Case Else
                               Session("intPage") =1
            End Select

rstMyCursor.AbsolutePage =Session("intPage")

%>
<HTML>
<HEAD>

<TITLE>Using Pages In Recordsets</TITLE>

</HEAD>
<BODY>

Page Count : <%= rstMyCursor.PageCount %> <BR>

Record Count : <%= rstMyCursor.RecordCount %> <BR>

Current Page : <%= Session("intPage") %> <BR>

<FORM METHOD="post">
Pages size : <INPUT TYPE= "text" SIZE="2" NAME="txtPageSize" VALUE="<%
Response.Write(intPageSize) %> ">
<% If Session("intPage") > 1 Then %>
<INPUT TYPE="submit" NAME="btnNavigate" VALUE="Previous">
<% End If %>
<% If Session("intPage") < rstMyCursor.PageCount Then % >

</FORM>

<% For Each strField In rstMyCursor.Fields % >

</TH>
< % Next % >

</TR>

< % For intCounter = 1 To rstMyCursor.PageSize %>

</TD>
< % Next % >
</TR>

<%
rstMyCursor.MoveNext

If rstMyCursor.EOF Then

Exit For

End If

Next
%>
</TABLE>
</BODY>
</HTML>

Filters

The Filter property of the Recordset Object can filter records from a recordset based on certain criteria. You can use either a criteria constant or a criteria string to filter records. Criteria constants are predefined ways of filtering records, based upon their current status. For example, the records can be filtered to display only edited records, records that have not yet been updated, etc. The criteria string is a user-defined criteria.

The syntax for setting the Filter property is:

rstMyCursor.Filter = [CriteriaConstant I CriteriaString]

The following table lists the predefined criteria constants:

Criteria Constants

Constant	Value	Description
adFilterNone	0	Removes,any filters set.
adFilterPendingRecord	1	Filters records change in batch mode and not yet commetted to the server.
adFilterAffectedRecords	2	Filters records changed with cancelBatch, Delete, Resync or UpdateBatch method
adFilterFetchedRecords	3	Filters Records in local memory from the last databast fetch

The criteria string can be any comparison expression on the recordset. The following example filters a recordset of employers for managers whose salary is greater than 10,000.

rstMyCursor.Filter = "EmpDesagnation= 'Manager' AND EmpSalary > 10000"

Data Manipulation

Data manipulation is extremely simple with ADO. The Recordset Object handles most situations very wisely, hence avoiding errors. For example, if you change values in the current record and move to the next record without updating the current one first, the Recordset Object automatically calls the Update method before moving.

Editing Records

To edit a value in a recordset simply assign it a new value. This value can be any valid expression that returns the data type of the field.

rstMyCursor("FieldName") = Value

Adding Records

The AddNew method of the Recordset Object is used to add records to the recordset. The method is optionally passed a list of fields and their respective values, much like the SQL Insert Into statement. If these parameters are passed, invoking the Update method is not required after adding the record. If these parameters are not passed, you can assign values to each field using the same syntax as when editing the record. In this case, the Edi tMode property of the Recordset Object is set to adEdi tAdd. This does not change until the buffer is added to the database (by updating it).

rstMyCursor.AddNew Fields, Values

Deleting Records

The Delete method can delete a set of records.

The syntax for deleting records is:

rstMyCursor.Delete AffectRecords

The optional AffectRecords argument is a constant that defines which records should be affected by the Delete method. By default, the current record is deleted (adAffeetCurrent). The current filtered set can be deleted by using
The criteria string can be any comparison expression on the recordset. The following example filters a recordset of employees for managers whose salary is greater than 10,000. adAffectGroup. The deleted record remains the current record even after deletion. Hence, before attempting to read from or write to the recordset after a Delete method, you must move the record pointer or an error will be raised.

Updating Records

The Update method saves any changes made to the current record. The syntax is similar to that of the AddNew method:

rstMyCursor.Update Fields, Values

The UpdateBatch method can update records in groups. Hence, you can modify all records that you wish to and then update all of them at once.

rstMyCursor.UpdateBatch AffectedRecords

The optional AffectedRecords constant takes the same values as that of the Delete method. One additional value that it can take is adAf fectAll, which updates all records.
This brings us to the end of the Recordset Object and pretty much to the end of the book, too. You now know how to use the Recordset Object to access and manipulate data from the database. Using this in conjunction with the Connection and Command Objects and with the rest of the knowledge of VBScript you have gained through this book, you will be able to create sophisticated dynamic Web sites.

ASP SCRIPTING