Is this the way to populate list boxes from a db in vb.net? 1

[BobRodes]

This code works fine. However, I'm wondering if this is the standard technique for creating list boxes and populating them with data from tables in a DB. In particular, the overriding of ToString was not an easy wrinkle to find; if I didn't do that, every list item shows the class type name instead of the data I wanted. I assume that the default implementation of ToString comes from System.Object, and returns the string value of the Class name. (I got this technique from one of Balena's books, still not sure where the class calls ToString exactly, or what it inherits from.)

    Private Class lstItemRow
        Public TextData As String
        Public ItemData As String

        Sub New(ByVal td As String, ByVal id As String)
            Me.TextData = td
            Me.ItemData = id
        End Sub

        Overrides Function ToString() As String
            Return TextData
        End Function
    End Class


    Public Sub doFormLoad()
        Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=myDB")
        Dim drLocal As SqlDataReader 
        cn.Open() 'declared elsewhere
        cmd = New SqlCommand("sp_getmylistdata", cn)
        cmd.CommandType = CommandType.StoredProcedure
        drLocal = cmd.ExecuteReader()
        With cForm.lstMyList.Items
            .Clear()
            Do While drLocal.Read
                .Add(New lstItemRow(CStr(drLocal.Item("record_desc")), CStr(drLocal.Item("record_id"))))
            Loop
        End With
        drLocal.Close()
     End Sub

Thanks,

Bob

 

[chrissie1]

I think the add method of the listbox will accept any class with two members (one valuemember, one display). So it doesn't need to inherit from anything. Instead o fadding directly to the listbox.items collection you could also make your own collection of lstitemrow elements and databind that via datasource. Because the itemscollection will accept any icollectionbase implemented class (like arralist,collection,datatable,...).

the tostring gets overriden of the baseclass. in the overrides function tostring. When you declare something as class you are infact "inheriting" some baseclass with a certain structure, among other things it has a ToString function wich you can override.

I hope this clears a few things up. If anybody doesn't agree please feel free to correct me.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[BobRodes]

Yeah, I guess I'm wondering which base class's tostring is being overridden, and thought it was probably System.Object. I know s.o has a ToString implementation, and suspect it returns a string cast of the class's name.

Bob

 

[BobRodes]

I've been looking over some of my old threads. Since writing this, I've found that this is in fact the case. System.Object provides a ToString implementation that returns the Class name as a string. This is often overridden, since many classes will have other uses for a ToString method. This is a case in point: lstItemRow will return whatever value is passed to the first argument of its constructor whenever ToString is called, and the ListBox uses ToString to show its List items.

Bob

 

[fawkes]

Here's what I use to fill datagrids, listboxes, etc.

It's a sortable collection, simply change MyNamespace.MyClass to the namespace and class name of the objects you want to hold in the collection.

To use the collection you set the data source of the ListBox or DataGrid to the collection and the datamember to the property of the object you want see.

'Author:    Fawxes
'Date:      22nd November 2005

'Foreword:  This strongly-typed collection of MyNamespace.MyClass objects enables sorting and searching.

'           The collection inherits from CollectionBase and implements IBindingList
'           to enable the collections to be sorted according to properties and direction.

'           Some methods are not supported and if they are required they be edited with 
'           code that provides support and moved to the correct region.

Public Class SortedCollection
    Inherits CollectionBase
    Implements System.ComponentModel.IBindingList
    Implements System.Collections.IComparer

#Region " Constructors "

    Public Sub New()

        'Call the base class new method
        MyBase.New()

        'Initialise the sort direction
        Me.mSortDirection = New System.ComponentModel.ListSortDirection
        Me.mSortDirection = System.ComponentModel.ListSortDirection.Ascending

        'Create a property descriptor collection for a new object
        Dim propCollection As System.ComponentModel.PropertyDescriptorCollection
        propCollection = System.ComponentModel.TypeDescriptor.GetProperties(New Object)

        ''Initialise the sort property
        Dim propDesc As System.ComponentModel.PropertyDescriptor
        propDesc = propCollection.Find("ToString", True)

        'Initialise the IsSorted flag
        mIsSorted = False

        'Initialise the auto sort flag
        Me.mAutoSort = False

    End Sub 'New

#End Region 'Constructors 

#Region " Binding List Supported Members "

    Public Function AddNew() As Object Implements _
                                System.ComponentModel.IBindingList.AddNew

        'Call the base class method
        Me.Add(New MyNamespace.MyClass)

    End Function

    Public ReadOnly Property AllowEdit() As Boolean _
                    Implements System.ComponentModel.IBindingList.AllowEdit
        Get
            Return True
        End Get
    End Property

    Public ReadOnly Property AllowNew() As Boolean _
                        Implements System.ComponentModel.IBindingList.AllowNew
        Get
            Return True
        End Get
    End Property

    Public ReadOnly Property AllowRemove() As Boolean _
                        Implements System.ComponentModel.IBindingList.AllowRemove
        Get
            Return True
        End Get
    End Property

    Private mIsSorted As Boolean
    Public ReadOnly Property IsSorted() As Boolean _
                        Implements System.ComponentModel.IBindingList.IsSorted
        Get
            Return Me.mIsSorted
        End Get
    End Property    'IsSorted

    Public ReadOnly Property SupportsSearching() As Boolean _
                        Implements System.ComponentModel.IBindingList.SupportsSearching
        Get
            Return True
        End Get
    End Property    'SupportsSearching

    Public ReadOnly Property SupportsSorting() As Boolean _
                        Implements System.ComponentModel.IBindingList.SupportsSorting
        Get
            Return True
        End Get
    End Property    'SupportsSorting

    Public ReadOnly Property SupportsChangeNotification() As Boolean _
                Implements System.ComponentModel.IBindingList.SupportsChangeNotification
        Get
            Return True
        End Get
    End Property    'SupportsChangeNotification

    Public Sub ApplySort(ByVal [property] As System.ComponentModel.PropertyDescriptor, _
                     ByVal direction As System.ComponentModel.ListSortDirection) _
                     Implements System.ComponentModel.IBindingList.ApplySort

        'If sorting is supported then sort the objects, otherwise throw an exception
        If Me.SupportsSorting Then
            'Set the property and direction
            Me.mSortProperty = [property]
            Me.mSortDirection = direction
            'Sort the inner list
            Me.InnerList.Sort(Me)
            'Set the is sorted flag
            Me.mIsSorted = True
            'Raise the list changed event
            RaiseEvent ListChanged(Me, _
                        New System.ComponentModel.ListChangedEventArgs( _
                                System.ComponentModel.ListChangedType.Reset, _
                                Me.SortProperty))
        Else
            Throw New System.NotSupportedException
        End If

    End Sub 'ApplySort

    'Provide a sort direction object
    Dim mSortDirection As System.ComponentModel.ListSortDirection
    Public ReadOnly Property SortDirection() As System.ComponentModel.ListSortDirection _
        Implements System.ComponentModel.IBindingList.SortDirection
        Get
            Return Me.mSortDirection
        End Get
    End Property    'SortDirection

    'Provide a sort property object
    Dim mSortProperty As System.ComponentModel.PropertyDescriptor
    Public ReadOnly Property SortProperty() As System.ComponentModel.PropertyDescriptor _
        Implements System.ComponentModel.IBindingList.SortProperty
        Get
            Return Me.mSortProperty
        End Get
    End Property    'SortProperty

    Public Function Find(ByVal [property] As System.ComponentModel.PropertyDescriptor, _
                         ByVal key As Object) As Integer _
                         Implements System.ComponentModel.IBindingList.Find
        'Provide an algorithm to find the position of an object based uopn a property 
        'value only if the colletion is sorted and the property passed is the sorted 
        'property
        If Not (Me.IsSorted And Me.SortProperty Is [property]) Then
            Me.mSortProperty = [property]
            Me.ApplySort(Me.SortProperty, Me.SortDirection)
        End If

        'Declare left, right and mid pointers
        Dim left As Integer
        Dim right As Integer
        Dim mid As Integer

        'Set the intial values of left and right
        left = 0
        right = Me.Count - 1

        'Check the left and right extremes
        If Me.CompareProperty(Me.Item(left), key) > 0 Then _
                                                Throw New System.ArgumentException
        If Me.CompareProperty(Me.Item(left), key) = 0 Then Return left

        If Me.CompareProperty(Me.Item(right), key) < 0 Then _
                                                Throw New System.ArgumentException
        If Me.CompareProperty(Me.Item(right), key) = 0 Then Return right

        Do While left < right

            'Calculate the mid point
            mid = System.Convert.ToInt16(System.Convert.ToDouble(left + right) / 2)

            'Check the mid point
            Select Case Me.CompareProperty(Me.Item(mid), key)
                Case Is > 0
                    right = mid
                Case Is = 0
                    Return mid
                Case Is < 0
                    left = mid
            End Select

            'If there are only two options left check them to avoid a repeating loop
            If right - left = 1 Then
                If Me.CompareProperty(Me.Item(left), key) = 0 Then Return left
                If Me.CompareProperty(Me.Item(right), key) = 0 Then Return left
                Throw New System.ArgumentException
            End If
        Loop

    End Function    'Find

    Public Event ListChanged(ByVal sender As Object, _
                             ByVal e As System.ComponentModel.ListChangedEventArgs) _
                             Implements System.ComponentModel.IBindingList.ListChanged

#End Region 'Binding List Supported Members 

#Region " Binding List Not Supported Members "

    Public Sub AddIndex(ByVal [property] As System.ComponentModel.PropertyDescriptor) _
                            Implements System.ComponentModel.IBindingList.AddIndex
        Throw New System.NotSupportedException
    End Sub 'AddIndex

    Public Sub RemoveIndex(ByVal [property] As System.ComponentModel.PropertyDescriptor) _
        Implements System.ComponentModel.IBindingList.RemoveIndex
        Throw New System.NotSupportedException
    End Sub 'RemoveIndex

    Public Sub RemoveSort() Implements System.ComponentModel.IBindingList.RemoveSort
        Throw New System.NotSupportedException
    End Sub 'RemoveSort


#End Region 'Binding List Not Supported Members 

#Region " Collection Base Supported Members "

    'Implement a default item object
    Default Public Overridable Property Item(ByVal index As Integer) _
                            As MyNamespace.MyClass
        Get
            Return CType(Me.List.Item(index), MyNamespace.MyClass)
        End Get
        Set(ByVal Value As MyNamespace.MyClass)
            'Set the value
            Me.List.Item(index) = Value
            'If auto sort is set then apply the sort
            If Me.AutoSort Then Me.ApplySort(Me.SortProperty, Me.SortDirection)
        End Set
    End Property    'Item

    'Implement Add, Insert and Remove methods
    Public Overridable Function Add( _
                ByVal value As MyNamespace.MyClass) As Integer
        'Add the item
        Return Me.List.Add(value)
        'If auto sort is set then apply the sort
        If Me.AutoSort Then Me.ApplySort(Me.SortProperty, Me.SortDirection)
    End Function    'Add

    Public Overridable Sub Insert(ByVal index As Integer, _
                                    ByVal value As MyNamespace.MyClass)
        'Insert the item
        Me.Insert(index, value)
        'If auto sort is set then apply the sort
        If Me.AutoSort Then Me.ApplySort(Me.SortProperty, Me.SortDirection)
    End Sub 'Insert

    Public Overridable Sub Remove(ByVal value As MyNamespace.MyClass)
        'Remove the item
        Me.Remove(value)
    End Sub 'Remove

    'Implement IndexOf and Contains methods
    Public Overridable Function IndexOf( _
                        ByVal value As MyNamespace.MyClass) As Integer
        Return Me.IndexOf(value)
    End Function    'IndexOf

    Public Overridable Function Contains( _
                        ByVal value As MyNamespace.MyClass) As Boolean
        Return Me.Contains(value)
    End Function    'Contains

    'Override some of the change validation methods
    Protected Overrides Sub OnInsert(ByVal index As Integer, ByVal value As Object)
        If Not value.GetType Is Type.GetType("MyNamespace.MyClass ") Then
            Throw New System.ArgumentException( _
                        "Value must be of type MyNamespace.MyClass ", "Value")
        End If
    End Sub 'OnInsert

    Protected Overrides Sub OnRemove(ByVal index As Integer, ByVal value As Object)
        If Not value.GetType Is Type.GetType("MyNamespace.MyClass ") Then
            Throw New System.ArgumentException( _
                        "Value must be of type MyNamespace.MyClass ", "Value")
        End If
    End Sub 'OnRemove

    Protected Overrides Sub OnSet(ByVal index As Integer, _
                                    ByVal oldValue As Object, _
                                    ByVal newValue As Object)
        If Not newValue.GetType Is Type.GetType("MyNamespace.MyClass ") Then
            Throw New System.ArgumentException( _
                        "Value must be of type MyNamespace.MyClass ", _
                        "NewValue")
        End If
    End Sub 'OnSet

    Protected Overrides Sub OnValidate(ByVal value As Object)
        If Not value.GetType Is Type.GetType("MyNamespace.MyClass ") Then
            Throw New System.ArgumentException( _
                        "Value must be of type MyNamespace.MyClass ")
        End If
    End Sub 'OnValidate

#End Region 'Collection Base Supported Members 

#Region " Collection Base Not Supported Members "

    'None

#End Region 'Collection Base Not Supported Members 

#Region " Sorting "

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer _
                                Implements System.Collections.IComparer.Compare

        'This method compares the two objects based upon the proerty identified by the
        'SortProperty

        'Collect the values of the properties in the SortProperty
        Dim xValue As Object = Me.SortProperty.GetValue(x)
        Dim yValue As Object = Me.SortProperty.GetValue(y)

        'Compare the two values
        If Me.SortDirection = System.ComponentModel.ListSortDirection.Ascending Then
            Return New CaseInsensitiveComparer().Compare(xValue, yValue)
        Else
            Return New CaseInsensitiveComparer().Compare(yValue, xValue)
        End If

    End Function    'Compare

    Public Function CompareProperty(ByVal item As Object, _
                                        ByVal value As Object) As Integer

        'This method compares the sorted property of the item against the value 

        'Collect the values of the properties in the SortProperty
        Dim xValue As Object = Me.SortProperty.GetValue(item)

        'Compare the two values
        If Me.SortDirection = System.ComponentModel.ListSortDirection.Ascending Then
            Return New CaseInsensitiveComparer().Compare(xValue, value)
        Else
            Return New CaseInsensitiveComparer().Compare(value, xValue)
        End If

    End Function    'Compare

    'Provide a property to identify if sorting is automatic when items are added, 
    'removed or changed
    Private mAutoSort As Boolean
    Public Property AutoSort() As Boolean
        Get
            Return Me.mAutoSort
        End Get
        Set(ByVal Value As Boolean)
            Me.mAutoSort = Value
        End Set
    End Property    'AutoSort

#End Region 'Sorting

End Class

Worth noting, the data member is better as a property from what I've read, so your class should have public properties and private variables not public variables.

Hope this is of use to you.

 

[BobRodes]

Nice piece of code, fawkes. I'd say it points up just how much stronger the .Net implementations of viewable lists are than those of VB6. Thanks for sharing!

Bob

 

[fawkes]

The majority of the code came from the visual studio help files, there is a section on building your own collection from the CollectionBase, I added the sorting and finding parts afterwards, if you dig deep enough the information seems to be there eventually.

I agree about the .Net implementation, using a collection allows you to build a truly object based application.

CType(MyComboBox.SelectedItem,MyNamespace.MyObject).MyProperty

The code above is all you need to access any property of the currently selected item for a combo box, a list box isn't too disimilar.

 

[BobRodes]

There's a comma between selecteditem and mynamespace which I assume ought to be a period.

 

[earthandfire]

Bob, CType takes two parameters, an object and a type to which the object should be cast. In this case it is being used to cast MyComboBox.SelectedItem to MyNamespace.MyObject and therefore the comma is correct.


Hope this helps.

 

[chrissie1]

And you can forget all this when you step into .net 2.0.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[SBendBuckeye]

Hello Bob,

Maybe I am missing something obvious here, but you can populate a listbox very easily with anything that supports IList by using the DataSource property. Here is some quick code to demonstrate:

Imports System.Data.SqlClient

Public Class Form3
    Inherits System.Windows.Forms.Form

' " Windows Form Designer generated code "

    Private m_DataSet As New DataSet
    Private m_FormLoaded As Boolean

    Private Sub Form3_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind")
        Dim da As New SqlDataAdapter("Select CustomerID, CompanyName From Customers", cn)
        da.Fill(m_DataSet)
        Me.ListBox1.DataSource = m_DataSet.Tables(0)
        Me.ListBox1.ValueMember = "CustomerID"
        Me.ListBox1.DisplayMember = "CompanyName"
        m_FormLoaded = True
    End Sub

    Private Sub ListBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedValueChanged
        If m_FormLoaded Then
            MsgBox(Me.ListBox1.SelectedValue.ToString)
        End If
    End Sub
End Class

Your stored procedure can return multiple columns which will be available to you behind the scenes if needed.


Have a great day!

j2consulting@yahoo.com

 

[BobRodes]

Yeah, that's simpler. The reason I got into this in the first place was that I had a VB6 application that used the ItemData property to store keys, and I couldn't find the stuff you're showing here. I would say, though, that the way that I put out here is more efficient, in that it uses a firehose cursor and lighter weight objects. (I'd say that the DataSet and DataAdapter are heavier than the DataReader and my little object.) So that might be the reason that the books all mention it.

If I had found your solution first, I would have used it, though.

Thanks for sharing,

Bob

 

[SBendBuckeye]

You may be right, it'd be interesting to find that out. Maybe for one of your advanced classes extra credit, someone could build a testing harness that iterates a few thousand times and reports the results.

Pertaining to building an items collection, you may also want to check out AddRange, BeginEdit and EndEdit which helps cut down flickering because it stops repainting while you are loading the control. These are off the top of my head and I haven't used them, I just remember reading about them so they may be off a bit.

Have a great day!

j2consulting@yahoo.com

 

[BobRodes]

Interesting. And earthandfire, thanks for your response too. I feel like a dummy! LOL

 

[earthandfire]

Coming from you, I had to read that question a couple of times and also re-read fawkes code to make sure I hadn't missed something.

 

[BobRodes]

Sorry to put you to the trouble! LOL

 

[SBendBuckeye]

As an aside, the SelectedItem is actually a DataRowView if I remember correctly, which means anything else in the query is also available. As an example, a query to load the combobox could include CustomerID (ValueMember), CustomerName (DisplayMember), Address, City, State and Zip so that the associated text boxes could be filled in from the selected CustomerID.

Have a great day!

j2consulting@yahoo.com

 

[BobRodes]

Well, SB, the other thing is that my code is unbound, due to the "binding paranoia" from which VB6 coders justifiably suffer. As for making the other things in the query available, the lstItemRow class can define whatever properties one wants, and do the same thing. I guess in the long run its 6 of one and a half dozen of the other.

Bob