Listbox selected item

rickb · Jun 30, 2003

I have a listbox which displays a list of names filtered by alphabet (The filtering is done via the text property of a textbox). Heres the problem: The filtered list is displayed in a listbox, and the relevant info for the selected name appears in a series of textboxes. When I select a different name, however, the data in the textboxes doesnt change. I wouldve thought that the solution might have something to do with an listbox.selecteditem event, but Im so lost Im not even sure about that. Heres the code for the sub that is triggered when, for example, the "A" button is clicked (thanks again to Jon(jfackler) for the code above the texbox binding):

Code:

Public Sub LoadList()
        Dim daUserList As New OleDbDataAdapter(_
        "SELECT * FROM tblAS400Users WHERE LastName LIKE " & _ 
         txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)

        Dim strName As String
        Dim oUserList As Object
        Dim oCurr As Object
        Dim dsUserList As New DataSet()

        Dim BldgNum As String
        Create the calculated column
        Try
            daUserList.Fill(dsUserList, "tblAS400Users")
            create a new column
            Dim dcName As DataColumn
            dcName = New DataColumn("Name")
            dcName.DataType = System.Type.GetType("System.String")
            dcName.Expression = "LastName + ,  + FirstName"

            Add the calculated column
            dsUserList.Tables("tblAS400Users").Columns.Add(dcName)

            Bind to the listbox
            With Me.lstUsers
                .DataSource = dsUserList.Tables("tblAS400Users")
                .DisplayMember = "Name"
            End With

        Catch ex As Exception
            MsgBox(ex.ToString)
            Exit Sub
        End Try

        Enable the Delete and Edit Button
        cmdDelete.Enabled = True
        cmdEdit.Enabled = True


        Me.txtBldgNum.DataBindings.Add("text", dsUserList, "tblAS400Users.LOC")
        Me.txtBldg.DataBindings.Add("text", dsUserList, "tblAS400Users.Building")
        Me.txtUserName.DataBindings.Add("text", dsUserList, "tblAS400Users.Name")
        Me.txtDeviceID.DataBindings.Add("text", dsUserList, "tblAS400Users.DeviceID")
        Me.txtUserProfile.DataBindings.Add("text", dsUserList, "tblAS400Users.UserProfile")
        Me.txtLastName.DataBindings.Add("text", dsUserList, "tblAS400Users.LastName")
        Me.txtFirstName.DataBindings.Add("text", dsUserList, "tblAS400Users.FirstName")

        For Each oCurr In Me.Controls
            If TypeOf oCurr Is TextBox Then
                oCurr.databindings.clear()
            End If
        Next

        If lstUsers.Items.Count = 0 Then
            For Each oCurr In Me.Controls
                If TypeOf oCurr Is TextBox Then
                    oCurr.text = ""
                End If
            Next
        End If

        BldgNum = Me.txtBldgNum.Text

        Select Case BldgNum
            Case 59 To 81
                Me.txtLAN.Visible = True
                Me.lblLAN.Visible = True
                Me.txtLAN.DataBindings.Add("text", dsUserList, "tblAS400Users.LANAddress")
            Case Else
                Me.txtLAN.Visible = False
                Me.lblLAN.Visible = False
                Exit Sub
        End Select
    End Sub

Can anyone point me in the right direction for a solution? Thanks in advance.

Rick

Nerseus · Jul 2, 2003

I think Im missing something...

It looks like youre doing the following (pseudo-code)

1. Fill Dataset dsUserList
2. Bind lstUsers to table
3. Add bindings to numerous textboxes
4. Loop through all controls and clear bindings
5. If no records in step 2, clear all textbox Text properties
6. Do something weird with a Building Number

Heres some comments:
1 and 2 look ok. Im not sure why you bind textboxes, then clear all databindings. You should be calling clear before rebinding only, as in:

Code:

Me.txtBldgNum.DataBindings.Clear()
Me.txtBldgNum.DataBindings.Add("text", dsUserList, "tblAS400Users.LOC")

Me.txtBldg.DataBindings.Clear()
Me.txtBldg.DataBindings.Add("text", dsUserList, "tblAS400Users.Building")

Step 5 shouldnt be necessary. If the bindings are in place and no records are returned, the textboxes should automatically be cleared (I think). If it were me, Id also want to check the rowcount of the DataTable instead of the ListBox. While there is no real difference, the code would look "cleaner" to use the original data table for record counts instead of the control.

Step 6 shouldnt be working at all right now since your textboxes all got cleared out in step 4.

Normally, when using DataBinding, you only need to bind one time. After that, refreshing your DataTable using the DataAdapter should suffice.

Is it possible to bring back ALL data in one query and never go back to the database, or are there too many records? You can do client side filtering using "LIKE..." filtering and bind to the filtered view (look at DataView object). You can also apply sorting (though its always better to let the DB do it if possible). If the entire chunk of data isnt too big, this may be easier as the response time in filtering would be cut since its all client side.

-Nerseus

rickb · Jul 2, 2003

Hopefully I can clear some of this up (and maybe learn something myself):

3 & 4. Binding, then unbinding, textboxes. I bound the textboxes to specifice columns in the datatbase to display relevant info based on the selected name (Is there a better way of doing this?).
I have buttons for each letter of the alphabet. Before clearing the bindings, I was getting an error when, for example, I clicked the "A" button, then clicked the "B" button (The error mentioned something about the textboxes already being bound). Each button triggers the LoadList() sub so that a new list of names appear.

5. If no records in step 2, clear all textbox Text properties.
There are no users whos last name begin with "X" or "Q"; when these buttons were clicked, I was receiving an error because the list was blank. This was my attempt to replace the null with a blank space (Im not sure if its the right way, but it worked!)

6.Do something weird with a Building Number. Not all of the users have a LAN Address; only those who are in Buildings numbered 59 to 81 have one. If the person is in one of these buildings, the LAN Address textbox and label are made visible; otherwise, they are not visible (I hope I answered the right question on this one).

I hope this cleared some, if not all, of this up. This all works perfectly in another app using the Data Adapter Wizard (obviously with a lot less code), but I am trying to duplicate the application through code only. Thanks for the reply, Nerseus; Im all ears if there is a better way to do this.

Rick

Heiko · Jul 2, 2003

Not that I know much about the topic,
but I only see a line of code where you bind a whole table to the controls (makes sense for a listbox) but I dont see where you position to a specific row in the table.

My wild guess is that the textboxes always show the fist row of the table. Where/how do you handle the selected item changed event of the listbox ?

rickb · Jul 2, 2003

Heiko, youre right; only the first row is displayed no matter which name is selected. I guess the gist of my problem is I dont know how to make another row display.

As an aside, any recommendations for a book (or books) that deals with issues similar to mine(not necessarily the problem, but the technique)?

jfackler · Jul 2, 2003

Code:

Public Sub LoadList()
        Dim daUserList As New OleDbDataAdapter(_
        "SELECT * FROM tblAS400Users WHERE LastName LIKE " & _ 
         txtPick.Text & "% ORDER BY LastName,FirstName", oledbconn)

        Dim strName As String
        Dim oUserList As Object
        Dim oCurr As Object
        Dim dsUserList As New DataSet()

        Dim BldgNum As String
        Create the calculated column
        Try
            daUserList.Fill(dsUserList, "tblAS400Users")
            create a new column
            Dim dcName As DataColumn
            dcName = New DataColumn("Name")
            dcName.DataType = System.Type.GetType("System.String")
            dcName.Expression = "LastName + ,  + FirstName"

            Add the calculated column
            dsUserList.Tables("tblAS400Users").Columns.Add(dcName)

            Bind to the listbox
            With Me.lstUsers
                .DataSource = dsUserList.Tables("tblAS400Users")
                .DisplayMember = "Name"
            End With

        Catch ex As Exception
            MsgBox(ex.ToString)
            Exit Sub
        End Try

        Enable the Delete and Edit Button
        cmdDelete.Enabled = True
        cmdEdit.Enabled = True


        Me.txtBldgNum.DataBindings.Add("text", dsUserList, "tblAS400Users.LOC")
        Me.txtBldg.DataBindings.Add("text", dsUserList, "tblAS400Users.Building")
        Me.txtUserName.DataBindings.Add("text", dsUserList, "tblAS400Users.Name")
        Me.txtDeviceID.DataBindings.Add("text", dsUserList, "tblAS400Users.DeviceID")
        Me.txtUserProfile.DataBindings.Add("text", dsUserList, "tblAS400Users.UserProfile")
        Me.txtLastName.DataBindings.Add("text", dsUserList, "tblAS400Users.LastName")
        Me.txtFirstName.DataBindings.Add("text", dsUserList, "tblAS400Users.FirstName")
 End Sub

Rick,
Youre good to here. Then things take on a strange twist.
Since all of your textboxes are bound to a single dataset, you should be able to clear it up easily enough however.

Double click your listbox in the Form1.vb (Design) view.
Your IDE will give you the following:

Code:

Private Sub lstUsers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstUsers.SelectedIndexChanged
 
add the following:
Me.BindingContext(dsUserList).Postion = lstUsers.SelectedIndex

and put the rest below:
  BldgNum = Me.txtBldgNum.Text

        Select Case BldgNum
            Case 59 To 81
                Me.txtLAN.Visible = True
                Me.lblLAN.Visible = True
                Me.txtLAN.DataBindings.Add("text", dsUserList, "tblAS400Users.LANAddress")
            Case Else
                Me.txtLAN.Visible = False
                Me.lblLAN.Visible = False
                Exit Sub
        End Select

End Sub

That will change the bindingcontext.postion to the appropriate location based on the item selected in the list box and fill your other criteria appropriately.

You mentioned that the "A" button gets clicked and the LoadList() sub gets called. You might clear the databindings in the button click event before the LoadList() sub gets called that would solve the binding issue.

Code:

    Private Sub BtnA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnA.Click

For Each oCurr In Me.Controls
            If TypeOf oCurr Is TextBox Then
                oCurr.databindings.clear()
            End If
        Next

LoadList()

    End Sub

Should get you a little further along your way.

Jon

rickb · Jul 2, 2003

Thanks for the response, Jon.

I did as you suggested, but not only is the user data not changing among the users on the first button click, Im now confronted with a different problem: where to put the dsUserList declaration. If I put it as a form level or in a module,when a second Alphabet button is clicked, I get a "A column named Name already belongs to this datatable" error message. If I put the declaration in the LoadList sub, I get a "Name dsUserList is not declared" error message in the lstUsers_SelectedIndexChanged sub.

Sorry if these problems are basic coding problems, but as Ive said before, Im still relatively new to VB.Net. Thanks for any help and/or suggestions.

Rick

jfackler · Jul 2, 2003

Can you show me the code youve written so far?

Jon

jfackler · Jul 2, 2003

Leave the dataset declaration where it is.

Put a dsUserList.Clear in the BtnA_Click Sub as its first line.

Jon

rickb · Jul 2, 2003

In addition to the code displayed in my first post, heres what I have for each alphabet button and the lstUserList_SelectedIndexChanged:

[VB]
Private Sub cmdA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdA.Click
Me.dsUserList.Clear() I just entered this per your last suggestion
Me.txtPick.Text = cmdA.Text
For Each oCurr In Me.Controls per your suggestion before the last one
If TypeOf oCurr Is TextBox Then
oCurr.databindings.clear()
End If
Next
LoadList()
End Sub
[/VB]

[VB]
Private Sub lstUsers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstUsers.SelectedIndexChanged
add the following:
Me.BindingContext(dsUserList).Position = lstUsers.SelectedIndex

and put the rest below:
BldgNum = Me.txtBldgNum.Text

Select Case BldgNum
Case 59 To 81
Me.txtLAN.Visible = True
Me.lblLAN.Visible = True
Me.txtLAN.DataBindings.Add("text", dsUserList, "tblAS400Users.LANAddress")
Case Else
Me.txtLAN.Visible = False
Me.lblLAN.Visible = False
Exit Sub
End Select

End Sub
[/VB]

If I leave the dsUserList declaration where it is -- in the LoadList sub-- I get the "dsUserList not declared" error in both the subs above. Ive also commented out the databindings.clear loop in the LoadList sub since it is running in the alphabet buttons.

Rick

Listbox selected item

rickb

Member

Nerseus

Danner

rickb

Member

Heiko

Well-known member

rickb

Member

jfackler

Well-known member

rickb

Member

jfackler

Well-known member

jfackler

Well-known member

rickb

Member