Set of records

Set of records

As mentioned earlier, it is not surprising that the recordset is the most commonly used object in ADO. After all, they contain the data. However, there is more to the recordset than you can imagine, and it is important to know how the data is saved and processed, because it provides more references for choosing which recordset to use.
There are different types of recordsets, and in some small places there are differences that can easily lead to errors. The first thing to be seriously talking about is the concept of the cursor.

8.4.1 cursor
The cursor (cursor) is a concept that puzzles many people, but it's actually very simple.
The cursor is used to manage the current position of the recordset and Recordset, which is handled by the current record pointer.
Is this not what the Recordset object does? Yes, but a recordset also relies on its cursor. This still doesn't answer the question of what the cursor is, so first look at a recordset, as shown in table 8-2:

Here are six rows and four columns. Open a Recordset, and the current record is the first record, which is the record for Bob White. What do you use to identify the current record? With the current record pointer. So how do you handle this pointer? When you need to move to the next record or another record, it is done by the cursor. When you access the field of the current row, the cursor knows which row it is in, so you can return the correct value. If you try to move out of the last row of the recordset, the cursor will also handle it.
A good way to understand the cursor is to think of the cursor as a window that can be moved within the recordset. This window is as tall and as long as a single row of a recordset, so you can see only one row of data values at a time. When you move to another record, the window moves along as well.
You might think it's quite simple, but it's really important because what you can do with the cursor is determined by the type of cursor.
1. Cursor type
The type of cursor identifies the functionality that the cursor can provide. Here are four kinds of cursors:
· Static (adOpenStatic). The static cursor contains a static copy of the record. This means that after the recordset is established, the contents of the recordset are fixed. Changes, additions, and deletions to records by other users are not visible. Allows you to move forward and backward through a set of records.
· Only forward shift (ADOPENFORWARDONLY) is allowed. The default cursor type is the same as the static cursor except that it only allows forward movement.
· Dynamic (adopendynamic). The dynamic cursor does not have a fixed set of records. Other user changes, add, or delete operations are visible in the recordset. Allows you to move forward and backward through a set of records.
· Key set (adOpenKeyset). The keyset-type cursor is fixed in addition to the recordset, and the rest is similar to the dynamic cursor. You can see changes from other users, but new records are not visible. If another user deletes a record, the records will become inaccessible in the recordset. This feature is implemented by identifying the key to the recordset, so the key remains, even if the record is changed or deleted.
To understand these concepts, imagine the cursor window again. For a cursor that only moves forward, it can be viewed as a window on a one-way gear, which can only move forwards. The advantage of this feature is that once a record is passed, the cursor will completely forget the record because it will never return to that record. The static cursor removes the one-way gear, allowing for backward movement, because it can also move backwards, and the cursor needs to track the records. For this reason, a static cursor is slower than a cursor that is only allowed to move forward.
For keyset and dynamic type cursors, the window can move backwards and forwards, but what you see may change. The keyset cursor can see other people's changes to the data, but no new or deleted records are visible. Therefore, the recordset is fixed, but not content fixed. The dynamic cursor expands it, not only to change the contents of the record, but also to change the recordset. So you can see a new record appear in the dynamic cursor, and the deleted record disappears from the recordset.
The type of cursor you use depends on what you want to achieve. If you only want to browse the record, perhaps to create a table or a select list, then the cursor with only a forward move is the best. Although the use of other types of cursor speed may be slower, but also can work correctly.
The type of cursor can affect performance, especially the server cursor. For example, in Microsoft SQL Server 6.5, keyset and statically typed cursors require a full copy of the data to be placed in the staging database (tempdb). Where the keyset-type cursor is a little more efficient, because it only copies the key into the staging database. This is not the case for SQL Server 7.0, where different types of cursors run less efficiently.
2. Cursor position
Now that you've explained what the cursor is and how the cursor manages the data, where is the cursor? The answer is not fixed, because the cursor depends on the data store. Some data stores, such as Microsoft's SQL Server, have their own cursor services, while other Microsoft Access has no cursor service.
When you open a recordset, you must choose whether you want the data store management cursor, or you want OLE DB and ADO to manage your cursor locally for you. The latter can be implemented because OLE DB has its own cursor service. You can set these two options by using the Connection object or the CursorLocation property of the Recordset object. You can set the value of this property to:
· adUseServer: Let the data store manage the cursor.
· adUseClient: Let ADO manage the cursor.
You can set this property before opening the connection or Recordset:
Conpubs.cursorlocation = adUseServer
Conpubs.open strconn
Or:
Rsauthors.cursorlocation = adUseClient
Rsauthors.open "Authors", conpubs
The default cursor is server-based, and it is important to understand the differences between the two types. For a server cursor, the task of data storage is to manage records, so when a recordset is built with a server cursor, the data store manages the movement of records, updates to records, and so on.
For a client cursor, the entire contents of the recordset are replicated to the customer, managed by the local client cursor service. This means that for a client cursor, it takes much longer to open a recordset with a large number of records than to open the same recordset with a server-based cursor. You also need to use a client based cursor, and later in this book, you will see more relevant examples when you study the components.
3. "Fire belt" cursor
You may know that the firehose cursor, because it can bring high operational efficiency to the application, it is very important to explain it. Because the "fire belt" cursor is a special type of cursor, it only appears when connected to Microsoft SQL Server. SQL Server creates the data set that the user requests, and then passes the data directly to the customer to get the data as quickly as possible. SQL Server itself has little cursor management, which means it can process data more quickly. That means the data can be quickly returned to the client in a very short period of time. Viewed from the client side, the type is a cursor that is allowed to move forward only.
So, in the previous discussion of the cursor type, why didn't the "fire belt" cursor be involved? Because this type of cursor is dedicated to SQL Server and is used only when using a server-based cursor. This is not a true cursor type, and the way to get a "fire belt" type of cursor is to not specify the type of cursor.

8.4.2 Lock
We have explained the cursor and how to manage the data. Can you now create a recordset? I'm afraid not yet, because there is another problem that is not discussed, that is locking.
Locking is how to ensure the integrity of your data and ensure that changes are not overwritten. The typical situation we need to avoid is multiple updates, such as a user altering some data, and then another user immediately modifies it. To protect this situation, there are many different ways to keep records protected in order to lock records. You can set these methods by locking the type.
Lock type
The lock type determines whether or how the record is locked when the record is updated. There are four types of locks:
· Read-only (adLockReadOnly): The default lock type, the recordset is read-only, and the record cannot be modified.
· Pessimistic (adlockpessimistic): When a record is modified, the data provider attempts to lock the record to ensure that it is successfully edited. Lock the record immediately as soon as the editor starts.
· Optimistic (adLockOptimistic): The record is not locked until the update record is submitted with the Update method.
· Batch optimistic (adLockBatchOptimistic): Allows you to modify multiple records, and only the UpdateBatch method is invoked to lock the record.
When you do not need to change any records, you should use a read-only recordset so that the provider does not have to do any testing. For general use, optimistic locking may be the best option because the record is only locked for a short period of time and the data is updated during this time period. This reduces the use of resources.
Pessimistic locking improves the integrity of the data, but at the expense of concurrency. Concurrency is the ability of many users to access data at the same time. Locked records are not visible to other users, so the concurrency of the data is reduced. Optimistic locking locks records for only a short period of time, thus enhancing the concurrency of the data, while other users are also more likely to modify the data.
Questions about concurrency and locking are well discussed in Microsoft's published "Inside SQL Server 7.0" (author Ron Soukup and Kalen Delaney). This is a very authoritative monograph, so in any case should buy this monograph, the book has a lot of valuable information.
8.4.3 Create a recordset
It is easy to create a recordset by calling the Recordset object's Open method:
Recordset.Open [Source], [ActiveConnection], [CursorType], [LockType], [Options]
Its parameters and descriptions are shown in table 8-3:
Table 8-3 the parameters and description of the Open method

For example, to open a recordset on the authors table in the database pubs:
Dim rsauthors

Set rsauthors = Server.CreateObject ("ADODB. Recordset ")

Rsauthors.open "Authors", strconn

' Do something here

Rsauthors.close
Set rsauthors = Nothing
Note that there are several parameters that are not specified. In fact, all parameters are optional, and you can set the corresponding property values for them before you open the recordset:
Dim rsauthors

Set rsauthors = Server.CreateObject ("ADODB. Recordset ")

With Rsauthors
. Source = "Authors"
. ActiveConnection = strconn
. CursorType = adOpenForwardOnly
. LockType = adLockReadOnly
. Open
End With

' Do something here

Rsauthors.close
Set rsauthors = Nothing
Once the recordset is opened, the current pointer is automatically positioned on the first record. If there are no records in the Recordset, the EOF and BOF properties are true:
Rsauthors.open "Authors", strconn

If Rsauthors.bof and Rsauthors.eof Then
' Recordset is empty
End If
1. Options Parameters
The options argument of the Open method allows you to specify the command text content. It can be one of the following CommandTypeEnum constants:
· adCmdText: Text commands, such as SQL strings.
· adCmdTable: Table name.
· adCmdStoredProc: Stored procedure name.
· adCmdFile: The file name of the saved recordset.
· adCmdTableDirect: Table name.
· Adcmdurlbind:url address.
The difference between adCmdTable and adCmdTableDirect is small, and if you want to use all the columns in the table, using adCmdTableDirect will make it a little faster to run some internal optimizations with ADO.
If you do not specify the type of command, ADO must calculate the type of command being executed, which results in additional overhead.
There are two more options: Adcmdunspecified indicates that no type is specified; Adcmdunknow indicates that the type of the command is unknown. They may not be used in general.
Additional options
The options parameter can be any of the above constants, but you can also add the following ExecuteOptionEnum constants:
· Adasyncexcute: Executes the command asynchronously.
· adAsyncFetch: Gets the remaining rows asynchronously after getting the initial rowset.
· Adasyncfetchblocking: Other than getting records does not prevent the command from running, the other is similar to adAsyncFetch.
· Adexechtenorecords: command does not return any records.
Asynchronous processing means that the operation is performed in the background, the command can be run, and then the other work continues without having to wait for it to complete (synchronous operation). This is especially handy when creating a user interface, because it can be returned from the execution of the command, displaying some content to the user, while the acquisition of the data is still in the background. This is not useful for ASP programmers when returning a recordset because the scripting language does not support ADO events, so when the recordset has completed the fill move is not known. When processing updates, deleting or inserting data commands, and not returning recordsets, you can use asynchronous operations that are only available if you do not care about the results.
On the other hand, the adExecuteNoRecords option is useful. It tells ADO that the command executed does not return any data. Therefore, there is no need to create a recordset (in short, it may be empty). This accelerates the query operation that is running updates or adds data.
In order to add one of these options, you can use the or symbol (equivalent to the plus sign "+")
adCmdStoredProc Or adExecuteNoRecords

Adcmdstoreproc + adExecuteNoRecords
In the next chapter, you'll see a more detailed description of the relevant content, because this is more useful when you're working with commands, not recordsets.
2. Moving in a record set
Once you have a recordset open, you often need to traverse each record. This requires the use of the EOF attribute. When the end of the recordset is reached, EOF becomes true because you can create a loop like this:
Rsauthors.open "Authors", strconn

While not rsauthors.eof
Response.Write Rsauthors ("au_lname:) &", "& _
Rsauthors ("au_fname") & "<BR>"
Rsauthors.movenext
Wend
The above example loops until the EOF property is true. The MoveNext method is used to move to the next record.
If the recordset allows for backward movement, you can use the MovePrevious method. In this case, the BOF property value needs to be detected in the loop. There are also separate MoveFirst and MoveLast methods for moving to the first and last records:
Rsauthors.open "Authors", strconn, adOpenDynamic
' Now on the '

Rsauthors.movelast
' Now on the last record
Rsauthors.moveprevious
Rsauthors.moveprevious
' Now three rows from the "End of the" recordset

Rsauthors.movefirst
' Back at the beginning again
3. Use Fields collection
The Fields collection contains Fields objects for each field (column) in the recordset. The Fields collection is the default collection of recordsets, so you can omit it when you access the field, just as the above while ... The case in the Wend example. Therefore, there are several ways to access a field:
Rsauthors.fields ("au_lname"). Value
Rsauthors ("au_lname"). Value
Rsauthors (1). Value
Rsauthors.fields (1). Value
You can use the field name, or use it's position in the collection. Using a name is best because it makes the code easier to maintain.
The Value property is the default property of the field, so you can omit it, for example:
Rsauthors ("au_lname")
If you want to traverse all the fields, you can use the For each structure:
For each fldauthor in Rsauthors.fields
Response.Write Fldauthor.name & ":" & _
Fldauthor.value & "<BR>"
Next
This example prints the name and value of each field.
4. Bookmark
When moving in a recordset, you may want to keep the location of the record and then move back later. Like a real bookmark, a recordset bookmark is a unique pointer to a single record.
To use bookmarks, you simply assign the value of the Bookmark property to a variable:
VARBKMK = Rsauthors.bookmark
You can then move through the recordset and move the record to the corresponding record on the bookmark mark later by using the opposite command:
Rsauthors.bookmark = VARBKMK
Bookmarks are useful when you are looking for records in a recordset. A related example is given in section 8.4.5 later in this chapter.
Note that not all recordsets support bookmarks, and the Supports method (discussed below) will recognize whether they support bookmarks.
It is important to note that bookmarks cannot be used across different recordsets, even if the recordset is created by the same command. Consider the following code:
Rsauthors.open "Authors", strconn
Rsauthorsother.opne "Authors", strconn

VARBKMK = Rsauthors.bookmark
Varbkmkother = Rsauthorsother.bookmark

Although two recordsets were created with the same command, the recordset's bookmarks are not the same.
You can use the Clone method to get an exchangeable bookmark, but we don't discuss it here.
5. Supported Features
As mentioned above, not all recordsets support bookmarks. There are also many other recordset options that are not supported by all providers or Recordset types, so you can validate them with the Supports method.
The Supports method uses one or more cursoroptionenum values as arguments, and returns TRUE or false to indicate whether the option is supported. The list of these values is quite large, so it is listed in Appendix F.
For example:
If rsauthors.supports (Adbookmark) Then
' The recordset supports bookmarks
VARBKMK = Rsauthors.bookmark
End If
You can combine multiple constants by using or or the plus sign "+":
If rsauthors.supports (Adbookmark Or adFind) Then
' The recordset supports bookmarks and use
VARBKMK = Rsauthors.bookmark
End If

8.4.4 Filter Record set
Filtering is a way to temporarily limit the visibility of records in a recordset. This is useful if you are displaying only some records in a recordset, but you do not need to requery the database each time.
1. Use conditional filtering
The Filter property has multiple parameters, one of which is a conditional expression, which is very much like the WHERE clause in sql:
Rsauthors.filter = "state = ' CA '"
This statement qualifies the recordset to display only the records of a state named CA. Using this filter condition causes the current pointer to return to the first matching record. You can traverse all records in a Recordset, and only records that match the criteria are visible.
It is not limited to a single condition, you can also use and or or to connect multiple conditions together:
Rsauthors.filter = "au_lname = ' Homer ' Or au_lname = ' Francis '"
This will filter out the records named Francis or Homer.
The above example shows a filter with a column matching a value, or you can use any of the following operators:
: Less than.
: Greater than.
<=: less than or equal.
>=: greater than or equal.
<&gt: Not equal to.
Like: wildcard characters.
You can use the "*" or "%" symbol when you are using a wildcard operation. For example:
Rsauthors.filter = "au_lname like ' ho% '"
"*" or "%" is used as a wildcard to match any character, so the above example matches all records starting with the "Ho" character in the au_lname field.
You can use an empty string to empty the filter condition so that all records are displayed:
Rsauthors.filter = ""
2. Using constant filtering
The Filter property can also use the FilterGroupEnum constant as its parameter:
· adFilterNone: Clears the current filter condition as it does with an empty string.
· Adefilterpendingrecords: Displays only those records that have changed but have not yet been sent to the server, only available in batch update mode.
· adFilterAffectedRecords: Displays only those records that are affected by the last call to the Delete, Resync, UpdateBatch, and CancelBatch methods.
· Adfilterfetchedrecords: Displays the record in the cache, which is the result of the last call to read the record.
· adFilterConflictingRecords: Displays records that failed to update in the last batch update.
You'll see an introduction to Mass updates later.
3. Use Bookmarks to filter
The last way to filter the recordset is to use an array of bookmarks. You can use this technique to create a list of records and then apply a filter condition to filter them. For example:
Rsauthors.open "Authors", strconn, adOpenKeyset, _
adLockReadOnly, adCmdTableDirect

' Save bookmark for the '
AVARBKMK (0) = Rsauthors.bookmark

' Move forward two records
Rsauthors.movenext
Rsauthors.movenext

' Save bookmark for the third record
AVARBKMK (1) = Rsauthors.bookmark

The "Move to" end and save the bookmark
Rsauthors.movelast
AVARBKMK (2) = Rsauthors.bookmark

' Now apply the filter
Rsauthors.filter = Array (AVARBKMK (0), AVARBKMK (1), AVARBKMK (2))

' Now loop through the recordset
While not rsauthors.eof
Response.Write rsauthors ("au_lname") & "<BR>"
Rsauthors.movenext
Wend
When you loop to the end of the recordset, only three records are found, because only three bookmarks apply to the filter condition.
Note that you cannot use array avarbkmk directly, you must use the array function to convert each bookmark to a different array.

8.4.5 Find Records
Finding a single record is done by the Find method. It is similar to the filtering method that uses conditions:
Rsauthors.find "au_lname = ' Lloyd '"
The main difference between them is that there can only be one condition for this method, and the use of and or or is not allowed.
You can use optional parameters to specify some additional options, with the complete syntax as follows:
Recordset.find Criteria, [SkipRows], [searchdirection], [strat]
SkipRows is a number that represents the number of rows skipped before the start of the lookup record. The default is 0, and the query starts at the current line.
The searchdirection can be a adsearchforward, a forward search record, or a adsearchbackward that represents a backward search record.
Start is a bookmark that indicates where to start looking for records.
If you open the corresponding record, the current pointer will be on the matching record, and if no record is found, then one of the following two locations will be located:
· If you are searching forward, the EOF is set to true after the end of the recordset.
· If it is a backward search, the BOF is set to true before the beginning of the recordset.
Save location with bookmarks
If the record is not found, the relocation of the record can be easily resolved by the bookmark, because you can make a bookmark for the current location, and then move back to the last saved location if you do not find the record in the lookup record.
For example:
' Save the current position
VARBKMK = Rsauthors.bookmark

' Find the Record '
Rsauthors.find "au_lname = ' Sussman '"

' Was it found
If not rsauthors.eof Then
Response.Write "Found:" & Rsauthors ("au_lname") & "," & _
Rsauthors ("au_fname") & "<BR>"
Else
Response.Write "not found. Moving <BR> "
Rsauthors.bookmark = VARBKMK
End If
One reason to use the Filter property is stronger than the Find method is that the search statement can only have one query condition, while the Filter property allows you to specify multiple conditions. That is, you cannot use the Find method when you want to look for more than one field condition. However, you can filter the records first, and then delete the filter criteria if you find a record.

8.4.6 Modify Records
Most of the web is used only to display information, and Web applications are becoming more common. In this case, it is really not useful to have only read-only data. There are many ways to create an application that will almost always need to modify existing data or add new data. In this section, you will learn how to use the method of the Recordset object to change the data. In the next chapter, you'll see how you can use a query to accomplish the same task.
You can set the locking type in addition to the adLockReadOnly to modify the data using the method of the Recordset object (assuming that you have the appropriate permissions). Remember, the default lock type is read-only.
1. Add a record
To add records in a Recordset, use the AddNew method. There are two ways to use AddNew. The first type has no arguments, just call AddNew, and add a blank record at the end of the recordset. You can optionally modify the data in a field before calling the Update method to save your changes:
With Rsauthors
. Open "Authors", strconn, adOpenDynamic, _
adLockOptimistic, adCmdTableDirect

. AddNew
. Fields ("au_id") = "123-12-1234"
. Fields ("au_lname") = "Lloyd"
. Fields ("au_fname") = "Janine"
. Fields ("contract") = 0
. Update

End With
This just adds a new record and sets four mandatory field values.
Another method is to use the optional arguments to the AddNew method, which is a two array, one is the field name and the other is the value of the field.
With Rsauthors
. Open "Authors", strconn, adOpenDynamic, _
adLockOptimistic, adCmdTableDirect

. AddNew Array ("au_id", "au_lname", "au_fname", "Contract"), _
Array ("123-12-1234", "Lloyd", "Janine", 0)
End With
This method does not require calling the Update method.
2. Edit Records
Editing a record is similar to adding a record, except that you do not need to invoke the AddNew method:
strSQL = "SELECT * FROM Authors" & _
"WHERE au_lname= ' Lloyd '"

With Rsauthors
. Open strSQL, strconn, adOpenDynamic, _
adLockOptimistic, adCmdText

. Fields ("contract") =1
. Update
End With
This is simply assigning the value of the contract field of the current record (in this case the first record, because the recordset just opened) to 1.
3. Delete a record
Deleting a record requires a call to the Delete method. Which record to delete depends on optional parameters, which can be one of the following AffectEnum constants:
· adAffectCurrent: Deletes the current record, the default action.
· adAffectGroup: Deletes all records that match the current filter condition.
· Adaffectall: Deletes all records in the recordset.
· adAffectAllChapters: Deletes records from all segments (chapter).
The simplest form of invocation is:
Rsauthors.delete
This deletes the current record. If you have a filter condition and want to delete all records that match the condition, just add the appropriate constants:
Rsauthors.delete adAffectGroup
4. Fields that are automatically incremented
When you add a new record, you typically encounter the question of how to handle those automatically incremented or identified fields (identity Filed). These fields are numeric fields that are automatically updated by the server and are typically used to provide a unique field value for each row. When a database contains more than one table, this unique field is often treated as a foreign key to the associated table. So, when you add a new record, you often need to find out their value.
For example, consider a table with two fields, an Automatically incremented ID field (the Identity field in SQL Server or the AutoNumber field in Access), and a text field named name. Now consider the following code to add a record to the table:
With Rsdata
. Open "Tbltest", adOpenDynamic, adLockOptimistic, adCmdTableDirect
. AddNew
. Fields ("Name") = "Janine"
. Update

IntID =. Fields ("ID")
End With
It looks normal, but whether you can get this value after adding a record depends on the type of cursor, the type of lock, and whether the ID field is indexed. Table 8-4 lists which combinations allow you to get the value of the newly inserted ID field. Other combinations that are not listed in the table cannot return the value of the ID field.
Table 8-4 Gets the relationship between the value of the ID field and the cursor, the type of lock, and whether the ID field is indexed

This clearly indicates that the correct combination must be used to ensure that the correct value for the ID field can be obtained. Otherwise, you may get a 0, null value, or NULL, depending on the way you combine it. When you work with a stored procedure in the next chapter, you see another way to get the Identity field value from SQL Server.