Transcript Cursors, Locks, Editing

INT213
Updating the Database
Contents
Cursors
 Locking
 Updating databases

2
Cursors



So far, we have stepped through a Recordset in the
forward direction one record at a time
This technique uses the simplest cursor, the forwardLooking-Only cursor
A cursor is simply a pointer that indicates your current
location in the Recordset
3
Cursors


A cursor points to a specific
row
Different types of cursors
can





Row 1
cursor
Row 2
Move forward
Move backward
Move by several rows
A cursor cannot

BOF
Move before the first record
(Beginning Of File)
Move after the last record
(End Of File)
Row n
EOF
4
Cursor Types
Cursor
Constant
Value Description
Forward only
adOpenForwardOnly
0
Moves forward only, one by one.
Fastest performing cursortype, and
also the most limited. The forwardonly cursor does not support the
RecordCount property, and does not
support the MovePrevious methods of
the recordset object.
Static
adOpenStatic
3
The result is not updated when
changes are made to the database
Keyset
adOpenKeyset
1
Not applicable to this course
Dynamic
adOpenDynamic
2
This reevaluates the query each time
a new row is requested. Thus, new
rows added are detected.
5
Scrollable Cursors



All cursors, except forward-only, are scrollable
This means they can move in more than just the
forward direction
Scrollable cursors support the methods
– move to the first record
 MoveLast – move to the last record
 MoveNext – move to the next record
 MovePrevious – move to the previous record
 Move n – move n records forward or back
 MoveFirst
6
Selecting a Cursor

The cursor is selected via one of the parameters used
when opening the RecordSet
RecordSet.Open
source, (sql statement, stored procedure or table name)
connection, (connection string or connection object)
cursorType, (How to move through RecordSet)
LockType, (Whether to read or write to a table)
commandType (describes the source)
7
Selecting a Cursor

To create a static cursor
set RS = Server.CreateObject ("ADODB.Recordset")
query = "SELECT * FROM Person ORDER BY lastName"
RS.Open query, objConn, adOpenStatic

To output a RecordSet in reverse
rs.MoveLast
Do While Not RS.BOF
Response.Write RS("firstName")
RS("lastName") & "<br>"
RS.MovePrevious
Loop
& " " &
8
Adovbs.inc



So far we have defined the constants for the cursor
types ourselves
These are actually in a file called adovbs.inc
You can include this in your scripts
 <!--#include virtual=“adovbs.inc”-->
 <!--#include file=“adovbs.inc”-->
9
Counting Records

How do you determine the number of records in
a RecordSet?
 Have
a loop Move through the RecordSet and count
them
 Use the RecordCount property of a scrollable cursor
 Response.Write "The number of names is "
& RS.RecordCount & "<br>"
10
Locking




When two people try to update a record at the same
time, confusion and errors result
To avoid this problem, VBScript can be configured to
lock the records
This means that one person locks the record and
updates it
The other person cannot read or update the record until
the first person releases their lock
11
Lock Types
Lock
Constant
Description
Read-only
adLockReadOnly
Records are read-only and
cannot be modified or added.
Default lock type.
Pessimistic adLockPessimistic
Records are locked as soon as
editing begins.
Optimistic
adLockOptimistic
Locks row only when update is
called. Used when having
multiple users making changes
to the same records is not a
major concern.
Batch
optimistic
adLockBatchOptimistic Outside course scope
12
Syntax for Opening Record Sets

Open a Scrollable, updatable Recordset with parameters
sql = “Select * from students”
Rs.open sql, conn, adOpenStatic, adLockOptimistic, adCmdText

Open a RecordSet after setting the properties
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.LockType = adLockOptimistic
rs.CursorType = adOpenStatic
rs.Open sql, conn
* adCmdText – indicates that the source holds command text, such as an
SQL command
13
Adding Records

If you call
 RS.AddNew
 It
will create a new, blank record and make it the
current record
 You can then assign values to the fields
 Finally, call
 RS.Update to write the changes to the DB
14
Adding Records
RS.AddNew
RS(“firstName”) = “Fred”
RS(“lastName”) = “Flintstone”
RS.Update
15
Updating Records

To update an existing record
 Get the record from the database
 Modify the field(s)
 Call Update or CancelUpdate
RS(“firstName”) = “Frederick”
RS.Update
16
Deleting Records

You can delete an existing record by
 Find the record you want to delete (sql query)
 Call RS.Delete
RS.MoveFirst
RS.Delete
17