Validating data before updating to the database

Any changes made to the current row are updated to the database when the user moves to another row or when your code executes the Edit and Update methods on the Data control's Recordset:

Example Title	Copy Code
Example Title

Before any changes are made to the database, the Data control's Validate event will fire, followed by True DBGrid's BeforeUpdate event. Either of these events can be used to cancel the update to the database by setting the Cancel argument to True.

Example Title	Copy Code
Private Sub TDBGrid1_BeforeUpdate(Cancel As Integer)

If the update succeeds, the grid's AfterUpdate event will fire:

Example Title	Copy Code
Private Sub TDBGrid1_AfterUpdate()

and the current cell moves to a new location when appropriate.

You can use similar True DBGrid events to validate the data before adding or deleting a row from the database:

Example Title	Copy Code
Private Sub TDBGrid1_BeforeInsert(Cancel As Integer) Private Sub TDBGrid1_AfterInsert() Private Sub TDBGrid1_BeforeDelete(Cancel As Integer) Private Sub TDBGrid1_AfterDelete()

Alternatively, you can use the Data control's Validate event to accept or reject the changes to the database. Internally, True DBGrid uses bookmarks to navigate through the database. Thus, when a Data control's Validate event is fired in response to a row change in the grid, the Action argument will be vbDataActionBookmark, which indicates that the Bookmark property of the underlying Recordset has changed. You can cancel the movement by setting the Action argument to zero. In this case, the grid will keep the original row current, rather than changing to the newly selected row. The Save argument will be True if data in a bound control has changed and is about to be written to the database. If you set the Save argument to False, any user edits will remain, and the user will need to correct the data before continuing. This is how invalid data is typically handled. See the Visual Basic Help for more details on using the Data control's Validate event.

The following example demonstrates how to use the BeforeUpdate event to validate an entire row of data. Whenever the user attempts to update the current row, a message box is displayed, asking the user if the record should be saved. If the user chooses Yes, the update proceeds. If the user chooses No, the update is canceled, and the grid's current row does not change.

Example Title	Copy Code
Dim UpdateFlag As Boolean Private Sub TDBGrid1_BeforeUpdate(Cancel As Integer) Dim MsgText As String, MsgCaption As String Dim Response As Integer, MsgBoxType As Integer MsgText = "Do you want to save this record?" MsgBoxType = vbYesNo + vbQuestion MsgCaption = "Confirm Update" Response = MsgBox(MsgText, MsgBoxType, MsgCaption) If Response = vbNo Then Cancel = True UpdateFlag = True End If End Sub Private Sub TDBGrid1_Error(ByVal DataError As Integer, _ Response As Integer) ' Error triggered in BeforeUpdate. If UpdateFlag Then ' Don't display default message. Response = 0 ' Clear flag for next update. UpdateFlag = False End If End Sub

Example Title

Copy Code

Dim UpdateFlag As Boolean

 

Private Sub TDBGrid1_BeforeUpdate(Cancel As Integer)

    Dim MsgText As String, MsgCaption As String

    Dim Response As Integer, MsgBoxType As Integer

 

    MsgText = "Do you want to save this record?"

    MsgBoxType = vbYesNo + vbQuestion

    MsgCaption = "Confirm Update"

 

    Response = MsgBox(MsgText, MsgBoxType, MsgCaption)

 

    If Response = vbNo Then

        Cancel = True

        UpdateFlag = True

    End If

End Sub

 

Private Sub TDBGrid1_Error(ByVal DataError As Integer, _

        Response As Integer)

 

    ' Error triggered in BeforeUpdate.

    If UpdateFlag Then    

 

        ' Don't display default message.

        Response = 0      

 

        ' Clear flag for next update.

        UpdateFlag = False

    End If

End Sub

Unlike the BeforeColUpdate event, displaying a message box in the BeforeUpdate event does not cause recursion problems.

The Error event is included in this example because it will fire as a result of the update being canceled. The Boolean variable UpdateFlag is used to suppress the default error message for the BeforeUpdate case but not for other kinds of errors. For more information, see Handling Database Errors.