如何为重复id添加错误消息框

Question

这是我为数据库输入记录的代码。如果我放一个副本，它只会终止程序。我的目标是放置一个显示错误的消息框，这样程序就不会终止。

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

Answer 1

添加一个MySqlDataReader类，以便程序可以读取数据库中是否存在任何现有数据

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

Answer 2

如果所有文本框都用于插入数据，则可以循环使用文本框来添加错误消息框。

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

您还可以使用"Tag"或"TabIndex"属性来标识每个文本框。

Answer 3

首先运行验证代码，如果可以，则尝试插入新用户。Insert语句只会在id不存在的情况下添加用户。

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