VB.NET MySQL query returns no value

Question

I am trying to display value in combobox using MySQL in vb.net. Right now the problem that I am facing is that combobox is not displaying values from MySQL. I have the below code:

MySqlConn = New MySqlConnection
    MySqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=s974_db"

    Try
        MySqlConn.Open()
        Label21.Text = "DB Connection Successful"

        Dim Query As String
        Query = "select * from s974_db.processors where Name='" & ComboBox1.Text & "'"

        COMMAND = New MySqlCommand(Query, MySqlConn)
        READER = COMMAND.ExecuteReader
        While READER.Read
            Label10.Text = READER.GetDouble("Price")

        End While


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


    End Try

However, using the above code Combobox1.Text returns nothing but if I use below code which has a different query it works:

        MySqlConn = New MySqlConnection
    MySqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=s974_db"

    Try
        MySqlConn.Open()
        Label21.Text = "DB Connection Successful"

        Dim Query As String
        Query = "select * from s974_db.processors"

        COMMAND = New MySqlCommand(Query, MySqlConn)
        READER = COMMAND.ExecuteReader
        While READER.Read
            Dim sName = READER.GetString("Name")
            ComboBox1.Items.Add(sName)
        End While


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


    End Try

Could someone please check and let me know what could be the issue? Thanks!

Answer 1

so the highlights as i mentioned them in the comments

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' You need only to open aconnection once
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=s974_db"

Try
  MySqlConn.Open()
  Label21.Text = "db connection successful"
  'First load both Combobox
  Dim query As String
  query = "select * from s974_db.processors"

  COMMAND = New MySqlCommand(query, MySqlConn)
  READER = COMMAND.ExecuteReader
  While READER.Read
    Dim sname = READER.GetString("name")
    ComboBox1.Items.Add(sname)
    ComboBox2.Items.Add(sname)
  End While



Catch ex As MySqlException
  MessageBox.Show(ex.Message)
Finally


End Try
End Sub
Private Sub Form1_Closing(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Closing
  Try
    MySqlConn.Close()
    MySqlConn.Dispose()
  Catch ex As Exception

  End Try
 End Sub

ANd now the Comboboxes

 Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
'Only when  there is a item selected , ask for data
If ComboBox1.SelectedIndex > -1 Then
  Try

    Dim Query As String
    Query = "select * from s974_db.processors where Name='" & ComboBox1.Text & "'"

    COMMAND = New MySqlCommand(Query, MySqlConn)
    READER = COMMAND.ExecuteReader
    While READER.Read
      Label11.Text = READER.GetDouble("Price")

    End While



  Catch ex As MySqlException
    MessageBox.Show(ex.Message)
  Finally


  End Try
End If
End Sub

Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
If ComboBox2.SelectedIndex > -1 Then
  Try

    Dim Query As String
    Query = "select * from s974_db.processors where Name='" & ComboBox1.Text & "'"

    COMMAND = New MySqlCommand(Query, MySqlConn)
    READER = COMMAND.ExecuteReader
    While READER.Read
      Label10.Text = READER.GetDouble("Price")

    End While



  Catch ex As MySqlException
    MessageBox.Show(ex.Message)
  Finally


  End Try
End If
End Sub

This is exactly as i described on Form_load you fill both comboboxes

When you now change one of the comboxes one of the label change too.

Sometimes you have to update the Element to see a change

in that case you write at the end of the loop

Label10.Update()

Answer 2

Starting at the top... Keep your database objects local to the method where they are used. (Not Form level variables) You can make the connection string a class level string variable. This is the only way you can ensure that they are closed and disposed.

Using...End Using blocks will close and dispose your database objects even if there is an error. The constructor of the connection takes the connection string. Connections are precious objects. Don't open the connection until directly before the .Execute method and close it as soon as possible.

It doesn't make much sense that the user could select an item from ComboBox1 before the Form.Load.

In general, we don't want to download anymore data than necessary and we want to hit the database as little as possible. In the Form.Load we bind the combobox to a data table that contains the name and price fields, setting the display and value members. Now, whenever the user picks a name in the combo we can retrieve the price without connecting to the database again.

I noticed that you were using Val in another event. This is an old VB6 method that can give you unexpected results. .Net and vb.net have all sorts of ways to get numbers out of strings that are faster and more reliable. CInt, .TryParse, .Parse, CType, Convert.To etc.

Public Class Form1

    Private ConString As String = "server=localhost;userid=root;password=root;database=s974_db"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Fill combobox
        Dim dt As New DataTable
        Using cn As New MySqlConnection(ConString),
                cmd As New MySqlCommand("select Name, Price from processors;", cn)
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using 'Closes and disposes both connection and command
        ComboBox1.DataSource = dt
        ComboBox1.DisplayMember = "Name"
        ComboBox1.ValueMember = "Price"
    End Sub

    Private Sub ComboBox1_SelectionChangeCommitted(sender As Object, e As EventArgs) Handles ComboBox1.SelectionChangeCommitted
        Label10.Text = ComboBox1.SelectedValue.ToString
        ClearLabels()
    End Sub

    Private Sub ClearLabels()
        Label11.Text = ""
        Label12.Text = ""
        Label13.Text = ""
        Label14.Text = ""
        Label15.Text = ""
        Label16.Text = ""
        Label17.Text = ""
        Label18.Text = ""
        Label19.Text = ""
        Label20.Text = ""
    End Sub

End Class