1 / 17

INT213

INT213. Updating the Database. Contents. Cursors Locking Updating databases. 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 forward-Looking-Only cursor

cynthia-mathis
Download Presentation

INT213

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INT213 Updating the Database

  2. Contents • Cursors • Locking • Updating databases

  3. 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 forward-Looking-Only cursor • A cursor is simply a pointer that indicates your current location in the Recordset

  4. Cursors BOF • A cursor points to a specific row • Different types of cursors can • Move forward • Move backward • Move by several rows • A cursor cannot • Move before the first record (Beginning Of File) • Move after the last record (End Of File) Row 1 cursor Row 2 Row n EOF

  5. Cursor Types

  6. 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 • MoveFirst – 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

  7. 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)

  8. 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

  9. 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”-->

  10. 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>"

  11. 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

  12. Lock Types

  13. 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

  14. 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

  15. Adding Records RS.AddNew RS(“firstName”) = “Fred” RS(“lastName”) = “Flintstone” RS.Update

  16. 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

  17. 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

More Related