Dreamweaver MX and Cursors
When we create a new recordset, Ultradev uses default values for the CursorType, CursorLocation and LockType properties of the recordset. We would assume that the defaults would work for every occasion. This is not the case.
So, how do we change the defaults? Select the recordset in Server Behaviors window and then look in the Property Inspector. The properties of the recordset can easily be changed by selecting different values in the dropdowns or manually altering the recordset code.
So what do all the different values for CursorType, CursorLocation and LockType mean?
Cursor Types
Forward Only(0) - Ultradev default. Means you can only move forwards through the recordset.
Keyset(1) - allows you to move forwards and backwards through the recordset. It also allows you see changes to the recordset as the records are identified by keys. As such it is the least efficient of the CursorTypes and shouldn't really be used.
Dynamic(2) - allows you to move forwards or backwards through the recordset. Any changes to the recordset will also be visible.
Static(3) - allows you move forwards or backwards through the recordset. However, this cursortype takes a static snapshot of the records at the time the recordset is created. So, any changes to the recordset will not be seen until the recordset is recreated.
In general, the Forward-only cursortype should be used. Next best is to use the Static cursortype.
Cursor Locations
Server(2) - Ultradev default. The manipulation of the records is handled by the database on the server.
Client(3) - the recordset is copied to the client and record manipulation is handled by client-based cursors.
In general, it best to the Server cursorlocation as it is more efficient. The Client cursorlocation can be very inefficient especially when dealing with large recordsets. But, sometimes it is neccessary to use it - when using SQL Server databases for example.
Lock Types
Read-only(1) - The recordset will be read-only.
Pessimistic(2) - the record is locked as soon as the change takes place.
Optimistic(3) - Ultradev default. The record is not locked until the change has finished taking place.
Batch Optimistic(4) - allows multiple records to be changed using the BatchUpdate recordset method.
Although Ultradev uses a default of Optimistic(3), it would be better to use Read-only(1). Unless, of course, one is going to use the Recordset AddNew and Update methods to change records within the recordset.
Why would we want to change the defaults?
1. Displaying multiple instances of Recordset field. Newcomers to Ultradev are often confused why this code doesn't work:
<%
if Recordset1.Fields.Item("Field1").Value <> "" then
Response.Write "" & Recordset1.Fields.Item("Field1").Value & ""
end if
%>
When using the Forward-Olny cursortype, the second instance of the recordset field has a null value. This can be rectified by using a Static cursortype or holding the recordset value in a local variable:
<%
tmpVar= Recordset1.Fields.Item("Field1").Value & ""
if tmpVar<> "" then
Response.Write "" & tmpVar & ""
end if
%>
2. Displaying ntext fields in SQL Server. When upsizing an Access database to SQL Server, memo fields are converted to ntext fields. When using Ultradev default recordset to show contents of ntext field:
<%=(Recordset1.Fields.Item("ntextField").Value)%>
an error may be thrown or the data not shown. The solution is to use a cusrorlocation of Client(3) or put the ntext at the end of list of field in SQL statement:
select MyNumberField,MyOtherField,MyntextField from MyTable
Personally, I tend to nvarchar datatype instead of ntext unless the record will contain huge amounts of data.
3. When using an OLEDB connection to Access database. It is imperitive to use a Read-only locktype when using an OLEDB connection to an Access database. This is because the Ultradev defaults are not supported by this driver. The forward-only cursortype is automatically converted to a Keyset cursortype 'behind one's back'. This can cause unexplained EOF/BOF errors with certain Ultradev behaviours.
4. When using an SQLOLEDB connect to an SQL Server database. An exception may be thrown when using Ultradev default recordset, SQLOLEDB connection and Recordset AddNew/Update methods. This may be overcome by using a Client(3) cursorlocation.
Online References:
http://www.4guysfromrolla.com/webtech/112998-1.shtml
http://www.adopenstatic.com/faq/jetcursortypes.asp
