防止重复输入数据库 [英] prevent duplicate entries to database

问题描述

我想使用vb.net和MySQL作为数据库来防止清单表中出现重复条目​​，这是我的代码:

 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. /p>

有人可以检查为什么不阅读插入内容吗

我的数据库表值:

pcode =主键

lname = longtext

fname = longtext

office = longtext

名称=长文本

非常感谢您的帮助，

解决方案

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

数据库具有内置系统，可防止数据重复.这是通过主键或唯一键约束实现的.对于您的情况，您已经创建了一个主键.因此，绝对不需要您执行SELECT COUNT(*)查询.

相反，只需将其直接插入表中，并在pcode已经存在时捕获完整性错误即可.

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手册页 PRIMARY KEY和UNIQUE索引约束

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

end sub

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,

my Database tables values:

pcode = primary key

lname = longtext

fname = longtext

office = longtext

designation = longtext

any help will be much appreciated, thanks,

解决方案

Sorry to say this is the wrong approach.

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.

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

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

这篇关于防止重复输入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！