防止重复进入数据库

2021-12-06 00:00:00 validation vb.net mysql

我想防止使用 vb.net 和 MySQL 作为数据库在我的库存表单中重复输入，这是我的代码:

I want to prevent duplicate entries to my inventory form using vb.net and MySQL as the database, here is my code:

 Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    Dim myCommand As New MySqlCommand
    Dim conn As MySqlConnection
    Dim i As String
    conn = New MySqlConnection
    conn.ConnectionString = "server = localhost;username= root;password= a;database= secret"
    Try
        conn.Open()
    Catch mali As MySqlException
        MsgBox("connot establish connection")
    End Try

    Dim intReturn As Integer
    Dim strSql As String = " select * from personnel where pcode = @pcode"

    Dim sqlcmd As New MySqlCommand(strSql, conn)
    With sqlcmd.Parameters
        .AddWithValue("@pcode", CType(pcode.Text, String))
    End With

    intReturn = sqlcmd.ExecuteScalar

    If (intReturn > 0) Then
        cmd = New MySqlCommand("Insert into personnel values('" & pcode.Text & "','" & lname.Text & "','" & fname.Text & "','" & office.Text & "','" & designation.Text & "')")
        i = cmd.ExecuteNonQuery


        If pcode.Text <> "" Then
        ElseIf i > 0 Then
            MsgBox("Save Successfully!", MessageBoxIcon.Information, "Success")
            mrClean()
            ListView1.Tag = ""
            Call objLocker(False)
            Call LVWloader()
            Call calldaw()
        Else
            MsgBox("Save Failed!", MessageBoxIcon.Error, "Error!")
        End If
    Else
        MsgBox("Personnel ID Already Exist!", MessageBoxIcon.Error, "Error!")

    End If

结束子

我在搜索答案时发现了这个，但是当我尝试运行它时，它不会读取插入命令，而是直接进入 msbox人员 ID 已经存在"，即使没有相同的人员 ID.

i found this while i search for answer, but when i tried to run it, it does not read the insert command but rather it goes directly to the msbox "Personnel ID Already Exist" even if theres no thesame Personnel ID.

有人可以检查为什么它不读取插入内容，

can someone check why it does not read the insert please,

我的数据库表值:

pcode = 主键

lname = 长文本

lname = longtext

fname = 长文本

fname = longtext

办公室 = 长文本

指定 = 长文本

非常感谢任何帮助，谢谢，

any help will be much appreciated, thanks,

推荐答案

很抱歉，这是错误的方法.

Sorry to say this is the wrong approach.

数据库有一个内置系统来防止数据被复制.那是通过主键或唯一键约束.在您的情况下，您已经创建了一个主键.因此，您绝对不需要执行该 SELECT COUNT(*) 查询.

Databases have a built in system to prevent data being duplicated. That's through primary keys or unique key constraints. In your case, you have already created a primary key. So there is absolutely no need for you to do that SELECT COUNT(*) query.

相反，当pcode已经存在时，直接插入表并捕获完整性错误.

Instead, just directly insert into the table and catch the integrity error when the pcode already exists.

Try
    cmd = New MySqlCommand("Insert into personnel values('" & pcode.Text & "','" & lname.Text & "','" & fname.Text & "','" & office.Text & "','" & designation.Text & "')")

    i = cmd.ExecuteNonQuery


    If pcode.Text <> "" Then
    ElseIf i > 0 Then
        MsgBox("Save Successfully!", MessageBoxIcon.Information, "Success")
        mrClean()
        ListView1.Tag = ""
        Call objLocker(False)
        Call LVWloader()
        Call calldaw()
    Else
        MsgBox("Save Failed!", MessageBoxIcon.Error, "Error!")
    End If
Catch ex As MySqlException
    MsgBox("Personnel ID Already Exist!", MessageBoxIcon.Error, "Error!")
End Try

另请参阅 MySQL 手册页 PRIMARYKEY 和 UNIQUE 索引约束

Please also refer to the MySQL Manual Page PRIMARY KEY and UNIQUE Index Constraints

相关文章