CursorTypes available with Jet (Code)

<%
Option Explicit
Response.Buffer = True

Call Main()

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' Sub Main
' Accepts no params
' Handles errors itself
' Sets global connection string and initialises arrays
' containing cursor types and locktypes. Opens a connection
' to DB Iterate through the cursor and locktype arrays
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Sub Main( _
 )

 On Error Resume Next

 Dim objConn
 Dim strConnect
 Dim arrCursorTypes(3)
 Dim arrLockTypes(3)
 Dim i
 Dim j

 strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=c:\databases\db1.mdb;"

 arrCursorTypes(0) = adOpenForwardOnly
 arrCursorTypes(1) = adOpenStatic
 arrCursorTypes(2) = adOpenKeyset
 arrCursorTypes(3) = adOpenDynamic

 arrLockTypes(0) = adLockReadOnly
 arrLockTypes(1) = adLockOptimistic
 arrLockTypes(2) = adLockPessimistic
 arrLockTypes(3) = adLockBatchOptimistic

 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open strConnect

 Response.Write("<html><body>" & vbCrLf)

 For i = 0 to UBound(arrCursorTypes)
 For j = 0 to UBound(arrLockTypes)
 Call subGetRSDetails(objConn, arrCursorTypes(i), arrLockTypes(j))
 Next
 Next

 objConn.Close
 Set objConn = Nothing

 Response.Write("</body></html>" & vbCrLf)

 If Err.number <> 0 then
 Response.Clear
 Response.Write(Err.Number & " " & Err.Description)
 Response.Flush
 End If

End Sub

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' Sub subGetRSDetails
' Accepts objConn as ADODB Connection
' Accepts intCursorType as a valid ADO CursorTypeEnum
' Accepts intLockType as a valid ADO LockTypeEnum
' Handles errors itself
' Opens a recordset object based on the params and
' reports the requested cursor/lock types and the
' resultant cursor/lock types
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Sub subGetRSDetails( _
 ByRef objConn, _
 ByVal intCursorType, _
 ByVal intLockType _
 )

 On Error Resume Next
 Dim objRS

 Set objRS = Server.CreateObject("ADODB.Recordset")
 objRS.Open "test", objConn, intCursortype, intLockType, adCmdTable

 With Response
 .Write("Requested CursorType = " & fncGetNiceCursorType(intCursorType) & " " & vbCrLf)
 .Write("Requested LockType = " & fncGetNiceLockType(intLockType) & " " & vbCrLf)
 .Write("Actual CursorType = " & fncGetNiceCursorType(objRS.CursorType) & " " & vbCrLf)
 .Write("Actual LockType = " & fncGetNiceLockType(objRS.LockType) & " " & vbCrLf)
 End With

 objRS.close
 Set objRS = Nothing

 If Err.number <> 0 then
 Response.Clear
 Response.Write(Err.Number & " " & Err.Description)
 Response.Flush
 End If

End Sub

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' Function fncGetNiceCursorType
' Accepts intCursorType as a valid ADO CursorTypeEnum
' Returns the ADO named constant for the given
' cursortype
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Function fncGetNiceCursorType( _
 ByVal intCursorType _
 )

 Select Case intCursorType
 Case 0
 fncGetNiceCursorType = "adOpenForwardOnly"
 Case 1
 fncGetNiceCursorType = "adOpenKeyset"
 Case 2
 fncGetNiceCursorType = "adOpenDynamic"
 Case 3
 fncGetNiceCursorType = "adOpenStatic"
 Case Else
 fncGetNiceCursorType = "Other?!?"
 End Select

End Function

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' Function fncGetNiceLockType
' Accepts intLockType as a valid ADO LockTypeEnum
' Returns the ADO named constant for the given
' locktype
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Function fncGetNiceLockType( _
 ByVal intLockType _
 )

 Select Case intLockType
 Case 1
 fncGetNiceLockType = "adLockReadOnly"
 Case 2
 fncGetNiceLockType = "adLockPessimistic"
 Case 3
 fncGetNiceLockType = "adLockOptimistic"
 Case 4
 fncGetNiceLockType = "adLockBatchOptimistic"
 Case Else
 fncGetNiceLockType = "Other?!?"
 End Select

End Function
%>

Back to Jet Cursor types | Back to FAQ listing