lvfunc.vb

LISTVIEW + DATABASE PROGRAMMING FUNCTIONS IN A NUTSHELL This solution contains a classLibrary projec

                    If myResult = 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                End If

                'add the listviewitem to the items collection of the listview
                LV.Items.Add(lvItem)
                'increase the row counter
                CurrentRow += 1
            End While
            'show the total records found
            lblToShowTotRec.Text = "Total " & CurrentRow & " records found."
        Catch ex As Exception
            CustomExceptionMessage(ex)
        Finally
            DR.Close() 'close the datareader when done
            CON.Close() 'close the connection when done
            CMD.Dispose() 'destroy the command object
        End Try
    End Sub
    'This sub is used to fill up a list view with data from the database(SQL SERVER VERSION)
    'With support of showing small icons left of each row.
    'NOTE: You must manualy set the SmallImageList property of the targeted listview to the imagelist
    'from which you will access the icons
    Public Sub FillListViewWithIcon_SQLSRV(ByVal strSQL As String, ByVal CON As SqlConnection, _
    ByVal LV As ListView, ByVal altRowColor As Color, ByVal startAtOddRow As Boolean, _
    Optional ByVal intImgIndex As Integer = 0, Optional ByVal intColCount As Integer = 0)

        'object declaration required for this sub
        Dim CMD As SqlCommand = New SqlCommand(strSQL, CON)
        Dim DR As SqlDataReader
        Dim i As Integer
        Dim CurrentRow As Integer
        Dim myResult As Double

        'try to open the connection
        Try
            If CON.State = ConnectionState.Closed Then
                CON.Open()
            End If
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try

        'now try to load the data into the listview
        Try
            'clear the listview before loading the data
            If LV.Items.Count > 0 Then
                LV.Items.Clear()
            End If

            DR = CMD.ExecuteReader 'execute the command to get the datareader
            If Not DR.HasRows Then Exit Sub 'first check if the datareader has rows or not

            'validate the mentioned column number
            If intColCount = 0 Then intColCount = DR.FieldCount - 1
            If intColCount > DR.FieldCount Then intColCount = DR.FieldCount

            'now read and load the data
            While DR.Read

                'check if the datareader item is null or not
                If DR(0) Is System.DBNull.Value Then
                    'add the listviewitem to the items collection of the listview
                    LV.Items.Add("", intImgIndex) 'if null then add blank
                Else
                    'else add the field value
                    'add the listviewitem to the items collection of the listview
                    LV.Items.Add(DR(0), intImgIndex) 'add the main listitem for each row
                End If

                'add the subitems to the mentioned limit
                For i = 1 To intColCount
                    'check if the datareader item is null or not
                    If DR(i) Is System.DBNull.Value Then
                        LV.Items(CurrentRow).SubItems.Add("") 'if null then add blank
                    Else
                        LV.Items(CurrentRow).SubItems.Add(DR(i)) 'else add the field value
                    End If
                Next

                '..... code for coloring the alternative rows of a listbox.....
                myResult = CurrentRow Mod 2
                'MsgBox(startAtOddRow)
                If startAtOddRow Then 'if the user want to start coloring from the odd row
                    If myResult <> 0 Then
                        LV.Items(CurrentRow).BackColor = altRowColor
                        LV.Items(CurrentRow).UseItemStyleForSubItems = True
                    End If
                    'MsgBox(CurrentRow)
                    'MsgBox(myResult)
                    'MsgBox(LV.Items(CurrentRow).BackColor.ToString)
                Else
                    If myResult = 0 Then
                        LV.Items(CurrentRow).BackColor = altRowColor
                        LV.Items(CurrentRow).UseItemStyleForSubItems = True
                    End If
                    'MsgBox(CurrentRow)
                    'MsgBox(myResult)
                    'MsgBox(LV.Items(CurrentRow).BackColor.ToString)
                End If

                'increase the row counter
                CurrentRow += 1
            End While
        Catch ex As Exception
            CustomExceptionMessage(ex)
        Finally
            DR.Close() 'close the datareader when done
            CON.Close() 'close the connection when done
            CMD.Dispose() 'destroy the command object
        End Try
    End Sub
    Public Sub CustomExceptionMessage(ByVal e As Exception)
        'this sub will handle any exception and show the details of it as a messagebox
        MessageBox.Show("Source : " & e.Source & vbCrLf & "Type : " & _
        e.GetType.ToString & vbCrLf & "Message : " & _
        e.Message, "Exception Details", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    End Sub
    Sub IterateThroughTheSelectedRowsOfListView(ByVal lItems As ListView.SelectedListViewItemCollection)
        'This sub is used to iterate through the selected rows of any listview passed by as 
        'argument and show the records in the console. for this function you need to set
        'the multiselect property of the listbix to true.
        Dim lItem As ListViewItem
        Dim i As Integer
        Try
            For Each lItem In lItems
                Console.WriteLine(lItem.Text & vbTab)
                For i = 0 To lItem.SubItems.Count - 1
                    Console.WriteLine(lItem.SubItems(i).ToString & vbTab)
                Next i
            Next
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try
    End Sub
    Sub IterateThroughTheRowsOfListView(ByVal lv As ListView)
        'This sub is used to iterate through all rows of any listview passed by as 
        'argument and show the records in the console. 
        Dim lItem As ListViewItem
        Dim i As Integer
        Try
            For Each lItem In lv.Items
                Console.WriteLine(lItem.Text & vbTab)
                For i = 0 To lItem.SubItems.Count - 1
                    Console.WriteLine(lItem.SubItems(i).ToString & vbTab)
                Next i
            Next
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try
    End Sub
    Sub ColorAlternateRowsOfListView(ByVal lv As ListView, ByVal altRowColor As Color, _
    ByVal startAtOddRow As Boolean)
        'This sub is used to only color the alternative rows of a listview
        Dim CurrentRow As Integer
        Dim myResult As Double
        Dim lvItem As ListViewItem

        'code for coloring the alternative rows of a listbox
        Try
            For Each lvItem In lv.Items
                myResult = CurrentRow Mod 2
                'check if the user wants to start coloring from the odd row
                If startAtOddRow Then
                    If myResult <> 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                Else 'start from first row(0)
                    If myResult = 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                End If
                CurrentRow += 1
            Next
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try
    End Sub
    Function SelectAnNumericItemOfListView(ByVal lItems As ListView.SelectedListViewItemCollection, _
    ByVal boolListViewItem As Boolean, ByVal subItemIndex As Integer) As Integer
        'Suppose you have selected a row of a listview and you want to get the value
        'of the second column. In cases like that you will need this function.
        'NOTE: 1. This function only selects numeric column values
        '      2. Column values starts from 0
        'This sub is used to get the desired numeric item value of a listview
        Dim lItem As ListViewItem

        'first check if any item is selected or not
        If lItems.Count = 0 Then
            MessageBox.Show("Please select a row.", "No Row Selected", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Function
        End If
        'validate the mention column number, if it exceeds the maximum column number
        If subItemIndex > lItems.Item(0).SubItems.Count - 1 Then
            MsgBox("Mentioned column number is out of range." & vbCrLf & _
            "Maximum allowed range : 0 to " & lItems.Item(0).SubItems.Count - 1, _
            MsgBoxStyle.OKOnly, "Inavlid Column Number")
            Exit Function
        End If

        Try
            For Each lItem In lItems
                'if the user wants the first column i.e the listviewitem then return it
                If boolListViewItem Then
                    'check if the value is numeric or not
                    If IsNumeric(lItem.Text) Then
                        SelectAnNumericItemOfListView = CInt(lItem.Text)
                    Else
                        MsgBox("Please select a column which contains numeric values." _
                        & vbCrLf & "Your selected rows mentioned column's value is : " _
                        & lItem.Text, MsgBoxStyle.OKOnly, "Non-Numeric Column Selected")
                        Exit Function
                    End If
                Else 'return the mentioned column number's value of the selected row
                    'check if the value is numeric or not
                    If IsNumeric(lItem.SubItems(subItemIndex).Text) Then
                        SelectAnNumericItemOfListView = CInt(lItem.SubItems(subItemIndex).Text)
                    Else
                        MsgBox("Please select a column which contains numeric values." _
                        & vbCrLf & "Your selected rows mentioned column's value is : " _
                        & lItem.SubItems(subItemIndex).Text, MsgBoxStyle.OKOnly, _
                        "Non-Numeric Column Selected")
                        Exit Function
                    End If
                End If
            Next
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try
    End Function
    Function SelectAnTextItemOfListView(ByVal lItems As ListView.SelectedListViewItemCollection, _
    ByVal boolListViewItem As Boolean, ByVal subItemIndex As Integer) As String
        'Suppose you have selected a row of a listview and you want to get the value
        'of the second column. In cases like that you will need this function.
        'NOTE: 1. This function only selects string column values
        '      2. Column values starts from 0
        'This sub is used to get the desired text item value of a listview
        Dim lItem As ListViewItem

        'first check if any item is selected or not
        If lItems.Count = 0 Then
            MessageBox.Show("Please select a row.", "No Row Selected", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Function
        End If

        'validate the mention column number, if it exceeds the maximum column number
        If subItemIndex > lItems.Item(0).SubItems.Count - 1 Then
            MsgBox("Mentioned column number is out of range." & vbCrLf & _
            "Maximum allowed range : 0 to " & lItems.Item(0).SubItems.Count - 1, _
            MsgBoxStyle.OKOnly, "Inavlid Column Number")
            Exit Function
        End If

        Try
            For Each lItem In lItems
                'if the user wants the first column i.e the listviewitem then return it
                If boolListViewItem Then
                    SelectAnTextItemOfListView = CStr(lItem.Text)
                Else 'return the mentioned column number's value of the selected row
                    SelectAnTextItemOfListView = CStr(lItem.SubItems(subItemIndex).Text)
                End If
            Next
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try
    End Function
End Class