[RESOLVED] error handling when trying to add a new row with incorrect datatype

Hello everyone,

I have a Windows application that inserts new data to a mySQL database.
When the user clicks the "Add" button on the main form, a new form (Insert Form) appears. This new form contains a list of labels (for the column names) and a list of blank textboxes.
The user then fills these textboxes with the values they want to insert into the DB table.
I am using a BindingSource to update the DataGridView on the main form and the actual mySQL database.

The code is shown below.

If the user attempts to insert a new record with a wrong datatype (for example, a STRING value when the column is of datatype INTEGER), they get an error message.
When they hit "Retry" or "Cancel" on the error message, both the error message and the Insert Form close.
When they close the error message and refresh the DataGridView, they can see that the program has duplicated the last row in the table, and a new row has been added.

I have 2 problems:
1. I want just the error message to close, not the insert form.
2. When the user encounters the dataType error, I do NOT want the last row to be duplicated in the DataGridView. I have absolutely no idea why it's doing this.

Any help would be greatly appreciated.

Thank you in advance.

Code:

---

Public Class insertPopup

    Public Property dtableMemVar As DataTable

    Public Sub New(ByVal dtable As DataTable)
        'This call is required by the Windows Form Designer.
        InitializeComponent()

        Dim dcHeader, dc As DataColumn
        Dim i As Integer
        Dim dt As DataTable = dtable

        dtableMemVar = dtable

        TableLayoutPanel1.RowCount = dt.Columns.Count
        TableLayoutPanel1.ColumnCount = 2

        While TableLayoutPanel1.RowStyles.Count < TableLayoutPanel1.RowCount
            TableLayoutPanel1.RowStyles.Add(New RowStyle())
        End While

        For Each style As RowStyle In TableLayoutPanel1.RowStyles
            style.SizeType = SizeType.Percent
            style.Height = 100.0F
        Next

        dcHeader = dt.Columns(0) 'get the ID column
        Dim idLabel As New Label()
        idLabel.Text = (dcHeader.ColumnName + ":").ToString 'display ID column name
        idLabel.Size = New System.Drawing.Size(70, 17)
        TableLayoutPanel1.Controls.Add(idLabel, 0, i)

        Dim lastRow As DataRow = dt.Rows(dt.Rows.Count - 1)

        Dim idTextbox As New TextBox()
        idTextbox.Size = New System.Drawing.Size(170, 17)
        idTextbox.Text = lastRow(0) + 1 'grabs the last ID in the dgv, then +1 to that
        idTextbox.Name = ("textbox0").ToString
        idTextbox.ReadOnly = True
        TableLayoutPanel1.Controls.Add(idTextbox, 1, i)

        For i = 1 To TableLayoutPanel1.RowCount - 1
            dc = dt.Columns(i)
            Dim label As New Label()
            label.Text = (dc.ColumnName + ":").ToString
            label.Size = New System.Drawing.Size(70, 17)
            TableLayoutPanel1.Controls.Add(label, 0, i)

            Dim textbox As New TextBox()
            textbox.Size = New System.Drawing.Size(170, 17)
            textbox.Text = ""
            textbox.Name = ("textbox" & i).ToString
            TableLayoutPanel1.Controls.Add(textbox, 1, i)
        Next

    End Sub

    Private Sub addRow()

      On Error GoTo driveError

        Dim dt As DataTable = dtableMemVar
        Dim dc As DataColumn
        Dim newrow As DataRow = dt.NewRow()

        For i = 0 To dt.Columns.Count - 1

            Dim textbox As TextBox = TableLayoutPanel1.Controls.Item("textbox" & i)
            textbox.Name = ("textbox" & i).ToString
            dc = dt.Columns(i)
            newrow(dc.ColumnName) = textbox.Text

        Next
        dt.Rows.Add(newrow)

driveError:
        Dim response As Integer, description As Integer
        description = vbExclamation + vbRetryCancel
        response = MsgBox(Err.Description, description, "Error") 'else output error message and retry
    End Sub

    Private Sub addButton_Click(sender As System.Object, e As System.EventArgs) Handles addButton.Click
        addRow()
    End Sub

End Class

---

First up, using 'On Error Goto' in VB.NET is a bit of a sin. If you want to handle exceptions in VB.NET then you really should be using structured exception handling, i.e. Try...Catch blocks. In this case though, there's no need for any exception handling because there's no need for any exceptions. Remember that prevention is almost always better than cure: unless it would be very hard work to do so, always do what you can to prevent an exception before it happens rather than cleaning up afterwards.

What you should be doing is handling the Validating event of your controls and validating their contents there. If the data is not valid then you set e.Cancel to True and the user won't be able to leave the control until they enter valid data. That guarantees that they won't be able to do something like enter non-numeric data where a number is required.

To ensure that even controls that the user doesn't visit get validated, you call the form's ValidateChildren method before using the data. That will raise the Validating event on every control and return False if any of them fail, letting you know not to proceed with using the data.

Could you please give me some sample code for the data validation? (handling the validating event of controls)
Because I am working with dynamic data, the control names depend on what database table is chosen by the user. I am having difficulty using the data validation because of this.
Thanks so much.

You can use a single Validating event handler for all the controls and attach it to each one using AddHandler. Make sure that you use RemoveHandler to detach each one when you're done.

In the event handler you use the 'sender', which is the object that raised the event, to get the data and determine what type it should be. The logical way to do that would be to put the TextBoxes in an array, determine the index of the TextBox being validated, get the DataColumn from the row with the same index and check its DataType. You would then validate the data accordingly. That might involve a series of If statements and then calling one of several validation methods, one for each data type. You can then cancel the event if the validation fails.

It's very important that you don't try to do this all at once. One of the primary reasons that beginners have issues is because they treat things like this as one big issue rather than the multiple smaller issues that it really is. You divide and conquer, solving each smaller problem and then putting the solutions together to inherently solve the big problem.

You also don't have to, and often shouldn't, work directly with your "production" project. Developers often put their project aside and create a small test rig to work on one feature in isolation. Once they know they have it working, they will then implement it in the original project. You should probably create two separate test projects. The first one would allow you to work on adding and removing event handlers dynamically. I'd use the Click event of a Button to begin with. You can then add another Button and work on handling the same event for multiple controls with one method. The other project would allow you to work on validation and validating multiple data types. Once you're comfortable with both, then you can combine them into your original project. Welcome to software development and the reason that it's not just programming.

Thank you for all your invaluable advice. I'll try to implement your suggestion in code and come back if I run into any trouble.