How do I auto fill multiple Textboxes based on Windows Combo box selected value?

Question

I have a windows combo box that is dynamically populated from the database.

Then I have a couple of textboxes that I would like to fill automatically based on selected value from this combo box.

When I select a value from the combo box, my code keeps raising an alert that No records found and the textboxes are not getting populated with data.

Any ideas what I am doing wrong?

Imports System.Data.SqlClient

Public Class Form1
    Dim conString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True;;MultipleActiveResultSets=True"
    Dim con As New SqlConnection(conString)
    Dim cmd As New SqlCommand()
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT locId, name FROM Places"
        Dim adpt As New SqlDataAdapter(cmd)
        Dim dsn As New DataSet()
        adpt.Fill(dsn)
        con.Close()

        ComboBox1.DisplayMember = "name"
        ComboBox1.ValueMember = "LocID"
        ComboBox1.DataSource = dsn.Tables(0)
    End Sub


    Private Sub comboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Try
            con.Open()
            Dim req As String = "SELECT locId, name FROM Places where locID = @locId"
            Dim com As New SqlCommand(req, con)
            'MessageBox.Show(req)
            cmd.Parameters.AddWithValue("@locId", Convert.ToInt32(ComboBox1.SelectedValue))
            Dim dr As SqlDataReader = com.ExecuteReader()
            If dr.Read() Then
                txtLocationID.Text = dr.GetValue(0)
                txtLocation.Text = dr.GetValue(1).ToString()
            End If
        Catch ex As Exception
            MessageBox.Show("No records found")
        Finally
            con.Close()
        End Try
    End Sub
End Class

hdkhardik hdkhardik · Accepted Answer · 2017-02-02T03:47:57

I guess you need to loop through the datareader first , then you will be able to get data. Try this

Imports System.Data.SqlClient

Public Class Form1
    Dim conString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True;;MultipleActiveResultSets=True"
    Dim con As New SqlConnection(conString)
    Dim cmd As New SqlCommand()
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT locId, name FROM Places"
        Dim adpt As New SqlDataAdapter(cmd)
        Dim dsn As New DataSet()
        adpt.Fill(dsn)
        con.Close()

        ComboBox1.DisplayMember = "name"
        ComboBox1.ValueMember = "LocID"
        ComboBox1.DataSource = dsn.Tables(0)
    End Sub


    Private Sub comboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Try
            con.Open()
            Dim req As String = "SELECT locId, name FROM Places where locID = @locId"
            Dim com As New SqlCommand(req, con)

            com.Parameters.AddWithValue("@locId", Convert.ToInt32(ComboBox1.SelectedValue)) ' You supposed to add parameters to instance of `com` object '
            Dim dr As SqlDataReader = com.ExecuteReader()
            While dr.Read
                txtLocationID.Text = dr(0)
                txtLocation.Text = dr(1)
            End While
        Catch ex As Exception
            MessageBox.Show("No records found")
        Finally
            con.Close()
        End Try
    End Sub
End Class

How do I auto fill multiple Textboxes based on Windows Combo box selected value?

1 Answers