How to Add an error message box for duplicate id?

Question

this is my code that inputs a record for my database. if I put a duplicate it will just terminate the program. my goal is to put a message box that displays an error so that the program will not terminate.

   Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

    MysqlConn = New MySqlConnection("server=localhost; user=root; pwd=; database=payrollmanagement")
    MysqlConn.Open()
    command = New MySqlCommand("INSERT INTO emp_info(empID,lastname,firstname,MiddleInitial,Age,address,Position,ContactNumber,pay_type,gender,dept,email) Values ('" & TextBox1.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox11.Text & "','" & TextBox3.Text & "','" & TextBox10.Text & "','" & TextBox4.Text & "','" & TextBox8.Text & "','" & TextBox10.Text & "','" & TextBox2.Text & "','" & TextBox14.Text & "')", MysqlConn)
    If (TextBox1.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox2.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox3.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox4.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox5.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox6.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox7.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox8.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox9.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox10.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox11.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ElseIf (TextBox14.Text = "") Then
        MessageBox.Show("Data not Inserted")
    Else
        command.ExecuteNonQuery()
        MessageBox.Show("Data Inserted")
        Shadows_load()
    End If
End Sub

Lot of problems here. You need to add a try/catch to capture the exception. But this is the minor of your problems. Let's try to add a single quote in the textbox that you use for the last name. Or worse let's type some sql text like in [this comic](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). And finally, why do you let your user type the ID? Let the database choose one marking the column with AUTOINCREMENT=True — Steve, Jan 11 '21 at 09:38
The problem with your string concatenation of the textboxes values could be solved only using a parameterized query https://stackoverflow.com/questions/16167924/c-sharp-with-mysql-insert-parameters — Steve, Jan 11 '21 at 09:42
You have opened a connection and then show up to a dozen message boxes. Why? You execute the command without allowing the user to correct the problems. Connections are precious resources. They should only be opened directly before the execute and closed and disposed as soon as possible. — Mary, Jan 12 '21 at 01:19

score 0 · Answer 1 · answered Jan 11 '21 at 10:30

Add a MySqlDataReader class so that the program can read if there is any existing data in the database

MysqlConn = New MySqlConnection("//YourConnection")
MysqlConn.Open()
command = New MySqlCommand("//Your sql")
MySqlDataReader reader;
reader = command.ExecuteReader()

If reader.Read() = true Then 'This will solve your problem
   MessageBox.Show("There is already existing data.")
Else
 If (TextBox1.Text = "") Then
        MessageBox.Show("Data not Inserted")
    ...
    Else
        command.ExecuteNonQuery()
        MessageBox.Show("Data Inserted")
        Shadows_load()
    End If
End If

You can do the Insert with a Where clause `WHERE NOT EXISTS` and a sub query and save an extra hit to the database. — Mary, Jan 12 '21 at 04:40

score 0 · Answer 2 · answered Jan 12 '21 at 03:04

0

If all textboxes are used to insert data, you can loop through textboxes to add error message boxes.

    For Each c As Control In Controls
        If TypeOf c Is TextBox Then
            If c.Text = "" Then
                MessageBox.Show("Data not Inserted")
            End If
        End If
    Next

You can also use 'Tag' or 'TabIndex' property to identify each textbox.

answered Jan 12 '21 at 03:04

Xingyu Zhao

625
7
27

You can shorten this for `For Each tb In Controls.OfType(Of TextBox)` – Mary Jan 12 '21 at 04:37

score 0 · Answer 3 · answered Jan 12 '21 at 06:17

First the validation code is run and if it is OK then attempt to insert the new user. The Insert statement will only add the user if the id does not exist.

Private Function ValidateInput() As Boolean
    For Each tb In Controls.OfType(Of TextBox)
        If tb.Text = "" Then
            MessageBox.Show("Data not Inserted")
            tb.Focus()
            Return False
        End If
    Next
    Return True
End Function

Private Function InsertUser(id As String, lname As String, fname As String, middle As String) As Integer
    Dim sql = "INSERT INTO dbusers (empID, lastname, firstname, MiddleInitial)
                Select @ID, @LName, @FName @MiddleIn
                WHERE Not EXISTS
                (SELECT * FROM dbusers WHERE empID = @ID);"
    Dim RetVal As Integer
    Using cn As New MySqlConnection("server=localhost; user=root; pwd=; database=payrollmanagement"),
            cmd As New MySqlCommand(sql, cn)
        With cmd.Parameters
            .Add("@Id", MySqlDbType.VarChar).Value = id
            .Add("@LName", MySqlDbType.VarChar).Value = lname
            .Add("@FName", MySqlDbType.VarChar).Value = fname
            .Add("@MiddleInitial", MySqlDbType.VarChar).Value = middle
        End With
        cn.Open()
        RetVal = cmd.ExecuteNonQuery()
    End Using
    Return RetVal
End Function

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    If ValidateInput() Then
        Dim RowsEffected = InsertUser(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)
        If RowsEffected = 1 Then
            MessageBox.Show("Successful insert.")
        Else
            MessageBox.Show("Error with insert.")
        End If
    End If
End Sub

How to Add an error message box for duplicate id?

3 Answers3