1 / 15

Programming the RecordSet Object

Programming the RecordSet Object. Recordset Object (ADO). When you use ADO, you manipulate data almost entirely using Recordset objects. A Recordset object represents the entire set of records from a database table or the results of an executed command.

fynn
Télécharger la présentation

Programming the RecordSet Object

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. Programming the RecordSet Object

  2. Recordset Object (ADO) • When you use ADO, you manipulate data almost entirely using Recordset objects. • A Recordset object represents the entire set of records from a database table or the results of an executed command. • All Recordset objects are constructed using records (rows) and fields (columns).

  3. Cursor • A cursor caches data on a client. • Provides synchronization mechanisms • Provides tools to minimize network traffic. • Define how a cursor is created using the CursorLocation and CursorType properties pertaining to the Recordset object. • You must set these properties before opening the recordset.

  4. The Recordset CursorLocation Property • Specifies the location of the cursor engine and can be set to one of two constants: • adUseClient • adUseServer • default

  5. The Recordset CursorType Property • The CursorType property defines the kind of cursor that will be opened. • adOpenStatic creates a static cursor • adOpenForwardOnly creates a forward only cursor • adOpenDynamic creates a dynamic cursor • adOpenKeyset creates a keyset cursor

  6. The Recordset LockType Property • To resolve the conflicts that may arise when two users try to change the same records, database providers support locking. • To specify the type of locking, set the LockType property to one of the following: • adLockReadOnly (default) • adLockPessimistic • adLockOptimistic

  7. The Recordset Open Method • The Open method opens a cursor (recordset). • Syntax: recordset.Open Source

  8. Private rstCurrent As New ADODB.Recordset Private Sub Form_Load() Dim conMagnet As New ADODB.Connection Dim cmdMagnet As New ADODB.Command conMagnet.Mode = adModeShareDenyNone conMagnet.Provider = "Microsoft.Jet.OLEDB.4.0" conMagnet.ConnectionString = "Data Source=A:\Magnet.mdb" conMagnet.Open Set cmdMagnet.ActiveConnection = conMagnet cmdMagnet.CommandType = adCmdTable cmdMagnet.CommandText = "tblOrderMaster" rstCurrent.LockType = adLockOptimistic rstCurrent.CursorLocation = adUseClient rstCurrent.CursorType = adOpenKeyset rstCurrent.Open cmdMagnet Call LoadCurrentRecord End Sub

  9. Recordset Navigation Methods • Navigate through the records in a Recordset with the following Recordset methods • MoveFirst • MoveLast • MoveNext • MovePrevious • Move • Examples: rstCurrent.Move +5 rstCurrent.MoveFirst rstCurrent.MoveLast

  10. RecordsetBOF and EOF Properties • The BOF and EOF properties indicate the beginning or end of the Recordset. • The BOF or EOF property is True when you move one record past the first or last record. • If both BOF and EOF are True, there are no records in the Recordset. • Example: If rstCurrent.BOF = True Then rstCurrent.MoveFirst EndIf

  11. RecordsetRecordCount Property • Use the RecordCount property to return the number of records in a Recordset object. • The property returns -1 when ADO cannot determine the number of records. • Reading the RecordCount property on a closed Recordset causes an error. • Example: txtRecordCount.Text = rstCurrent.RecordCount

  12. RecordsetAbsolutePosition Property • Use the AbsolutePosition property to determine the current record number • The AbsolutePosition property is a Long Integer between 1 and the number of records in the Recordset. • Example: txtCurrentRecord.Text = rstCurrent.AbsolutePosition

  13. Recordset Find Method recordsetName.Find (criteria) • Searches a Recordset for the record that satisfies the specified criteria. • If the criteria is met, the recordset position is set on the found record; otherwise, the position is set on the end of the recordset. • Example: rstCurrent.Find “[Member ID] = 1234”

  14. Examples Dim strName As String strName = "John Smith" rstCurrent.Find "fldID = 123" rstCurrent.Find "fldName = 'John Smith'" rstCurrent.Find "fldName = '" & strName & "'"

  15. Private Sub cboMemberID_Click() Dim strTemp As String strTemp = cboMemberID.Text rstCurrent.MoveFirst rstCurrent.Find "[Member ID] = '" & strTemp & "'" Call LoadCurrentRecord End Sub

More Related