none
Set selected in listbox from array from table field RRS feed

  • Question

  • Hi Gurus,

    I could do this no problem in Access VBA but in VB.Net I can't find anything that points me correctly in the right direction. I a table I have a field with data like 1;4;23;56

    On my form after selecting the relevant record I want my listbox to show as selected all the rows that correspond to the data from the field. I have the following but don't know how to get the index from the listbox to compare.

    Dim AreasResult As String() = Areas.Split(";")
                    For Each Result In AreasResult
                        For i = 0 To lstAreas.Items.Count - 1
                            Console.WriteLine(lstAreas.Items(i).ToString)
                            Console.WriteLine(CInt(Result))
    
                            If CInt(Result) = i Then '(not correct. Left here for now)
                                lstAreas.SetSelected(i, True)
                                Exit For
                            End If
    
                        Next i
                    Next result
    Any guidance much appreciated

    Monday, June 25, 2018 6:05 PM

Answers

  • Here is a working example which may or may not get you in the right direction or it may not be useful if using a earlier version of Visual Studio (I'm using VS2017) and if the version of Visual Studio is an issue you can still gleem the logic behind this by reading the inline comments.

    Note I refrain from using a DataSet or DataTable as there is no gain from using these containers and instead went with a lightweight container, a List.

    All classes are in the same form but really should be broken out into their own files yet this keeps things simple.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            '
            ' All but B's Beverages are valid so when done three items will be selected
            '
            Dim contactNames = New List(Of String) From
                    {
                        "Ana Trujillo",
                        "Hanna Moos",
                        "B's Beverages",
                        "Victoria Ashworth"
                    }
    
            ' cast the data source of the ListBox to what is was loaded with a List(Of Customer)
            Dim customers = CType(ListBox1.DataSource, List(Of Customer))
    
            '
            ' The Select gets the indices of each customer and the contact name for the customer
            ' where what is displayed is the contact name
            '
            Dim results As IEnumerable(Of TempItem) = customers.
                    Select(Function(item, i) New TempItem With {.Name = item.ContactName, .Index = i}).
                    Where(Function(item) contactNames.Contains(item.Name))
    
            ' prevent unwanted items to be selected
            ListBox1.SelectedIndex = -1
    
            ' see if we have anything to select
            ' ReSharper disable once PossibleMultipleEnumeration
            If results.Any() Then
                For Each item In results
                    ListBox1.SelectedIndex = item.Index
                Next
            Else
                MessageBox.Show("Nothing to select")
            End If
    
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' setup for allowing multiple selections
            ListBox1.SelectionMode = SelectionMode.MultiExtended
    
            ' instantiate the data class
            Dim ops As New DataOperations
            '
            ' set up data source, display member need not be set as it will use
            ' ToString of the Customer class
            '
            ListBox1.DataSource = ops.ReadCustomers()
    
        End Sub
    End Class
    ''' <summary>
    ''' This is for our select above rather than using an 
    ''' anonymous type which some frown upon.
    ''' </summary>
    Public Class TempItem
        Public Property Index() As Integer
        Public Property Name() As String
    End Class
    ''' <summary>
    ''' Class which is responsible for reading the table in a SQL-Server database
    ''' </summary>
    Public Class DataOperations
        '
        ' Server and catalog to create the connection string
        '
        Private Server As String = "KARENS-PC"
        Private Catalog As String = "NorthWindDemo"
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        ''' <summary>
        ''' Read the data into a list
        ''' </summary>
        ''' <returns></returns>
        Public Function ReadCustomers() As List(Of Customer)
            Dim custList As New List(Of Customer)
    
            Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd = New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT CustomerIdentifier, ContactName, ContactTitle FROM Customer"
    
                    cn.Open()
    
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    While reader.Read()
    
                        custList.Add(New Customer() With 
                                        {
                                            .Id = reader.GetInt32(0), 
                                            .ContactName = reader.GetString(1), 
                                            .ContactTitle = reader.GetString(2)
                                        })
    
                    End While
                End Using
            End Using
    
            Return custList
    
        End Function
    End Class
    ''' <summary>
    ''' Concrete class for returning customer data
    ''' </summary>
    Public Class Customer
        Public Property Id() As Integer
        Public Property ContactName() As String
        Public Property ContactTitle() As String
    
        Public Overrides Function ToString() As String
            Return ContactName
        End Function
    End Class
    

    After pressing button 1

    Data behind the code

    You may ask, is there a less involved solution? I'm sure someone could come up with one but this is how I would suggest doing this.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 25, 2018 11:25 PM
    Moderator

All replies

  • See the following

    Private Sub FindMyString(ByVal searchString As String)
       ' Ensure we have a proper string to search for.
       If searchString <> String.Empty Then
          ' Find the item in the list and store the index to the item.
          Dim index As Integer = listBox1.FindString(searchString)
          ' Determine if a valid index is returned. Select the item if it is valid.
          If index <> -1 Then
             listBox1.SetSelected(index, True)
          Else
             MessageBox.Show("The search string did not match any items in the ListBox")
          End If
       End If
    End Sub

    https://msdn.microsoft.com/en-us/library/e5et1818(v=vs.110).aspx?f=255&MSPPError=-2147217396&cs-save-lang=1&cs-lang=vb#code-snippet-2


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 25, 2018 6:09 PM
    Moderator
  • Hi Karen,

    The data in the table is from my Access DB which I'm re-writing to use SQL server & Vb.Net In Access I was able to refer  the listbox index in this manner

    For iItem = 0 To .ListCount - 1
    lngCondition = .Column(1, iItem)

    so the values in the table are reference to the primary key of a table of Areas. The searchstring would therefore not return a valid result.

    Monday, June 25, 2018 6:34 PM
  • Hi Karen,

    The data in the table is from my Access DB which I'm re-writing to use SQL server & Vb.Net In Access I was able to refer  the listbox index in this manner

    For iItem = 0 To .ListCount - 1
    lngCondition = .Column(1, iItem)

    so the values in the table are reference to the primary key of a table of Areas. The searchstring would therefore not return a valid result.

    I will put a code sample together for you so hang in there.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 25, 2018 10:35 PM
    Moderator
  • Here is a working example which may or may not get you in the right direction or it may not be useful if using a earlier version of Visual Studio (I'm using VS2017) and if the version of Visual Studio is an issue you can still gleem the logic behind this by reading the inline comments.

    Note I refrain from using a DataSet or DataTable as there is no gain from using these containers and instead went with a lightweight container, a List.

    All classes are in the same form but really should be broken out into their own files yet this keeps things simple.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
            '
            ' All but B's Beverages are valid so when done three items will be selected
            '
            Dim contactNames = New List(Of String) From
                    {
                        "Ana Trujillo",
                        "Hanna Moos",
                        "B's Beverages",
                        "Victoria Ashworth"
                    }
    
            ' cast the data source of the ListBox to what is was loaded with a List(Of Customer)
            Dim customers = CType(ListBox1.DataSource, List(Of Customer))
    
            '
            ' The Select gets the indices of each customer and the contact name for the customer
            ' where what is displayed is the contact name
            '
            Dim results As IEnumerable(Of TempItem) = customers.
                    Select(Function(item, i) New TempItem With {.Name = item.ContactName, .Index = i}).
                    Where(Function(item) contactNames.Contains(item.Name))
    
            ' prevent unwanted items to be selected
            ListBox1.SelectedIndex = -1
    
            ' see if we have anything to select
            ' ReSharper disable once PossibleMultipleEnumeration
            If results.Any() Then
                For Each item In results
                    ListBox1.SelectedIndex = item.Index
                Next
            Else
                MessageBox.Show("Nothing to select")
            End If
    
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ' setup for allowing multiple selections
            ListBox1.SelectionMode = SelectionMode.MultiExtended
    
            ' instantiate the data class
            Dim ops As New DataOperations
            '
            ' set up data source, display member need not be set as it will use
            ' ToString of the Customer class
            '
            ListBox1.DataSource = ops.ReadCustomers()
    
        End Sub
    End Class
    ''' <summary>
    ''' This is for our select above rather than using an 
    ''' anonymous type which some frown upon.
    ''' </summary>
    Public Class TempItem
        Public Property Index() As Integer
        Public Property Name() As String
    End Class
    ''' <summary>
    ''' Class which is responsible for reading the table in a SQL-Server database
    ''' </summary>
    Public Class DataOperations
        '
        ' Server and catalog to create the connection string
        '
        Private Server As String = "KARENS-PC"
        Private Catalog As String = "NorthWindDemo"
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        ''' <summary>
        ''' Read the data into a list
        ''' </summary>
        ''' <returns></returns>
        Public Function ReadCustomers() As List(Of Customer)
            Dim custList As New List(Of Customer)
    
            Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd = New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT CustomerIdentifier, ContactName, ContactTitle FROM Customer"
    
                    cn.Open()
    
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    While reader.Read()
    
                        custList.Add(New Customer() With 
                                        {
                                            .Id = reader.GetInt32(0), 
                                            .ContactName = reader.GetString(1), 
                                            .ContactTitle = reader.GetString(2)
                                        })
    
                    End While
                End Using
            End Using
    
            Return custList
    
        End Function
    End Class
    ''' <summary>
    ''' Concrete class for returning customer data
    ''' </summary>
    Public Class Customer
        Public Property Id() As Integer
        Public Property ContactName() As String
        Public Property ContactTitle() As String
    
        Public Overrides Function ToString() As String
            Return ContactName
        End Function
    End Class
    

    After pressing button 1

    Data behind the code

    You may ask, is there a less involved solution? I'm sure someone could come up with one but this is how I would suggest doing this.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 25, 2018 11:25 PM
    Moderator
  • Hi Karen, Thanks for this. I will play around with it
    Tuesday, June 26, 2018 1:06 PM
  • Hi Karen, Thanks for this. I will play around with it

    For the record, I used a List(Of String) as I'm use to this but could had been done as shown below.

    Dim contactNames = New List(Of String) From
            {
                "Ana Trujillo",
                "Hanna Moos",
                "B's Beverages",
                "Victoria Ashworth"
            }
    
    Dim contactNamesArray As String() = contactNames.ToArray
    
    
    Dim contactNames2 = New List(Of String)({
                "Ana Trujillo",
                "Hanna Moos",
                "B's Beverages",
                "Victoria Ashworth"
            })
    
    Dim contactNamesArray2 As String() = {
                "Ana Trujillo",
                "Hanna Moos",
                "B's Beverages",
                "Victoria Ashworth"
            }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, June 26, 2018 1:39 PM
    Moderator
  • Now is a great time to learn Entity Framework. If you build the application using EF then the application will be easier in the future. The EF will do most of the details of things like this.


    Sam Hobbs
    SimpleSamples.Info

    Tuesday, June 26, 2018 3:01 PM