身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 15:24:31
Defining the ADO Constants
ASP has no inherent knowledge of what the CursorType, LockType and Options constants (eg adOpenForwardOnly or adLockOptimistic) actually mean. Instead you need to define them. In this respect the use of <%Option Explicit%> is strongly recommended as errors regarding undefined variables will automatically point you to the fact that your ADO constants are not defined.
There are three methods of defining constants.
Method 1 - Strongly Recommended
Put the following code into your global.asa in the Application_OnStart subroutine (all on one line):
<!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common Files\system\ado\msado15.dll" -->
You''ll need to replace the drive letter above if your NT Boot partition is not drive c: By including a reference to the ADO type library you make ASP "aware" of the ADO constants that you are using.
Method 2 - Recommended
Not as highly recommended as Method 1 is including the adovbs.inc file in whatever pages require access to the ADO constants. The adovbs.inc file should be on your hard drive if you''ve installed Microsoft IIS or PWS. Typically it can be found in c:\program files\common files\system\ADO\
Copy the file to your includes directory on your webserver (eg c:\inetpub\wwwroot\includes\) and use the following code on the pages that require access to the file:
<!-- #include virtual="/includes/adovbs.inc" -->
Since the adovbs.inc file is quite large, you could choose to create a user-defined file which contains only the constants that you use and include that instead. Simply open adovbs.inc in Notepad, delete the constants you don''t need, save the file as adoConst.asp (or similar) and include this new file instead.
Method 3 - Not Recommended
You can define the constants inline on the page. This is not a recommended practise.
NOTE: It is also possible to supply the underlying values of the ADO Constants directly into the Recordset''s Open method. The use of these "magic numbers" (as Phil Paxton calls them) is to be strongly discouraged. Nearly 50% of problematic code that I have encountered resulted from people choosing incorrect numbers to supply to the .Open method, and the person looking over the code has to constantly look up the documentation to find out what numbers correspond to what constants.
Use of "magic number" results in code that looks like this:
objRS.Open strSQL, strConnect, 3, 1, &H0001
Even is these numbers just happened to be valid values could you tell me what they stood for? If you can then you''ve been spending too much time memorising ADO constants! The programmer that puts objRS.AddNew on the line after the above is in for a nasty suprise.
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 15:44:04
<%
Function FormatDate( _
byVal strDate, _
byVal strFormat _
)
' Accepts strDate as a valid date/time,
' strFormat as the output template.
' The function finds each item in the
' template and replaces it with the
' relevant information extracted from strDate.
' You are free to use this code provided the following line remains
' www.adopenstatic.com/resources/code/formatdate.asp
' Template items
' %m Month as a decimal no. 2
' %M Month as a padded decimal no. 02
' %B Full month name February
' %b Abbreviated month name Feb
' %d Day of the month eg 23
' %D Padded day of the month eg 09
' %O Ordinal of day of month (eg st or rd or nd)
' %j Day of the year 54
' %Y Year with century 1998
' %y Year without century 98
' %w Weekday as integer (0 is Sunday)
' %a Abbreviated day name Fri
' %A Weekday Name Friday
' %H Hour in 24 hour format 24
' %h Hour in 12 hour format 12
' %N Minute as an integer 01
' %n Minute as optional if minute <> 00
' %S Second as an integer 55
' %P AM/PM Indicator PM
On Error Resume Next
Dim intPosItem
Dim int12HourPart
Dim str24HourPart
Dim strMinutePart
Dim strSecondPart
Dim strAMPM
' Insert Month Numbers
strFormat = Replace(strFormat, "%m", DatePart("m", strDate), 1, -1, vbBinaryCompare)
' Insert Padded Month Numbers
strFormat = Replace(strFormat, "%M", Right("0" & DatePart("m", strDate), 2), 1, -1, vbBinaryCompare)
' Insert non-Abbreviated Month Names
strFormat = Replace(strFormat, "%B", MonthName(DatePart("m", strDate), False), 1, -1, vbBinaryCompare)
' Insert Abbreviated Month Names
strFormat = Replace(strFormat, "%b", MonthName(DatePart("m", strDate), True), 1, -1, vbBinaryCompare)
' Insert Day Of Month
strFormat = Replace(strFormat, "%d", DatePart("d",strDate), 1, -1, vbBinaryCompare)
' Insert Padded Day Of Month
strFormat = Replace(strFormat, "%D", Right ("0" & DatePart("d",strDate), 2), 1, -1, vbBinaryCompare)
' Insert Day of Month Ordinal (eg st, th, or rd)
strFormat = Replace(strFormat, "%O", GetDayOrdinal(Day(strDate)), 1, -1, vbBinaryCompare)
' Insert Day of Year
strFormat = Replace(strFormat, "%j", DatePart("y",strDate), 1, -1, vbBinaryCompare)
' Insert Long Year (4 digit)
strFormat = Replace(strFormat, "%Y", DatePart("yyyy",strDate), 1, -1, vbBinaryCompare)
' Insert Short Year (2 digit)
strFormat = Replace(strFormat, "%y", Right(DatePart("yyyy",strDate),2), 1, -1, vbBinaryCompare)
' Insert Weekday as Integer (eg 0 = Sunday)
strFormat = Replace(strFormat, "%w", DatePart("w",strDate,1), 1, -1, vbBinaryCompare)
' Insert Abbreviated Weekday Name (eg Sun)
strFormat = Replace(strFormat, "%a", WeekDayName(DatePart("w",strDate,1), True), 1, -1, vbBinaryCompare)
' Insert non-Abbreviated Weekday Name
strFormat = Replace(strFormat, "%A", WeekDayName(DatePart("w",strDate,1), False), 1, -1, vbBinaryCompare)
' Insert Hour in 24hr format
str24HourPart = DatePart("h",strDate)
If Len(str24HourPart) < 2 then str24HourPart = "0" & str24HourPart
strFormat = Replace(strFormat, "%H", str24HourPart, 1, -1, vbBinaryCompare)
' Insert Hour in 12hr format
int12HourPart = DatePart("h",strDate) Mod 12
If int12HourPart = 0 then int12HourPart = 12
strFormat = Replace(strFormat, "%h", int12HourPart, 1, -1, vbBinaryCompare)
' Insert Minutes
strMinutePart = DatePart("n",strDate)
If Len(strMinutePart) < 2 then strMinutePart = "0" & strMinutePart
strFormat = Replace(strFormat, "%N", strMinutePart, 1, -1, vbBinaryCompare)
' Insert Optional Minutes
If CInt(strMinutePart) = 0 then
strFormat = Replace(strFormat, "%n", "", 1, -1, vbBinaryCompare)
Else
If CInt(strMinutePart) < 10 then strMinutePart = "0" & strMinutePart
strMinutePart = ":" & strMinutePart
strFormat = Replace(strFormat, "%n", strMinutePart, 1, -1, vbBinaryCompare)
End If
' Insert Seconds
strSecondPart = DatePart("s",strDate)
If Len(strSecondPart) < 2 then strSecondPart = "0" & strSecondPart
strFormat = Replace(strFormat, "%S", strSecondPart, 1, -1, vbBinaryCompare)
' Insert AM/PM indicator
If DatePart("h",strDate) >= 12 then
strAMPM = "PM"
Else
strAMPM = "AM"
End If
strFormat = Replace(strFormat, "%P", strAMPM, 1, -1, vbBinaryCompare)
FormatDate = strFormat
End Function
Function GetDayOrdinal( _
byVal intDay _
)
' Accepts a day of the month
' as an integer and returns the
' appropriate suffix
On Error Resume Next
Dim strOrd
Select Case intDay
Case 1, 21, 31
strOrd = "st"
Case 2, 22
strOrd = "nd"
Case 3, 23
strOrd = "rd"
Case Else
strOrd = "th"
End Select
GetDayOrdinal = strOrd
End Function
%>
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 15:50:38
http://www.adopenstatic.com/resources/code/UIValidation.asp
输入验证
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 15:59:00
http://www.adopenstatic.com/experiments/recordsetpaging.asp
分页比较
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:11:00
http://www.adopenstatic.com/experiments/ConnStringSpeed.asp
连接DB的比较
http://www.adopenstatic.com/experiments/fastestautonumber.asp
自动编号的比较
http://www.adopenstatic.com/experiments/stringconcatenation.
字符串连接的比较
http://www.adopenstatic.com/experiments/clientsidecursortypes.asp
客户端游标类型
http://www.learnasp.com/freebook/asp/
ASP教程
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:18:45
INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
http://support.microsoft.com/kb/q232144/
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:24:01
How To Improve String Concatenation Performance
http://support.microsoft.com/kb/q170964/
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:28:47
http://www.adopenstatic.com/faq/recordcounterror.asp
RecordCount 返回-1？
----------------------------
The use of the ADO Recordset''s .RecordCount property requires either the use of:
Static or Keyset server-side cursors or
A client-side cursor (which returns a Static cursor)
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:43:34
The 3 most important recordset properties
faq333-618
Posted: 3 Apr 01
When developing your ASP (ADO – ActiveX Data Objects) applications, there are three recordset properties that will help you determine whether your code will run smoothly, or whether you will be up all night pulling your hair out trying to figure out WHY otherwise perfectly good code just won’t work, no matter what you do –
Those are Cursor Type, Lock Type, and Cursor Location. A good understanding of what these properties are and how they work is essential in having an application that will run as efficiently as possible – given its needed level of functionality.
Cursor Types:
This determines what type of cursor you would like for the recordset. Different cursor types support different directions of movement, and some support methods and properties that others do not (i.e. bookmarks, recordcount, etc…).
adOpenForwardOnly ( 0 ) -- Default
Forward only cursor. You can only move forward through the recordset and only one row at a time. This cursor will improve performance if you are only making a single pass through the recordset.
adOpenKeyset ( 1 )
Keyset cursor. The data you receive is fixed, you do not see additions or deletions. However, the data in the fixed set is up to date. All types of movement are supported.
adOpenDynamic ( 2 )
Dynamic cursor. The data is not fixed. The data you see is up to date. All types of movement are supported. It should be noted that some providers do not support bookmarks.
adOpenStatic ( 3 )
Static cursor. The data is fixed – so this is like a snapshot of the data, and all types of movement are supported.
There are two times that you can set a cursor type, and those are before you open it, or on the same line that you open the recordset. I’ll go into that syntax at the end.
Cursor Locations:
Next is the cursor location. There are four options here. You have your choice of where to declare this, as well. If you declare the cursor location for the connection object that you will use for the recordset’s data connection, the recordset inherits that setting by default. You can also set it before you open it.
adUseNone ( 1 )
Indicates no cursor location
adUseServer ( 2 ) -- Default
Use server side cursor (assuming that the server will support it).
adUseClient ( 3 )
Use the Microsoft client-side cursor
adUseClientBatch ( 3 )
Use the Microsoft client-side cursor (exists for backward compatibility)
A server-side cursor (when available) handles data concurrency issues better than client-side cursors. Client-side cursors must be used when creating disconnected recordsets and custom recordsets (this one is cool – I’ll have to write a FAQ on it).
Let me stop right here for just a second and say that the cursor type and cursor location have a direct relationship. If the cursorLocation property is set to adUseClient or adUseBatchClient, then only adOpenStatic cursor type is supported. So, even if you specify another cursorType, once you set the location to either adUseClient or adUseClientBatch ADO will downgrade the cursor type to adOpenStatic – so it stands to reason that you might not even want to declare one explicitly if you do use the client side cursor.
Lock Types:
Here’s the biggie for those of use who provide web interfaces for databases. This one is the one that determines what kind of record locking (or concurrency) your recordset will have.
adLockReadOnly ( 1 ) -- Default
A read only recordset. No record locking is provided on the database server since there can be no updates whatsoever to this type of recordset.
adLockPessimistic ( 2 )
Pessimistic locking is used (go figure). The provider will attempt to lock the record once editing begins on the recordset.
adLockOptimistic ( 3 )
Any guesses here? That’s right, Optimistic locking is used where the provider only provides record locking once the update method is used on the recordset.
adLockBatchOptimistic ( 4 )
Optimistic batch locking is used. Locks are issued only when the updateBatch method is used on the recordset, and not during field assignments.
Your choice of locking is dependent mostly upon the business rules for your data. Pessimistic locking should be used if you can’t afford a dirty read – that is, pessimistic locking will lock all records that you select from the database until you explicitly release the lock on them. Airline reservation systems would have to use such a locking mechanism to ensure that two people weren’t reserving the same seat at the same time. They can be expensive (talking server resources), especially over the web because you have to maintain a constant connection to the database while your user goes to the bathroom to think about whether he wants an aisle or window seat. Optimistic locking is much more forgiving, but it does introduce some problems as well. Dirty reads are possible, but if you use error trapping, and research the different states of a recordset (i.e. – originalValue and underlyingValue), you can check to make sure everything is ok before you do an update, and using those same properties, you can even resolve the issue without ever throwing an error back at your users. That’s beyond the scope of this FAQ, however.
Ok, let’s look at some syntax –
Here, I’m going to create a recordset and set it’s properties with the long form. We’ll assume I already have a connection object called ‘con’ (there’s already a FAQ on how to make a connection to a database) –
dim rs
set rs = server.createobject (“ADODB.Recordset”)
rs.activeconnection = con
rs.cursortype = adOpenStatic
rs.cursorlocaton = adUseClient
rs.locktype = adLockOptimistic
rs.open “SELECT * FROM myLittleTable”
set rs.activeconnection = nothing
con.close
See that? I just closed the connection, I still have my data, and I''ve freed up some server resources. If you use a client side cursor, then you don’t even need to keep your active connection. Can you say, “My web site can now support more visitors”? I can. Thinking in this vein is especially important if you are using a database management system such as Access, that doesn’t have built in safeguards and record locking like MS SQL Server. Don’t get me wrong, I’m not saying you don’t have to think about these issues if you use an Enterprise Scale RDBMS… believe me, it’s VERY important, and you DO have to think about them. All I’m saying is that you could make a case that says it’s even more important if you aren’t using such a system. Carefully planning out how you will retrieve and deal with your data can vastly improve the performance of your site.
So now you’re thinking… “Well, if I don’t have an active connection to the database, why even bother setting the locktype, since it’s not going to have any real effect on the database, itself”, right? True enough, BUT if you don’t set a locktype, then you have accepted the default of adLockReadOnly, and therefore you cannot update it. Don’t want to update it, you say? Fine, then… don’t set it. See how easy this is? You just have to think about all the things that you want to do with your recordset, and the proper attributes will simply present themselves.
Ok, I said I would cover both ways of setting your options, so here is the short form. Once you know what is what and who is who, coding is all about shortcuts.
dim sqlStatement
sqlStatement = “SELECT * FROM myLittleTable”
rs.cursorLocation = adUseClient
rs.open sqlStatement, con, adOpenDynamic ,adLockBatchOptimistic
The open method is followed by your source (SQL Statement), followed by your connection object, followed by your cursor type, followed by your lock type, and you can follow those with options such as adCmdStoredProc, adCmdTable, or a host of others which again, are beyond the scope of what we’re talking about here. Cursor Location is the only one of the three that can’t be declared “inline” with your other declarations. It has to go on it’s own line. For reference sake, here is the “official” syntax of the open method:
recordsetobject.open source, ActiveConnection, CursorType, LockType, Options
You can separate fields you do not wish to declare with just plain ole commas (e.g. rs.open sql, con, ,adLockOptimistic) -- and you can OR more than one option together if you need to.
And leave off anything at the end – such as how I left off the options part of that statement.
Ok, one last thing before I shut up – all the constant enum names (e.g. adLockThis and adUseThat) that were used to explain the different types of attributes here are all contained in the handy-dandy adovbs.inc include file that can easily be found by typing that file name into the text box at your favorite search engine. It MUST be included on every page where you want to use the enums rather than the contants (e.g. 1, 5, 3) unless you declare them yourself before you use them.
Well, gee, I hope I haven’t forgotten anything. Feel free to add your own FAQ if I have to beef up what’s been said here. I hope this makes someone’s life a little easier sometime.
Happy Coding!
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 16:44:07
http://builder.com.com/5100-31-5076711.html
Work with databases in ASP
http://www.tek-tips.com/threadminder.cfm?pid=333
论坛
http://www.tek-tips.com/faq.cfm?pid=333
FAQ
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 17:15:49
http://www.js-examples.com/page/asp__connection.html
身份：admin
发帖：1435
登陆次数：3225
发表于 2007/4/2 17:19:17
http://www.adopenstatic.com/faq/whyOLEDB.asp
Reasons to use the native Jet OLEDB Provider
http://www.adopenstatic.com/faq/OLEDBConnection.asp
