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!

Please learn how to use prepared statements in vb net, because every code is potentially vulnerable to sql injetion When your first code don't work, mysql tellys you that you have no data with that criteria. Test your query if they don't work in mysql workbench to see if they give any result also check the text of the combobox — nbk
– nbk, Commented Feb 24, 2020 at 18:26
First code sample seems to set a label text property only. Second code sample sets combobox items. — Andrew Mortimer
– Andrew Mortimer, Commented Feb 24, 2020 at 18:28
Query = "select * from s974_db.processors where Name='" & ComboBox1.Text & "'" - please show Debug.Print Query. — Akina
– Akina, Commented Feb 24, 2020 at 18:50
@nbk the combobox text is empty. However, I don't I can test that command in MySQL because it involves combobox which means it won't work. — Abdullah Amir
– Abdullah Amir, Commented Feb 24, 2020 at 18:51
@AndrewMortimer first code sample is supposed to at least populate the combobox at least that's the idea but it won't Second code sample works fine but I won't be able to achieve with it what I need. I am trying to fetch the name from the table and populate in combobox and then when a name is selected based on that it populates the label with the price. — Abdullah Amir
– Abdullah Amir, Commented Feb 24, 2020 at 18:53

Mary · Accepted Answer · 2020-08-28 20:52:09Z

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()

edited Aug 28, 2020 at 20:52

Mary

15.1k3 gold badges20 silver badges27 bronze badges

answered Feb 24, 2020 at 21:15

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mary Over a year ago

You leave a connection flapping in the breeze until the Form is closed!?!

Abdullah Amir Over a year ago

Hi @nbk you're awesome! Thank you so much for helping me out. Now I understood the concept a little bit better and did solve one more small issue where Reader had to be closed because we were going to use it again in ComboBox1_SelectedIndexChanged Sub Once again thank you so much. You're the best!

Mary Over a year ago

I added the language comment tag to the code sections to get rid of the red. It seems that if you add a comment in the code on a separate line, the editor considers the rest of the code to be a string. Argh!

Mary · Accepted Answer · 2020-08-28 20:51:13Z

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

Collectives™ on Stack Overflow

VB.NET MySQL query returns no value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related