mysql - mysql中的行重复和行不匹配

Question

我的问题是我在 MySQL 中的行在注册后被重复和不匹配，例如 1 人有 3 只鸡，它在 datagridview 中显示为 1 人有 3 只鸡但是如果我添加另一个 1 人有 3 只鸡，它们都会被重复两次（所以12 行而不是 6 行），对于行不匹配的示例是Aentryname 有 B、C、D 鸡和Bentryname 有 E、F、G 鸡和Bentryname 也会有 B、C、D 鸡我希望这能澄清一切，并为混淆抱歉它显示在我在这里发布的图像中 DGVdups_and_mismatch

这个按钮添加点击

    Private Sub ButtonAdd_Click(sender As Object, e As EventArgs) Handles ButtonAdd.Click
        Dim chicken As New cockeryclass
        Dim dumbbell As New ChickenClass

        Dim fname As String = TextBoxfname.Text
        Dim lname As String = TextBoxlname.Text
        Dim nume As Integer = TextBoxNume3.Text
        Dim ename As String = TextBoxEntryname.Text

        Dim Chicken1 As String = TextBoxChicken1.Text
        Dim Chicken2 As String = TextBoxChicken2.Text
        Dim Chicken3 As String = TextBoxChicken3.Text


        Dim eno1 As String = TextBoxEntryNo1.Text
        Dim eno2 As String = TextBoxEntryNo2.Text
        Dim eno3 As String = TextBoxEntryNo3.Text

        Dim weightlb1 As String = ComboBoxWeightlb1.Text
        Dim weightlb2 As String = ComboBoxWeightlb2.Text
        Dim weightlb3 As String = ComboBoxWeightlb3.Text


        Dim weightoz1 As String = ComboBoxWeightOz1.Text
        Dim weightoz2 As String = ComboBoxWeightOz2.Text
        Dim weightoz3 As String = ComboBoxWeightOz3.Text




        If verif() And chicken.InsertRooster(fname, lname, nume, ename) And dumbbell.insertbreedenolboz1(Chicken1, eno1, weightlb1, weightoz1) And dumbbell.insertbreedenolboz2(Chicken2, eno2, weightlb2, weightoz2) And
            dumbbell.insertbreedenolboz3(Chicken3, eno3, weightlb3, weightoz3) Then

            MsgBox("error")
        Else
            MsgBox("success")

        End If

    End Sub
    Function verif() As Boolean

        If TextBoxfname.Text.Trim = "" Or TextBoxlname.Text.Trim = "" Or TextBoxEntryname.Text.Trim = "" Then
            Return False
        Else
            Return True
        End If

    End Function


End Class```

this is my 2 classes cockeryclass and Chickenclass


```Imports MySql.Data.MySqlClient
Public Class ChickenClass
    Dim db As New MYCON
    Public Function insertbreedenolboz1(ByVal breed1 As String, ByVal eno1 As String, ByVal weightlb1 As String, ByVal weightoz1 As String) As Boolean

        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`,`chicken`,`lb`,`oz`,`financer_F_id`) VALUES (@eno,@ckn,@lb,@oz)", db.getconnection)
            Command.Parameters.Add("@ckn", MySqlDbType.String).Value = breed1
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno1
            Command.Parameters.Add("@lb", MySqlDbType.String).Value = weightlb1
            Command.Parameters.Add("@oz", MySqlDbType.String).Value = weightoz1


            db.openConnection()

            If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function

    Public Function insertbreedenolboz2(ByVal breed2 As String, ByVal eno2 As String, ByVal weightlb2 As String, ByVal weightoz2 As String) As Boolean
        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`,`chicken`,`lb`,`oz`,`financer_F_id`) VALUES (@eno,@ckn,@lb,@oz)", db.getconnection)
            Command.Parameters.Add("@ckn", MySqlDbType.String).Value = breed2
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno2
            Command.Parameters.Add("@lb", MySqlDbType.String).Value = weightlb2
            Command.Parameters.Add("@oz", MySqlDbType.String).Value = weightoz2


            db.openConnection()

            If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function
    Public Function insertbreedenolboz3(ByVal breed3 As String, ByVal eno3 As String, ByVal weightlb3 As String, ByVal weightoz3 As String) As Boolean
        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`,`chicken`,`lb`,`oz`,`financer_F_id`) VALUES (@eno,@ckn,@lb,@oz)", db.getconnection)
            Command.Parameters.Add("@ckn", MySqlDbType.String).Value = breed3
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno3
            Command.Parameters.Add("@lb", MySqlDbType.String).Value = weightlb3
            Command.Parameters.Add("@oz", MySqlDbType.String).Value = weightoz3


            db.openConnection()
If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If

        End Using

    End Function

End Class```

```Imports MySql.Data.MySqlClient
Public Class cockeryclass
    Dim db As New MYCON
    Public Function InsertRooster(ByVal fname As String, ByVal lname As String, ByVal nume As String, ByVal ename As String) As Boolean

        Using command As New MySqlCommand("Insert into `derbabe`.`financer`( fname, lname,entry_name,num_entries) values (@fn,@ln,@ename,@nume);", db.getconnection)

            command.Parameters.Add("@fn", MySqlDbType.VarChar).Value = fname
            command.Parameters.Add("@ln", MySqlDbType.VarChar).Value = lname
            command.Parameters.Add("@ename", MySqlDbType.VarChar).Value = ename
            command.Parameters.Add("@nume", MySqlDbType.String).Value = nume

            db.openConnection()

            If command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function

    Public Function inserteno1(ByVal eno1 As String) As Boolean
        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`) VALUES (@eno)", db.getconnection)
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno1
            db.openConnection()
            If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function

    Public Function inserteno2(ByVal eno2 As String) As Boolean
        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`) VALUES (@eno)", db.getconnection)
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno2
            db.openConnection()
            If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function

    Public Function inserteno3(ByVal eno3 As String) As Boolean
        Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`) VALUES (@eno)", db.getconnection)
            Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno3
            db.openConnection()
            If Command.ExecuteNonQuery() = 1 Then
                Return True
            Else
                Return False
            End If
        End Using

    End Function

    Function getfighters(ByVal command As MySqlCommand) As DataTable

        command.Connection = db.getconnection
        Dim adapter As New MySqlDataAdapter(command)
        Dim tbl As New DataTable

        adapter.Fill(tbl)

        Return tbl
    End Function

End Class

标签： mysqlvb.netdatagridview

Answer 1

我在您的代码中看到的一些问题...

If verif() And chicken.InsertRooster(fname, lname, nume, ename) '...

更改您的 If 语句以AndAlso在条件之间使用。这会使 If 短路，因为它一旦找到 a 就会退出False。这样，如果verif返回，您的数据库函数将不会被执行False。

If TextBoxfname.Text.Trim = "" Or TextBoxlname.Text.Trim = "" '...

同样，OrElse一旦条件返回，就会将 If 短路True。

Dim nume As Integer = TextBoxNume3.Text

a的.Text属性TextBox是 a String。使用Option Stricton （并且应该打开），您不能将 an 分配给Stringtype 的变量Integer。

If verif() AndAlso chicken.InsertRooster(fname, lname, nume, ename) '...

和

Public Function InsertRooster(ByVal fname As String, ByVal lname As String, ByVal nume As String, '...

您不能将和Integer( nume) 传递给Function期望String( ByVal nume As String) 的 a。我将声明更改nume为String.

      MsgBox("error")
    Else
        MsgBox("success")

我认为您的消息框颠倒了。

insertbreedenolboz1
insertbreedenolboz2
insertbreedenolboz3

每个函数都做同样的事情。您只是发送不同的参数。删除最后 2 个并将您的按钮代码更改为仅调用insertbreedenolboz1.

inserteno1
inserteno2
inserteno3

和上面一样。删除最后 2 个，然后调用inserteno1.

"INSERT INTO `rooster` (`entry_no`,`chicken`,`lb`,`oz`) VALUES (@eno,@ckn,@lb,@oz)"

此插入语句将不起作用，因为您有 5 个字段但只有 4 个参数。我真的无法在不知道更多的情况下解决这个问题，所以我只是删除了financer_F_id.

我摆脱了任何东西db.Connection，并使用了每个方法的本地连接，以便可以关闭和处理它。连接字符串是类级别的变量。

您的课程可以合并为一个DataAccess课程。为每个方法创建一个连接，以便您可以正确关闭和处置连接。

检查数据库中所有字段的数据类型。将数字存储为字符串是不寻常的。

Private Sub ButtonAdd_Click(sender As Object, e As EventArgs) Handles ButtonAdd.Click
    Dim da As New DataAccess

    Dim fname As String = TextBoxfname.Text
    Dim lname As String = TextBoxlname.Text
    Dim nume As String = TextBoxNume3.Text
    Dim ename As String = TextBoxEntryname.Text

    Dim Chicken1 As String = TextBoxChicken1.Text
    Dim Chicken2 As String = TextBoxChicken2.Text
    Dim Chicken3 As String = TextBoxChicken3.Text


    Dim eno1 As String = TextBoxEntryNo1.Text
    Dim eno2 As String = TextBoxEntryNo2.Text
    Dim eno3 As String = TextBoxEntryNo3.Text

    Dim weightlb1 As String = ComboBoxWeightlb1.Text
    Dim weightlb2 As String = ComboBoxWeightlb2.Text
    Dim weightlb3 As String = ComboBoxWeightlb3.Text


    Dim weightoz1 As String = ComboBoxWeightOz1.Text
    Dim weightoz2 As String = ComboBoxWeightOz2.Text
    Dim weightoz3 As String = ComboBoxWeightOz3.Text

    If verif() AndAlso da.InsertRooster(fname, lname, nume, ename) AndAlso da.insertbreedenolboz1(Chicken1, eno1, weightlb1, weightoz1) AndAlso da.insertbreedenolboz1(Chicken2, eno2, weightlb2, weightoz2) AndAlso da.insertbreedenolboz1(Chicken3, eno3, weightlb3, weightoz3) Then
        MsgBox("success")
    Else
        MsgBox("error")
    End If

End Sub

Function verif() As Boolean
    If TextBoxfname.Text.Trim = "" OrElse TextBoxlname.Text.Trim = "" OrElse TextBoxEntryname.Text.Trim = "" Then
        Return False
    Else
        Return True
    End If
End Function



Public Class DataAccess

    Private conString As String = "Your connection string"

    Public Function insertbreedenolboz1(ByVal breed1 As String, ByVal eno1 As String, ByVal weightlb1 As String, ByVal weightoz1 As String) As Boolean
        Using cn As New MySqlConnection(conString)
            Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`,`chicken`,`lb`,`oz`,`financer_F_id`) VALUES (@eno,@ckn,@lb,@oz)", cn)
                Command.Parameters.Add("@ckn", MySqlDbType.String).Value = breed1
                Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno1
                Command.Parameters.Add("@lb", MySqlDbType.String).Value = weightlb1
                Command.Parameters.Add("@oz", MySqlDbType.String).Value = weightoz1
                cn.Open()
                If Command.ExecuteNonQuery() = 1 Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

    Public Function InsertRooster(ByVal fname As String, ByVal lname As String, ByVal nume As String, ByVal ename As String) As Boolean
        Using cn As New MySqlConnection(conString)
            Using command As New MySqlCommand("Insert into `derbabe`.`financer`( fname, lname,entry_name,num_entries) values (@fn,@ln,@ename,@nume);", cn)
                command.Parameters.Add("@fn", MySqlDbType.VarChar).Value = fname
                command.Parameters.Add("@ln", MySqlDbType.VarChar).Value = lname
                command.Parameters.Add("@ename", MySqlDbType.VarChar).Value = ename
                command.Parameters.Add("@nume", MySqlDbType.String).Value = nume
                cn.Open()
                If command.ExecuteNonQuery() = 1 Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

    Public Function inserteno1(ByVal eno1 As String) As Boolean
        Using cn As New MySqlConnection(conString)
            Using Command As New MySqlCommand("INSERT INTO `rooster` (`entry_no`) VALUES (@eno)", cn)
                Command.Parameters.Add("@eno", MySqlDbType.String).Value = eno1
                cn.Open()
                If Command.ExecuteNonQuery() = 1 Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

End Class