Listbox, Textbox, Database (M.Access)

[Dorayaki]

Hi, I want to ask about listbox things.
I made a Listbox, 3 textbox, and a Database
The Database Table is Member
The member field has 4 which is MemberID, Name, Address, Phone.

I have 2 records and it were display to the listbox which are Adry and Brandy (I get it from database name)

So, What I want is, when I click/select on Adry, the program will show Name, address , and phone to the 3 textboxes I made.
When I click/select on Brandy, the program will show the name, address, and phone to the 3 textboxes i made.

Anyway please take a look at my code :
This code is to get records from database and show it to the listbox

Sub loadlistbox()
        ListBox2.Items.Clear()
        Dim state = "SELECT * FROM Member ORDER BY Names"
        Dim Comm As OleDbCommand = New OleDbCommand(state conn)
        Try
            conn.Open()
            Dim reader As OleDbDataReader = sCommand.ExecuteReader()
            While reader.Read
                ListBox2.Items.Add(reader(1).ToString)
            End While
            sConnection.Close()
        Catch excep As System.Exception
            MsgBox(sException.Message, MsgBoxStyle.OkOnly Or MsgBoxStyle.Critical)
        End Try
        If conn.State <> ConnectionState.Closed Then conn.Close()
        If ListBox2.Items.Count > 0 Then ListBox2.Items(0).Selected = True
        ListBox2.Focus()
    End Sub

And Here is my Code when the listbox select changed

Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
        Dim state= "SELECT * FROM Member ORDER BY Names"
        Dim commAs OleDbCommand = New OleDbCommand(state, conn)
        Try
            conn.Open()
            Dim readerAs OleDbDataReader = sCommand.ExecuteReader()
            While reader.Read
               TextBox1.Text = reader(1)
               TextBox2.Text = reader(2)
               TextBox3.Text = reader(3)
            End While
        Catch ex As Exception
            MsgBox("Error Loading Data", , "My Address Book")
            conn.Close()
        End Try
        'btnDelete.Enabled = True
    End Sub

Something is wrong there, I don't now. It can show, but I can't click another record :(

I have done searching also from google but still I'm stuck. Please help.

[kvprajapati]

Use where clause,

Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
 ...
 Dim state= "SELECT * FROM Member Where Names='" & ListBox2.Text & "'"
 ...
End Sub

[Dorayaki]

Thanks, it works but when I clicked on the 2nd data, I got "error loading data" message. Hmm, I don't know where is the mistakes either.

also I got this error
"Public member 'Selected' on type 'String' not found"
it pointed to the listbox2.items(0).selected = true
from sub loadlistbox()

If ListBox2.Items.Count > 0 Then ListBox2.Items(0).Selected = True
ListBox2.Focus()

this thing is i want to make when it load, it also automatically select the first data in the listbox.

[kvprajapati]

Close the connection.

...
 Try
            conn.Open()
            Dim readerAs OleDbDataReader = sCommand.ExecuteReader()
            IF reader.Read Then
               TextBox1.Text = reader(1)
               TextBox2.Text = reader(2)
               TextBox3.Text = reader(3)
           End If
           reader.Close()
        Catch ex As Exception
            MsgBox("Error Loading Data" & ex.Message , , "My Address Book")
        Finally
            conn.Close()
        End Try
 ...

[Dorayaki]

I see, the "finally" code solved my problem...

Thanks!

anyway is there any solution for the listbox2.focus() ?

I tried many ways, but seems like it cannot auto selected the first data (if there is any)

[kvprajapati]

Set SelectedIndex property,

listBox2.SelectedIndex = 0;

[TomW]

A bit different but I wanted to offer a suggestion. You are first querying the DB and manually entering each name into your listbox and then re-querying the DB when a selection is made to get the specified record.

I'm thinking that it would be easier and more efficient if you just filled a dataset table to start, bind the name column to the listbox without having to itterate thru a loop of all records and when a selection is made, you only need to filter your existing datatable in memory rather then requerying the database for the record. Storing the results in a dataset would also be benificial for changes to the records (delete, update, insert etc)

However the size of your database would determine whether or not this would be optimal, I wouldnt suggest keeping that many records in memory for instance if there were tens of thousands of records to choose from. But at the same time I wouldnt recommend filling a listbox that large either... :)

[Dorayaki]

Thanks adatapost.. my problems are solved..

@TomW
Thanks for your suggestion, I appreciate it.
Unfortunately, I haven't learned until binding method.. Quite confuse too. If possible could you give me some example ? it would be great to learn. thanks

[TomW]

Dim ds As New DataSet

Using con As New SqlConnection(strDbConnectionHere)
    Dim cmd As New SqlCommand
    Dim da As SqlDataAdapter = Nothing

    cmd.Connection = con
    cmd.CommandText = "Select * From Member Order By Names"

    da = New SqlDataAdapter(cmd)
    da.Fill(ds, "Member")

    da.Dispose()
    cmd.Dispose()
End Using

ListBox2.DataSource = ds.Tables("Member") 'Binds the table to your listbox
ListBox2.DisplayMember = "Name" 'The col in the tbl you want to display

Just replace the sql objects with the equivelent OLEDB objects