mysql - 添加数据库时出现逻辑错误

Question

在我的程序中，用户能够将学生分组到一个类(class)中。在我的代码中，似乎存在一些奇怪的逻辑错误；我的数据库使用 StudentsGroups 表将一个组链接到学生。虽然输入应如下所示:

+---------------------+----------------------------------+
|      studentsgroups |                           groups |
+---------+-----------+---------+-----------+------------+
| groupID | studentID | groupID | groupName | groupFocus |
+---------+-----------+---------+-----------+------------+
|       1 |         1 |       1 | TestGroup |      Maths |
|       1 |         2 +---------+-----------+------------+
|       1 |         3 |
+---------+-----------+

我的输出看起来像这样:

+---------+-----------+
| groupID | studentID |
+---------+-----------+
|       1 |         0 |
|       2 |         0 |
|       3 |         0 |
+---------+-----------+

groups 表为空。我没有从中收到任何错误消息，据我所知，我的代码运行良好，但在此过程中的某个地方出现了问题。

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    ' We create a new instance of a Groups object to store our entered values; the group's name
    ' the group's focus and the students in this group
    Dim newGroup As New Groups
    newGroup.strGroupName = txtGroupName.Text
    newGroup.strGroupFocus = cmbGroupFocus.SelectedValue
    ' We add every student in this group list to the Groups object's student list.
    For i As Integer = 0 To lstGroupList.Items.Count - 1
        newGroup.addStudent(groupList.Item(i))
    Next
    ' Firstly, we must insert a new group into the database. This will allow us to link every
    ' student in the group to this group in our StudentsGroups table.
    Using conn As New MySqlConnection(connString)
        conn.Open()
        Using cmd As New MySqlCommand
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO groups(groupName, groupFocus) VALUES(@grpName, @grpFocus);"
            cmd.Parameters.AddWithValue("@grpName", newGroup.strGroupName)
            cmd.Parameters.AddWithValue("@grpFocus", newGroup.strGroupFocus)
        End Using
        conn.Close()
    End Using
    Try
        ' To get the ID of the group generated by the database, we make a temporary string to
        ' hold the ID value.
        Dim groupID As String
        Using conn As New MySqlConnection(connString)
            conn.Open()
            Using selComm As New MySqlCommand
                ' We create a SELECT query to find the new ID by using the entered group's name.
                selComm.Connection = conn
                selComm.CommandText = "SELECT groupID FROM groups WHERE groupName = @groupName;"
                selComm.Parameters.AddWithValue("@groupName", newGroup.strGroupName)
                selComm.Prepare()
                ' We use the ExecuteScalar method, which would return the very first entry on the
                ' first row. Since we only want one row and one result, this is better than using
                ' a DataTable and DataReader.
                groupID = selComm.ExecuteScalar()
            End Using
            conn.Close()
        End Using
        ' Now we begin to link the students to their group. We run through every student in the
        ' group list and add in their ID along with the group's ID to the table.
        For i As Integer = 0 To groupList.Count - 1
            Using conn As New MySqlConnection(connString)
                conn.Open()
                Using newCommand As New MySqlCommand
                    newCommand.Connection = conn
                    newCommand.CommandText = "INSERT INTO studentsgroups VALUES(@groupID, @studentID);"
                    newCommand.Parameters.AddWithValue("@groupID", CInt(groupID))
                    ' To get the student's ID, we do the same process as above; make a new
                    ' command to get the student's ID, using ExecuteScalar.
                    Dim studentID As String
                    ' When processing a new command, we need a new connection item to 
                    Using conn2 As New MySqlConnection(connString)
                        conn2.Open()
                        Using comm2 As New MySqlCommand
                            comm2.Connection = conn2
                            comm2.CommandText = "SELECT idNumber FROM students WHERE passCode = @passcode"
                            comm2.Parameters.AddWithValue("@passcode", groupList.Item(i).intIDNum)
                            comm2.Prepare()
                            studentID = comm2.ExecuteScalar()
                        End Using
                        conn2.Close()
                    End Using
                    newCommand.Parameters.AddWithValue("@studentID", CInt(studentID))
                    newCommand.Prepare()
                    newCommand.ExecuteNonQuery()
                End Using
                conn.Close()
            End Using
        Next
    Catch ex As Exception
        MsgBox("Error: " & ex.ToString())
    Finally
        MsgBox("New Group created!")
        Me.Close()
    End Try
End Sub

这是提交部分的相关代码，有人知道逻辑问题可能出现在哪里吗？

Answer 1

使用组表，您可以打开连接、格式化命令，然后关闭连接，而无需执行命令。添加行

cmd.ExecuteNonQuery()

在“使用 cmd...” block 的末尾。

我没有看到导致您的GroupID更改的逻辑错误，但我怀疑罪魁祸首是studentgroups表的groupID列设置为AutoIncrement(可能是因为大多数mySQL客户端默认第一列作为主键) 。构建表时请注意这一点，请记住，只有唯一值才应设置为主键，并且并非每个表都需要主键。

至于为什么你的studentID列的每一行都是0，我在遵循你的代码时遇到了一些麻烦。在行中:

comm2.CommandText = "SELECT idNumber FROM students WHERE passCode = @passcode"
comm2.Parameters.AddWithValue("@passcode", groupList.Item(i).intIDNum)

您似乎:1) 提供学生 ID 号 2) 查找学生 ID 号与 passCode 列匹配的行，以及 3) 从这些行中获取学生 ID 号。如果不了解更多有关您的数据库架构和 GUI 结构的信息，我无法判断这是否是问题或如何解决它，但让我问您:

您是否需要编写查询来获取群组中每个人的学生 ID 号？用户似乎已经选择了要添加的学生，并且此信息已存储在您的表单中。像这样的东西:

newCommand.Parameters.AddWithValue("@groupID", CInt(groupID))
'Delete everything between these two lines
newCommand.Parameters.AddWithValue("@studentID", groupList.Item(i).intIDNum)

如果我正确地推断了你的设置，那应该可以工作。如果没有，请绘制学生表格并准确解释您可以从表格访问其中的哪些列。