How to Link ComboBox with Database and Show values in TextBox if Select ComboBox in VB.NET-CodePudding

I am trying to select a database value from Combobox that has been populated from the database and displayed in Combobox, and I want to click on any option in Combobox and it should display values in the textbox but upon doing so, I get an error that says: data is null. This method or property cannot be called on NULL values.

The error: https://snipboard.io/Rn6YwC.jpg CONTINUED SCREENSHOT: https://snipboard.io/i3l8MO.jpg

This is my code:

 Private Sub CollectionEntry_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=golden_star"



    Dim READER As MySqlDataReader
    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "select * from golden_star.loan"

        Command = New MySqlCommand(Query, MysqlConn)



        READER = Command.ExecuteReader

        While READER.Read()
            Dim sname = READER.GetString("account_name")
            ComboBox1.Items.Add(sname)

        End While

        MysqlConn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=root;database=golden_star"


    Dim READER As MySqlDataReader
    Dim table As New DataTable
    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "select * from golden_star.loan where account_name = '" & ComboBox1.Text & "' "

        Command = New MySqlCommand(Query, MysqlConn)

        READER = Command.ExecuteReader

        While READER.Read
            txtID.Text = READER.GetInt32("id")
            DatePick.Text = READER.GetDateTime("date")
            txtName.Text = READER.GetString("account_name")
            txtCollections.Text = READER.GetInt32("collections")
            txtContact.Text = READER.GetInt32("contact")
        End While

        MysqlConn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

The first code is loading data from the database and putting it on Combobox when form onl oad and the second form is Combobox selected index changed. Selecting one of the lists and displaying it on the textbox.

I can't seem to find the error that's causing the error to pop up. Where did I go wrong that it pops when selecting the list values in Combobox?

CodePudding user response：

You don't need any code at all when the user makes a selection. You're getting all the data on the first query so just use that data. Populate a DataTable and bind it to all the controls, preferably via a BindingSource. The other controls will then update automatically when a selection is made.

Using connection As New MySqlConnection("connection string here"),
      command As New SqlCommand("SELECT * FROM golden_star.loan", connection)
    connection.Open()

    Using reader = command.ExecuteReader()
        Dim table As New DataTable

        table.Load(reader)
        BindingSource1.DataSource = table
    End Using
End Using

With ComboBox1
    .DisplayMember = "account_name"
    .ValueMember = "id"
    .DataSource = BindingSource1
End With

txtID.DataBindings.Add(NameOf(txtID.Text), BindingSource1, "id")
DatePick.DataBindings.Add(NameOf(DatePick.Value), BindingSource1, "date")
'etc

Note that, for the DateTimePicker, you should be using the Value property, which is type DateTime, not the Text property.

Note that the BindingSource gets added to the form in the designer, along with the controls.