UpdateRecord Method

The UpdateRecord method is a public subroutine that modifies a critique record in the Critique table of the FmLib database. The update is performed by opening an ADODB recordset (oRs), assigning the concatenation of the string "Critique#=" and the CritiqueNo (passed as a parameter) to the Filter property of the oRs recordset, and executing the Update method of the oRs recordset. DeleteRecord Method describes the benefits of using the adCmdTable option.

oRs.Filter = "Critique#=" & CritiqueNo
oRs.Open "Critique", Me.ConnectionString, adOpenKeyset, adLockOptimistic, adCmdTable

The Open method of oRs uses adCmdTable in the options parameter. The alternative to adCmdTable is adCmdTableDirect, which was introduced with ADO 2.0 and allows the command text — "Critique" in our example — to be interpreted directly as a table. When the Filter property is applied before a recordset is opened there is no advantage to using adCmdTableDirect; in that case adCmdTable and adCmdTableDirect provide similar performance.

If a filter was not applied this statement could use adCmdTableDirect in place of adCmdTable. The difference is that adCmdTable sends a "select * from Critique" statement to the provider and the ADODB recordset accesses the result of the select statement, whereas adCmdTableDirect sends only "Critique" to the provider and has no intermediary results, but accesses the table directly. Using adCmdTableDirect gives significant performance gains when no filter is applied.

The UpdateRecord method is called for two reasons:

• The reviewer has edited a previous critique and saved it to the public folder. All columns, isApproved, objectId, title, rating, and dataOfCritique in the Critique table of the FmLib database are updated with parameter values.
• The approver has approved a critique and it is reposted to the LitCrit public folder. Only the isApproved and objectId columns in the Critique table are updated with parameter values.

' It is update from reviewer
If CritiqueTitle <> "" Then
   oRs("isApproved") = IIf(IsApproved, 1, 0)
   oRs("objectId") = MessageID
   oRs("Title") = CritiqueTitle
   oRs("Rating") = OverAllRating
   oRs("dateOfCritique") = CStr(CritiqueDate)
' It is update from approver
ElseIf IsApproved Then
   oRs("isApproved") = 1
   oRs("objectID") = MessageID
Else
   ' Not approved, should use DeleteRecord instead
   Err.Raise NERR_NOUPDATE, "UpdateRecord", SERR_NOUPDATE
End If

Note  A total of seven parameters are passed to the UpdateRecord method, but only CritiqueNo, MessageID, and IsApproved are required. The last four parameters in the list, OverAllRating, CritiqueTitle, CritiqueDate, and ConnectionString, are optional and are not passed to the UpdateRecord method when the update is from the approver.

The Critique component is instantiated in the server-side script that runs when the Folder_OnMessageCreated and Message_OnChange events fire because critiques are posted to the LitCrit public folder or critiques are revised and saved in the LitCrit public folder.

The complete code for the UpdateRecord method is available in Critique.cls Server-Side COM Component in the code section.