Programming | Advanced 8.4.3 Creating recordsets
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
Parameters
Description
Source
The data source. Can be a table name in a database, a stored query or procedure, an SQL string, a command object, or another command object that applies to a provider
ActiveConnection
The connection used by the recordset. Can be a connection string or an open connection object
CursorType
The type of cursor used. Must be one of the defined cursor types, the default value is Adforwardonly
LockType
The type of lock used. Must be one of the defined locking types, the default value is adLockReadOnly
Options
Tells the provider what the source parameter is, such as a table, a text string, and so on
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 a field name, or use it in a bit in the collection
[1] [2] [3] [4] Next page
