The Shayaan The Shayaan - 10 days ago 7
Vb.net Question

Combobox ValueMember not working with MySQL

My

ComboBox
controls display publishername and authorlastname and stores the publisherid and authorid.

When I run my code, it does display publishername and authorlastname with the
ValueMember
of publisherid and authorid however when the insert query runs it literally tries to insert the words
_publisherid_
and
_authorid_
.

ComboBox Code:

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


Insert Code:

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

You should be using SelectedValue instead of 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"

should be

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"
Comments