mysql - Combobox ValueMember 不适用于 MySQL

Question

我的 ComboBox 控件显示 publishername 和 authorlastname 并存储 publisherid 和 authorid。

当我运行我的代码时，它会显示 publishername 和 authorlastname 以及 publisherid 和 ValueMember em>authorid 然而，当插入查询运行时，它会尝试插入单词 _publisherid_ 和 _authorid_。

组合框代码:

Private Sub addbook_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    mysqlconn = New MySqlConnection
    mysqlconn.ConnectionString = "server=localhost;userid=root;database=librarydatabase;Convert Zero Datetime=True"
    Dim table As New DataTable

    Dim da As New MySqlDataAdapter("select * from publishertable", mysqlconn)

    da.Fill(table)
    ComboBox1.DataSource = New BindingSource(table, Nothing)
    ComboBox1.DisplayMember = "publishername"
    ComboBox1.ValueMember = "PublisherId"

    Dim pa As New MySqlDataAdapter("select * from authortable", mysqlconn)

    pa.Fill(table)
    ComboBox2.DataSource = New BindingSource(table, Nothing)
    ComboBox2.DisplayMember = "authorlastname"
    ComboBox2.ValueMember = "authorid"

End Sub

插入代码:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button1.Click
    conn = New MySqlConnection
    conn.ConnectionString =
        "server=localhost;userid=root;database=librarydatabase"
    Dim reader As MySqlDataReader

    Try
        conn.Open()
        query = "SET foreign_key_checks = 0;insert into booktable(ISBNno,bookname,dateofpublication,genre,duodecimal,copies,copiesinstock,authorid,publisherid) Values('" & ISBNno.Text & "','" & Title.Text & "','" & dateofpublication.Text & "','" & genre.Text & "','" & duodecimal.Text & "','" & copies.Text & "','" & copies.Text & "','" & ComboBox2.ValueMember & "', '" & ComboBox1.ValueMember & "');SET foreign_key_checks = 1"

        command = New MySqlCommand(query, conn)
        reader = command.ExecuteReader

        MessageBox.Show(query)

        conn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        conn.Dispose()


    End Try

End Sub

Answer 1

您应该使用 SelectedValue 而不是 ValueMember

query = "SET foreign_key_checks = 0;insert into booktable(ISBNno,bookname,dateofpublication,genre,duodecimal,copies,copiesinstock,authorid,publisherid) Values('" & ISBNno.Text & "','" & Title.Text & "','" & dateofpublication.Text & "','" & genre.Text & "','" & duodecimal.Text & "','" & copies.Text & "','" & copies.Text & "','" & ComboBox2.ValueMember & "', '" & ComboBox1.ValueMember & "');SET foreign_key_checks = 1"

应该是

query = "SET foreign_key_checks = 0;insert into booktable(ISBNno,bookname,dateofpublication,genre,duodecimal,copies,copiesinstock,authorid,publisherid) Values('" & ISBNno.Text & "','" & Title.Text & "','" & dateofpublication.Text & "','" & genre.Text & "','" & duodecimal.Text & "','" & copies.Text & "','" & copies.Text & "','" & ComboBox2.SelectedValue & "', '" & ComboBox1.SelectedValue & "');SET foreign_key_checks = 1"