<%
Option Explicit
Dim cnnPubs
Dim sParm
Dim sSQL
Dim rstAuthors
Set cnnPubs = Server.CreateObject("ADODB.Connection")
cnnPubs.Open "pubs", "sa"
sParm = "M"
sSQL = "Select au_fname, au_lname "
sSQL = sSQL & "FROM AUTHORS "
sSQL = sSQL & "WHERE au_lname Like " & chr(39)
sSQL = sSQL & sParm & "%" & chr(39)
Set rstAuthors = Server.CreateObject("ADODB.Recordset")
Set rstAuthors.ActiveConnection = cnnPubs
rstAuthors.Open sSQL
Do While Not rstAuthors.EOF
Response.Write rstAuthors(0) & chr(32) & _
rstAuthors(1) & "<BR>"
rstAuthors.MoveNext
Loop
%>
Figure 2 usp_authors_parm
CREATE PROCEDURE usp_authors_parm
@au_lname varchar(40)
AS
SELECT au_fname, au_lname
FROM AUTHORS
WHERE au_lname
LIKE @au_lname + '%'
Figure 5 ADO Object Details
<%
Option Explicit
Dim iStoredProc
Dim cnnPubs
Dim cmdParms
Dim parm
iStoredProc = 4
Set cnnPubs = Server.CreateObject("ADODB.CONNECTION")
cnnPubs.Open "pubs", "sa"
Set cmdParms = Server.CreateObject("ADODB.COMMAND")
Set cmdParms.ActiveConnection = cnnPubs
cmdParms.CommandText = "byroyalty"
cmdParms.CommandType = iStoredProc
cmdParms.Parameters.Refresh
%>
<HTML>
<BODY>
<TABLE>
<TR>
<TH>Attibutes</TH>
<TH>Direction</TH>
<TH>Name</TH>
<TH>NumericScale</TH>
<TH>Precision</TH>
<TH>Size</TH>
<TH>Type</TH>
</TR>
<TR>
<%
For Each parm in cmdParms.Parameters
%>
<TD><%= parm.Attributes %></TD>
<TD><%= parm.Direction %></TD>
<TD><%= parm.Name %></TD>
<TD><%= parm.NumericScale %></TD>
<TD><%= parm.Precision %></TD>
<TD><%= parm.Size %></TD>
<TD><%= parm.Type %></TD>
</TR>
<%
Next
%>
</TABLE>
<BODY>
</HTML>
| Attributes | Direction | Name | NumericScale | Precision | Size | Type |
| 0 | 4 | RETURN_VALUE | 0 | 10 | 0 | 3 |
| 64 | 1 | @percentage | 0 | 10 | 0 | 3 |
Figure 7 Explicitly Creating a Parameter
<%
Option Explicit
Dim iStoredProc
Dim cnnPubs
Dim cmdByRoyalty
Dim prmByRoyalty
Dim sAuthorIds
Dim rstAuthors
Dim sSQL
iStoredProc = 4
Set cnnPubs = Server.CreateObject("ADODB.CONNECTION")
cnnPubs.Open "pubs", "sa"
Set cmdByRoyalty = Server.CreateObject("ADODB.COMMAND")
Set cmdByRoyalty.ActiveConnection = cnnPubs
cmdByRoyalty.CommandText = "byRoyalty"
cmdByRoyalty.CommandType = iStoredProc
Set prmByRoyalty = cmdByRoyalty.CreateParameter("@percentage",
3, 1, 0, 40)
cmdByRoyalty.Parameters.Append prmByRoyalty
Set rstByRoyalty = Server.CreateObject("ADODB.RECORDSET")
Set rstByRoyalty = cmdByRoyalty.execute
sAuthorIds = "("
Do While Not rstByRoyalty.EOF
sAuthorIds = sAuthorIds & chr(39) & rstByRoyalty(0) & chr(39) & ","
rstByRoyalty.MoveNext
Loop
sAuthorIds = Left(sAuthorIds, Len(sAuthorIds) - 1) & ")"
Set rstAuthors = Server.CreateObject("ADODB.RECORDSET")
Set rstAuthors.ActiveConnection = cnnPubs
sSQL = "SELECT au_fname, au_lname FROM AUTHORS "
sSQL = sSQL & "WHERE au_id IN " & sAuthorIds
rstAuthors.Open sSQL
Do While Not rstAuthors.EOF
Response.Write rstAuthors(0) & chr(32) & rstAuthors(1) & "<BR>"
rstAuthors.MoveNext
Loop
%>
Figure 8 RetrieveImage.asp
<%
Option Explicit
Dim cnnPubs
Dim rstPub_Info
' Clear existing HTTP header info
Response.Expires = 0
Response.Buffer = TRUE
Response.Clear
' Set the HTTP header to an image type.
Response.ContentType = "image/gif"
Set cnnPubs = Server.CreateObject("ADODB.Connection")
cnnPubs.Open "pubs", "sa"
Set rstPub_Info = cnnPubs.Execute("SELECT logo FROM pub_info WHERE _
pub_id='1389'")
Response.BinaryWrite rstPub_Info("logo")
Response.End
%>
Figure 9 ShowImage.asp
<HTML>
<HEAD>
<TITLE>Show the Image</TITLE>
</HEAD>
<BODY>
<H2>Presenting the Logo:</H2>
<!-- This page contains a link to the image so that you can display
the text. The RetrieveImage.asp page won't allow you to write out text
since you have set the ContentType to image/gif" -->
<IMG SRC="RetrieveImage.asp">
</BODY>
</HTML>
Figure 11 RsSupports.asp
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
'--------------------------------------
' ShowRs
'--------------------------------------
Sub ShowRs(rs)
Do Until rs Is Nothing
Response.Write "<TABLE Border=1>"
Response.Write "<TR>"
For Each Fd In rs.Fields
Response.Write "<TD bgcolor='#B4CFFC'>"
Response.Write "<font face='Geneva,Arial' size='2'>"
Response.Write Fd.Name
Response.Write "</font></TD>"
Next
Response.Write "</TR>"
Do While Not rs.EOF
Response.Write "<TR>"
For Each Fd in rs.Fields
Response.Write "<TD>"
Response.Write "<font face='Geneva,Arial' size='2'>"
Response.Write Fd.Value
Response.Write "</font></TD>"
Next
Response.Write "</TR>"
rs.MoveNext
Loop
Response.Write "</TABLE><P>"
Set rs = rs.NextRecordset
Loop
End Sub
'--------------------------------------
' RsSupports
'--------------------------------------
Sub RsSupports(sSQL,sCnn)
Dim iCursorType(4)
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdTable = &H0002
iCursorType(0) = adOpenForwardOnly
iCursorType(1) = adOpenKeyset
iCursorType(2) = adOpenDynamic
iCursorType(3) = adOpenStatic
Response.Write "<TABLE BORDER=1>"
Response.Write "<TR><TD COLSPAN=9 BGCOLOR='#B4CFFC'>"
Response.Write "Recordset Supports Values"
Response.Write "</TD>"
For iLoop = 0 To 3
Response.Write "<TR><TD COLSPAN=9 BGCOLOR='#FFF7CA'><font _
face='Geneva,Arial' size='2'>"
Set rs = Server.CreateObject("Adodb.Recordset")
rs.CursorType = iCursorType(iLoop)
rs.LockType = adLockOptimistic
rs.Open "Select * from authors", sCnn
Select Case iCursorType(iLoop)
Case adOpenForwardOnly
Response.Write "ForwardOnly cursor supports:<BR>"
Case adOpenKeyset
Response.Write "Keyset cursor supports:<BR>"
Case adOpenDynamic
Response.Write "Dynamic cursor supports:<BR>"
Case adOpenStatic
Response.Write "Static cursor supports:<BR>"
End Select
Response.Write "</font></TD></TR>"
Response.Write "<TR>"
DisplaySupport rs
Response.Write "</TR>"
rs.Close
Next
Response.Write "</TABLE>"
End Sub
Sub DisplaySupport(rs)
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
' Fill array with cursor option constants.
Dim lConstants(9)
lConstants(0) = adAddNew
lConstants(1) = adApproxPosition
lConstants(2) = adBookmark
lConstants(3) = adDelete
lConstants(4) = adHoldRecords
lConstants(5) = adMovePrevious
lConstants(6) = adResync
lConstants(7) = adUpdate
lConstants(8) = adUpdateBatch
For iLoop = 0 To 8
Response.Write "<TD><font face='Geneva,Arial' size='2'>"
bSupports = rs.Supports(lConstants(iLoop))
If bSupports Then
Select Case lConstants(iLoop)
Case adAddNew
Response.Write "AddNew"
Case adApproxPosition
Response.Write "AbsolutePosition and AbsolutePage"
Case adBookmark
Response.Write "Bookmark"
Case adDelete
Response.Write "Delete"
Case adHoldRecords
Response.Write "Holding Records"
Case adMovePrevious
Response.Write "MovePrevious and Move"
Case adResync
Response.Write "Resyncing Data"
Case adUpdate
Response.Write "Update"
Case adUpdateBatch
Response.Write "Batch Updating"
End Select
End If
Response.Write "</font></TD>"
Next
End Sub
</SCRIPT>
Figure 13 Recordset Properties Available by Cursor
| Property | Forward-only | Dynamic | Keyset | Static |
| AbsolutePage | Not available | Not available | Read/write | Read/write |
| AbsolutePosition | Not available | Not available | Read/write | Read/write |
| ActiveConnection | Read/write | Read/write | Read/write | Read/write |
| BOF | Read-only | Read-only | Read-only | Read-only |
| Bookmark | Not available | Not available | Read/write | Read/write |
| CacheSize | Read/write | Read/write | Read/write | Read/write |
| CursorLocation | Read/write | Read/write | Read/write | Read/write |
| CursorType | Read/write | Read/write | Read/write | Read/write |
| EditMode | Read-only | Read-only | Read-only | Read-only |
| EOF | Read-only | Read-only | Read-only | Read-only |
| Filter | Read/write | Read/write | Read/write | Read/write |
| LockType | Read/write | Read/write | Read/write | Read/write |
| MarshalOptions | Read/write | Read/write | Read/write | Read/write |
| MaxRecords | Read/write | Read/write | Read/write | Read/write |
| PageCount | Not available | Not available | Read-only | Read-only |
| PageSize | Read/write | Read/write | Read/write | Read/write |
| RecordCount | Not available | Not available | Read-only | Read-only |
| Source | Read/write | Read/write | Read/write | Read/write |
| State | Read-only | Read-only | Read-only | Read-only |
| Status | Read-only | Read-only | Read-only | Read-only |
Figure 14 Recordset Methods Available by Cursor
| Method | Forward-only | Dynamic | Keyset | Static |
| AddNew | Yes | Yes | Yes | Yes |
| CancelBatch | Yes | Yes | Yes | Yes |
| CancelUpdate | Yes | Yes | Yes | Yes |
| Clone | No | No | Yes | Yes |
| Close | Yes | Yes | Yes | Yes |
| Delete | Yes | Yes | Yes | Yes |
| GetRows | Yes | Yes | Yes | Yes |
| Move | Yes | Yes | Yes | Yes |
| MoveFirst | Yes | Yes | Yes | Yes |
| MoveLast | No | Yes | Yes | Yes |
| MoveNext | Yes | Yes | Yes | Yes |
| MovePrevious | No | Yes | Yes | Yes |
| NextRecordset | Yes | Yes | Yes | Yes |
| Open | Yes | Yes | Yes | Yes |
| Requery | Yes | Yes | Yes | Yes |
| Resync | No | No | Yes | Yes |
| Supports | Yes | Yes | Yes | Yes |
| Update | Yes | Yes | Yes | Yes |
| UpdateBatch | Yes | Yes | Yes | Yes |
Figure 22 Error Object Properties
| Property | Description |
| Description | Descriptive text of the error that occurred. |
| HelpContext | The help topic in the help file that contains more information. |
| HelpFile | The filename of the help file that contains more information. |
| NativeError | An error code that is specific to that particular driver. |
| Number | A unique identifier that distinguishes the Error objects from each other. |
| Source | The name of the application or object that returned the error. |
| SQLState | The five-digit SQL error code. |
Figure 23 Decimal and Hexadecimal Error Code Values Returned by ADO
| Constant Name | Number | Hex | Description |
| adErrInvalidArgument | 3001 | 0x800A0BB9 | The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. |
| adErrNoCurrentRecord | 3021 | 0x800A0BCD | Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record. |
| adErrIllegalOperation | 3219 | 0x800A0C93 | The operation requested by the application is not allowed in this context. |
| adErrInTransaction | 3246 | 0x800A0CAE | The application may not explicitly close a Connection object while in the middle of a transaction. |
| adErrFeatureNotAvailable | 3251 | 0x800A0CB3 | The operation requested by the application is not supported by the provider. |
| adErrItemNotFound | 3265 | 0x800A0CC1 | ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application. |
| adErrObjectInCollection | 3367 | 0x800A0D27 | Can't append. Object already in collection. |
| adErrObjectNotSet | 3420 | 0x800A0D5C | The object referenced by the application no longer points to a valid object. |
| adErrDataConversion | 3421 | 0x800A0D5D | The application is using a value of the wrong type for the current operation. |
| adErrObjectClosed | 3704 | 0x800A0E78 | The operation requested by the application is not allowed if the object is . |
| adErrObjectOpen | 3705 | 0x800A0E79 | The operation requested by the application is not allowed if the object is open. |
| adErrProviderNotFound | 3706 | 0x800A0E7A | ADO could not find the specified provider. |
| adErrBoundToCommand | 3707 | 0x800A0E7B | The application cannot change the ActiveConnection property of a Recordset object with a Command object as its source. |
| adErrInvalidParamInfo | 3708 | 0x800A0E7C | The application has improperly defined a Parameter object. |
| adErrInvalidConnection | 3709 | 0x800A0E7D | The application requested an operation on an object with a reference to a closed or invalid Connection object. |