[RESOLVED] [02/03] Displaying data using Combobox/Textbox

[frankwhite]

Hi people, I was hoping someone could offer me some help please.

I have a database and vb.net as the front end. On my form I have a combobox and a textbox. What I want to do is show the name field in the combbobox and the address field in the textbox. So far I have the combobox working perfectly but I'm stuggling to get the textbox to connect with the combobox and the database.

My coding so far is:

Imports ss = System.Data.SqlClient

Dim cn As New System.Data.OleDb.OleDbConnection
    Dim cm As New System.Data.OleDb.OleDbCommand
    Dim rd As System.Data.OleDb.OleDbDataReader

'Following is under the Form Load Event
 
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.mdb"
        Try
            cn.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        cmbbyname.Items.Clear()
        txtbyname.Clear()
        cm.Connection = cn
        cm.CommandText = "select * from people"
        rd = cm.ExecuteReader()
        While (rd.Read)
            cmbbyname.Items.Add(rd("Name"))
           ' txtbyname.Text = cn.ExecuteScalar.ToString("Address"))??
        End While
        rd.Close()
        If cmbbyname.Items.Count > 0 Then
            cmbbyname.SelectedIndex = 0
        End If
        Try
            Dim adp As New Data.OleDb.OleDbDataAdapter
            Dim ds As New DataSet
            Dim dv As New DataView
            adp.SelectCommand = cm
            adp.Fill(ds, "dataset")
            dv.Table = ds.Tables(0)
            dv.Sort = "name"
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cn.Close()
        End Try

?? = I dont know what to put there.

Any help is really appeciated, please help me as I'm really struggling.

[riteshjain1982]

If i am not wrong you want to show the address of the Person selected in Combobox,If that is the case than you have to Hanlde Combobox's selectedIndexChange event and in that pass query something like

select Top 1 Address from People where Name = '" & cboPeople.text "'"

and than call command object's ExecuteScalar Method to get address.

PS:I just example,you need to change query as Name can be duplicated,so better fill ID or PK too in combobox....

[jmcilhinney]

Behold the power of data-binding:

Dim connection As New OleDbConnection("connection string here")
Dim adapter As New OleDbDataAdapter("SELECT ID, Name, Address FROM People ORDER BY Name", connection)
Dim table As New DataTable
 
adapter.Fill(table)
 
Me.ComboBox1.DisplayMember = "Name"
Me.ComboBox1.ValueMember = "ID"
Me.ComboBox1.DataSource = table
 
Me.TextBox1.DataBindings.Add("Text", table, "Address")

Do that and the rest will be automatic.

Notice also that I used the SQL query to sort the data rather than VB code. Only use the Sort property of the DataView if you need to be able to change the sort order after the original result set is returned.

[frankwhite]

Both of you thank you very much, jmcilhinney Just one last question on my combobox I would prefer to filter the data for example if someone is called John and I enter J then I want to show all the people with J and then if I press O then it will filter all the people with 'Jo' and so on (however new data can be entered in to the combobox).

Is this possible? If it's not then its ok, you've been a great help buddy and I really do owe u a million

[jmcilhinney]

What you're asking for is very simple in .NET 2.0 or later because the ComboBox includes autocomplete functionality. The really good thing is that the autocomplete list is separate to the drop-down list, so you get a filtered list of matching items while you type without having to filter the bound data.

In .NET 1.x you could handle the TextChanged event and set the bound DataView's RowFilter. That would mean that you'd have to clear the ComboBox to see the complete list again.

[frankwhite]

Thanks for your help, I think I'll forget adding that option as I don't understand.

Thanks again