mysql - 如何使用vb将Combobox和checklistbox保存到mysql php中

Question

我正在使用 vb 2017。我尝试将组合框值和选中列表框项目保存到数据库中。数据库表仅显示组合框的“System.Data.DataRowView”和选中列表框的“System.Windows.Forms.CheckedListBox+ObjectCollection”。谁能帮我？我使用 mysql phpmyadmin 作为数据库。这是我在下面使用的代码。它没有显示错误。但所选项目的值尚未显示在数据库表中。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Dim constr As String = "server=localhost;user=root;database=login;port=3306;password=root123;SslMode=none"
Dim conn As MySqlConnection = New MySqlConnection(constr)
Dim result As Integer
'If True Then
Try
    conn.Open()
    With {}
        Dim cmd As MySqlCommand
        For Each item In CheckedListBox1.CheckedItems
            cmd = New MySqlCommand("INSERT INTO mastersubject(name,subjectpriority) VALUES(@name,@subjectpriority)", conn)
        Next
        cmd.Parameters.AddWithValue("@name", ComboBox1.SelectedItem.ToString)
        cmd.Parameters.AddWithValue("@subjectpriority", CheckedListBox1.Items.ToString())
        result = cmd.ExecuteNonQuery()
        'conn.Close()
    End With
    'End If

    If result > 0 Then

        MsgBox("Record has been saved")

    Else
        MsgBox("Error!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    End If

Catch ex As Exception
    Console.WriteLine(ex.ToString())
    MsgBox(ex.Message)

Finally
    conn.Close()
End Try

End Sub

Answer 1

评论和解释一致。

Private Sub InsertRecord()
        Dim constr As String = "server=localhost;user=root;database=login;port=3306;password=root123;SslMode=none"
        'A Using...End Using block will ensure that your data objects
        'are closed and disposed event if there is an error
        Try
            Using conn As MySqlConnection = New MySqlConnection(constr)
                'You need the new keyword to create the command
                'Pass the sql query string and the connection object to the
                'constructor of the command
                'Create the command once, only the value of the subjectpriority changes
                Using cmd As New MySqlCommand("INSERT INTO mastersubject (name, subjectpriority) VALUES (@name, @subjectpriority);", conn)
                    cmd.Parameters.AddWithValue("@name", ComboBox1.SelectedItem.ToString)
                    cmd.Parameters.Add("@subjectpriority")
                    'Open the connection as late as possible
                    conn.Open()
                    For i = 0 To CheckedListBox1.CheckedItems.Count - 1
                        Dim Result As Integer
                        'You are not adding a new parameter, just changing its value
                        cmd.Parameters("@subjecpriority").Value = CheckedListBox1.CheckedItems(i).ToString()
                        'the command will be executed for each item checked
                        Result = cmd.ExecuteNonQuery()
                        If Result > 0 Then
                            MessageBox.Show("Record has been saved")
                        Else
                            MessageBox.Show("Error!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                        End If
                    Next
                End Using 'Disposes the command
            End Using ' closes and disposes the connection
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
End Sub