ASP 3.0 advanced process designing (three ten nine)
Tuesday, March 03, 2009 by rain
It is very easy to found volume of a record,Will come true through calling the Open method of Recordset object:
Recordset.Open [Source] , [ActiveConnection] , [CursorType] , [LockType] , [Options]
If its parameter and specification express 8-3 place to show:
Express 8-3 The parameter of Open method reachs a specification
Join number
Say bright
Source
Data source.Object of string of the inquiry that can be the watch name in the database, memory or process, SQL, Command or the other command object that apply to the person that offer
ActiveConnection
The connection that record volume uses.Can be the Connection object that string of a join perhaps opens
CursorType
Use cursor type.One of the cursor type that must be a definition,Default value is AdForwardOnly
LockType
Use lock decides a type.A kind in locking up calm type must be a definition,Default value is AdLockReadOnly
Options
What is the content of parameter of Source of the person that tell to offer,If string of watch, text is waited a moment
For example,The record volume on watch of the Authors in wanting to open database Pubs:
Dim RsAuthors
Set RsAuthors = Server.CreateObject("ADODB.Recordset" )
RsAuthors.Open "authors" , strConn
' Do Something Here
RsAuthors.Close
Set RsAuthors = Nothing
Notice,A few parameter were not appointed.Actually,All parameter are optional,Corresponding attribute can be installed to be worth for them before opening record market:
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 open record market,Current index is located in automatically on the first record.If be in,the record was not recorded centrally,So EOF and BOF attribute are True:
RsAuthors.Open "authors" , strConn
If RsAuthors.BOF And RsAuthors.EOF Then
' Recordset Is Empty
End If
1. Options parameter
The Options parameter of Open method allows to assign command text content.It can be one of constant of the following CommandTypeEnum:
·AdCmdText:Text commands,For instance SQL string.
·AdCmdTable:Express a name.
·AdCmdStoredProc:Memory process name.
·AdCmdFile:The saved file name that records part.
·AdCmdTableDirect:Express a name.
·AdCmdURLBind:URL address.
The distinction of AdCmdTable and AdCmdTableDirect is very little,If want to use the entire line in the watch,Use AdCmdTableDirect will be carried out as a result of ADO certain interior is optimized and make traversal speed becomes a bit a few faster.
If did not specify jussive type,The jussive kind that ADO must push cipher out to carry out,This will cause additional expense.
There still are two options here:AdCmdUnspecified expresses to did not specify a type;AdCmdUnknow states jussive type is sealed.Popularly won't use them possibly.
Additional options
Options parameter can be the anyone in above constant,But also can join following ExecuteOptionEnum constant:
·AdAsyncExcute:Asynchronous ground executes an order.
·AdAsyncFetch:After acquiring initiative travel part,Asynchronous ground gets the travel that remain.
·AdAsyncFetchBlocking:Besides get a record not to prevent a command to move beyond,Other as similar as AdAsyncFetch.
·AdExechteNoRecords:The command does not return any records.
Asynchronous processing means backstage to execute an operation,Can run a command,Continue next other job,And do not need to await its to carry out end (synchronous operation) .When founding user interface,This appears particularly convenient,Because can obey the order,return,Show a few content to the user,And get with what duration occupies still backstage undertakes.When returning record volume,This is not very useful to ASP programmer,Do not support ADO incident because of script language,When did record volume already finish fill to move so cannot be informed.Update when processing, delete or insert data command and when returning record volume,Can use asynchronous to operate,The ability below the circumstance that caring a result only namely is used.
On the other hand,AdExecuteNoRecords option is very useful.The order that it tells ADO to carry out does not return any data.So,Not was necessary to found record volume (anyhow,The likelihood is empty) .The inquiry that update or adds data what this meeting is moving quickly is operated.
To add one of these option,Can use Or symbol (be equal at plus " + " )
AdCmdStoredProc Or AdExecuteNoRecords
AdCmdStoreProc + AdExecuteNoRecords
In below one chapter,the introduction that sees pair of relevant content are more detailed,Because this is handling an order (is not record volume) when the meeting is more useful.
2. In the record concentration moves
Once open market of a record,Often need one by one each record.This need uses EOF property.When arriving at the fine that records part,EOF turns into True,Because can found a loop so:
RsAuthors.Open "authors" , strConn
While Not RsAuthors.EOF
Response.Write RsAuthors("au_lname:) %26 " , "%26 _
RsAuthors(%26quot;au_fname%26quot; ) %26amp; %26quot;%26lt;BR%26gt; %26quot;
RsAuthors.MoveNext
Wend
The ability when the example above is True to EOF attribute circularly all the time is exited.MoveNext method is used at moving to be recorded to below one.
If record collect to allow to move backward,Can use MovePrevious method.Below this kind of circumstance,The need in the loop detects BOF attribute is worth.The MoveFirst that additionally respectively still shift is recorded with last to the first and MoveLast method:
RsAuthors.Open "authors" , strConn, adOpenDynamic
' Now On First Record
RsAuthors.MoveLast
' Now On 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 assembles
Fields gather includes a record to center each field (row) Fields object.The default that Fields gather is record volume assembles,Because of when this is visitting a field, can omiting,Be like the While of Alexandrine face. . The circumstance in.Wend example.Because of this,The method that has a variety of visit fields:
RsAuthors.Fields("au_lname" ).Value
RsAuthors("au_lname).Value
RsAuthors(1).Value
RsAuthors.Fields(1).Value
Can use field name,Or use its position in gather.Use name is best,Because will make so code more safeguard easily.
The default attribute that Value attribute is a field,Also can omit accordingly,For instance:
RsAuthors("au_lname" )
If think all previous is all field,Can use For Each structure:
For Each FldAuthor In RsAuthors.Fields
Response.Write FldAuthor.Name %26 " : "%26 _
FldAuthor.Value %26amp; %26quot;%26lt;BR%26gt; %26quot;
Next
The name that this example will print each field and value.
4. Bookmark
When the record centers shift when,The place that the likelihood needs to preserve a record,Move again later come back.With true bookmark likeness,Bookmark of volume of a record is an only index that points to single record.
To use a title label pasted on the cover of a Chinese-style thread-bound book,Need to gift Bookmark property value only a variable:
VarBkmk = RsAuthors.Bookmark
Next,Can move centrally in the record,On the corresponding record that can carry contrary command to move the record to had done bookmark to label later:
RsAuthors.Bookmark = VarBkmk
When the record searchs a record centrally,Bookmark is very useful.In this chapter later 8.4.5 have a relevant case in the section.
Notice,Be not all record volume to support a title label pasted on the cover of a Chinese-style thread-bound book,Supports method (discuss below) aux will be able to identifies his to whether support a title label pasted on the cover of a Chinese-style thread-bound book.
Notable a bit more important be,Cannot span to record collect differently to use a title label pasted on the cover of a Chinese-style thread-bound book,Although these record volume are same command,found.Consider the following code:
RsAuthors.Open "authors" , strConn
RsAuthorsOther.Opne "authors" , strConn
VarBkmk = RsAuthors.Bookmark
VarBkmkOther = RsAuthorsOther.Bookmark
Although two records volume is,found with same command,But the bookmark that records part is different.
Can use Clone method to obtain exchangeable bookmark,But here we do not discuss it.
5. Supportive function
As above,Be not all record volume to support a title label pasted on the cover of a Chinese-style thread-bound book.Still having a lot of other record volume option also is not by what the person that all offerring or record volume kind support,Because this can use Supports method test and verify.
Supports method uses one or more CursorOptionEnum to be worth as parameter,Return True or False to make clear whether to support this option.The list of these values is quite huge,So its in rank appendix F.
For example:
If RsAuthors.Supports(adBookmark) Then
' The Recordset Supports Bookmarks
VarBkMk = RsAuthors.Bookmark
End If
Can use Or or plus " + " combine many constant:
If RsAuthors.Supports(adBookmark Or AdFind) Then
' The Recordset Supports Bookmarks And Use Of Find
VarBkMk = RsAuthors.Bookmark
End If
8.4.4 filters record volume
Filtering is record of demarcate of a kind of momentarily a kind of method that records it is thus clear that centrally.If show the certain notes that records concentration only,But do not need to inquire a database afresh every time again,This kind of method is very useful.
1 ． Use condition filters
Filter attribute has many parameter,Among them one is conditional expression,It special the Where in resembling SQL is clausal:
RsAuthors.Filter = "state = 'ca' "
The record that volume of record of this statement demarcate shows to city name is Ca only.Use this to filter the condition will make current index returns the first to match a record to go up.The overall record that can all over all previous to record concentration,And match conditional record only just see.
Not just be confined to is onefold condition,Still can use And or Or to join many conditions together:
RsAuthors.Filter = "au_lname = 'Homer' Or Au_lname = 'Francis' "
This will filter the record that gives a surname to be Francis or Homer.
The example above showed one lists those who match a cost to filter method,Also can use below in operator any a kind:
%26lt; :Be less than.
%26gt; :Be more than.
%26lt;= :Be less than be equal to.
%26gt; = :Be more than be equal to.
%26lt;%26gt; :Do not be equal to.
LIKE:Match accord with.
Should use when be being operated through matching accord with,Can use " * " or " % " symbol.For example:
RsAuthors.Filter = "au_lname LIKE 'Ho%' "
" * " or " % " connect as match accord with,Match any character,In can matching Au_lname field because of the example above this with " Ho " all records that character begins.
Can use empty string clear sky filters condition,Will show overall record so:
RsAuthors.Filter = " "
2 ． Use constant filters
Filter attribute also can regard his as parameter with FilterGroupEnum constant:
·AdFilterNone:Clear sky filters currently condition,As identical as the effect that uses an empty string.
·AdeFilterPendingRecords:Show those already were changed only,But the record that has not sent a server,Updating mode to fall group by group only usable.
·AdFilterAffectedRecords:The record that shows those sufferring to call Delete, Resync, UpdateBatch and CancelBatch method effect last time only.
·AdFilterFetchedRecords:The record in cache of indication high speed,Call the result when reading the order that takes a record last time namely.
·AdFilterConflictingRecords:The record that shows newer in be updated group by group last time failure.
Can see later introduce about what update group by group.
3 ． Use bookmark filters
Last kinds filter the method that records part is to use array of a bookmark.Can use this technology to found list of a record,Apply to filter again next the condition filters to its.For example:
RsAuthors.Open "authors" , strConn, adOpenKeyset, _
AdLockReadOnly, adCmdTableDirect
' Save Bookmark For The First Record
AvarBkmk(0) = RsAuthors.Bookmark
' Move Forward Two Records
RsAuthors.MoveNext
RsAuthors.MoveNext
' Save Bookmark For The Third Record
AvarBkmk(1) = RsAuthors.Bookmark
' Move To The 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(%26quot;au_lname%26quot; ) %26amp; %26quot;%26lt;BR%26gt; %26quot;
RsAuthors.MoveNext
Wend
Come when the loop when recording collect fine position,Meeting discovery is recorded three times only,Because only three bookmark apply at filtering condition.
Notice,Cannot use array AvarBkmk directly,Must use Array function to change each bookmark into diverse array.
8.4.5 searchs a record
Search single record to be finished by Find method.It is similar to use condition filter method:
RsAuthors.Find "au_lname = 'Lloyd' "
The mainest distinction between them depends on this kind of method can having a requirement only,Do not allow to use And or Or.
Can use optional parameter to appoint a few additional options,Its complete grammar is as follows:
Recordset.Find Criteria, [SkipRows] , [SearchDirection] , [Strat]
SkipRows is a number,The linage that before expressing to beginning to search a record, has jumped.Default is 0,Inquiry begins from current travel.
SearchDirection can be AdSearchForward,Express to search a record ahead;Or AdSearchBackward,Express to search a record backward.
Start is a bookmark,Point out the position that begins to search a record.
If open corresponding record,Current index will be located in on the record that match,If did not find a record,Will be located in so below one of two positions:
If · is forward search,At the back of be located in position of record volume fine,EOF is installed to be True.
If · is to be searched backward,Before be located in record volume to begin the position,BOF is installed to be True.
Use bookmark saves the seat
If did not find corresponding record,The new fixed position of the record can be solved easily by bookmark,Because can feel current the position makes a title label pasted on the cover of a Chinese-style thread-bound book,The record that if searching a record,place did not find to need in the process,So move again return the seat that saves last.
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: "%26 RsAuthors ("au_lname" ) %26 " , "%26 _
RsAuthors(%26quot;au_fname%26quot; ) %26amp; %26quot;%26lt;BR%26gt; %26quot;
Else
Response.Write "Not Found. Moving %26lt;BR%26gt; %26quot;
RsAuthors.Bookmark = VarBkmk
End If
Use Filter attribute is strong a reason at Find method is Find statement can inquire a condition only,And Filter attribute allows to appoint many conditions.That is to say,The field condition that should want to search is more than when,Cannot use Find method.However,Can filter first record,If find a record to be able to be deleted again,filter condition.
8.4.6 alters a record
Major Web uses indication information only,And Web application process is becoming more and more general.Below this kind of case,If have a reading to occupy only,do not have what good really.Establish an application program,Need revises existent data or be to add new data nearly always,Its method has a lot of.In this section,The method that how uses study Recordset target will alter data.In below one chapter,Will see how to use inquiry to finish same job.
The means that can set the lock of the place besides AdLockReadOnly to decide a type to cooperate to use Recordset target goes revising data (assume have corresponding limits of authority) .Remember,Default it is read-only that the lock decides a type.
1 ． Add a record
Should add a record centrally in the record,Use AddNew method.The method that has two kinds of use AddNew.The first kind without any parameter,Call AddNew merely,An empty record is added of collect finally in the record.Before calling Update method to save what what do to change,The data in modification field of OK and optional ground:
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 added a new record,The setting enforces four times model the field is worth.
The optional parameter that another kind of method is use AddNew method,This is two array,One is field name,Another value that is a 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 need to call Update method.
2 ． Editor record
Editor record and the methodological likeness that add a record,Different point depends on needing to call AddNew method:
StrSQL = "SELECT * FROM Authors" %26 _
"WHERE Au_lname='Lloyd' "
With RsAuthors
.Open StrSQL, strConn, adOpenDynamic, _
AdLockOptimistic, adCmdText
.Fields("contract" ) =1
.Update
End With
This is will current record merely (the first record is below this kind of circumstance,Because just open record market) the value ode of Contract field is 1.
3 ． Delete a record
Delete a record to need to call Delete method.Delete which record to depend on optional parameter,Can be below one of AffectEnum constant:
·AdAffectCurrent:Delete current record,Default is operated.
·AdAffectGroup:Delete match to filter currently all records of the condition.
·AdAffectAll:The overall record that deletes record concentration.
·AdAffectAllChapters:Delete all paragraph (Chapter) medium record.
The simplest calling a form is:
RsAuthors.Delete
This will delete current record.If one filters condition,Want to delete all records that match this requirement,So need to add proper constant only:
RsAuthors.Delete AdAffectGroup
4 ． The field that increases by degrees automatically
When adding a new record,General meeting encounters a such problems:If why handle what those increase by degrees automatically or label field (Identity Filed) .These fields are the digital field that replaces automatically by the server,Use at offerring an only field to be worth for each commonly.When the database contains many lists,So this only field often is regarded as the external key of associated watch.So,When adding a new record,The value that often needs to find out them.
For example,The list that considers to have two fields,An ID field that increases by degrees automatically (the AutoNumber field in the IDENTITY field in SQL Server or Access) ,The text field that name of a field is Name.The code that considers to add a record below to the middle of the watch now:
With RsData
.Open "tblTest" , adOpenDynamic, adLockOptimistic, adCmdTableDirect
.AddNew
.Fields("Name" ) = "Janine "
.Update
IntID = .Fields("ID" )
End With
Look very common,But the kind that whether can the type that relies on cursor to this value, lock take to decide after adding a record and ID field are indexed.Watch 8-4 listed what combination allows to get new the value of insertion ID field.Other the value that cannot return ID field in the combination in the watch without the row.
Express 8-4 The type that the value that gets ID field and cursor, lock decides reachs the relation that whether is ID field indexed
The person that offer
Object
Index
Cursor position
Cursor type
The lock decides a type
ODBC
Access97
Yes
Server
Key collect
Pessimism
Hopeful model
Access2000
Yes
Server
Key collect
Pessimism
Hopeful model
Deny
Client
All
Pessimism
Hopeful model
SQL Server 6.5
Yes
Server
Key collect
Pessimism
Hopeful model
SQL Server 7.0
Yes
Server
Key collect
Pessimism
Hopeful model
Client
All
Pessimism
Hopeful model
Deny
Client
All
Pessimism
Hopeful model
Jet 4.0
Access97
Yes
Server
All
All
Access 2000
Yes
Server
All
All
Deny
Client
All
Pessimism
Hopeful model
SQL OLE DB
SQL Server 6.5
SQL Server 7.0
Yes
Server
Key collect
Pessimism
Hopeful model
Client
All
Pessimism
Hopeful model
Deny
Client
All
Pessimism
Hopeful model
This explains clearly must use right combination,The true value that ability assures to be able to acquire ID field.Otherwise,May get 0, empty value or NULL,This depends on assorted means.
When memory process is being handled in issueing one order,The method that will see another kind gets IDENTITY field to be worth from SQL Server.
8.5 management is wrong
When processing data stores,The possibility that produces a mistake always exists:Security problem,The record that tries to already was deleted newlier by other user,The question of such is very much.Unwarrantable everything moves good,Accordingly must the mistake that compose proposes some kind of form is controlled.
In the 7th chapter,The common error in studying ASP page is handled,But what involve now is data storage,Must consider to use additional code to undertake the mistake is handled so.See Errors assemble first,The wrong processor that discusses its how to satisfy ASP 3.0 again is made.