vb.netlistviewoledbcommandoledbdatareader

exit function when number of records in 0


I am applying a search filter on my listview (sSQL). I want to cancel the action when it turns out no records were found, otherwise the Listview is empty and I can't undo the filter.

Private Sub LoadStandardListview(Optional ByVal sFilter As String = "")

        Try
            Dim sSQL As String
            Dim sSubStr As String
            Dim strs As String
            If sFilter = "" Then
                sSQL = "SELECT Id,[Document Type],[Document Name],DISnummer,XPostnummer,[Last Accessed],[Document Location] FROM Archief WHERE ([Document Type] Is Not Null) AND ([DISnummer] Is Not Null) AND ([XPostnummer] Is Not Null) AND ([Last Accessed] Is Not Null) ORDER BY Id DESC"
            Else
                sSubStr = " AND [Document Name] LIKE '%" + sFilter + "%' "
                sSQL = "SELECT Id,[Document Type],[Document Name],DISnummer,XPostnummer,[Last Accessed],[Document Location] FROM Archief WHERE ([Document Type] Is Not Null) AND ([DISnummer] Is Not Null) AND ([XPostnummer] Is Not Null) AND ([Last Accessed] Is Not Null)" + sSubStr + "ORDER BY Id DESC"
            End If
            strs = "SELECT Count(*) FROM Archief"
            Dim cmd As New OleDb.OleDbCommand(sSQL, con)
            Dim cmd2 As New OleDb.OleDbCommand(strs, con)
            Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader
            Dim dis As String
            Dim docImage As VariantType
            Dim items As New List(Of ListViewItem)
            dr.Read()
            Dim recordCount As Long
            recordCount = cmd2.ExecuteScalar

            ListView1.BeginUpdate()
            ListView1.Items.Clear()
            While dr.Read
            If dr("Document Type").ToString = "PDF" Or dr("Document Type").ToString = "pdf" Then
                docImage = (ImageList1.Images.Count - 1)
            ElseIf dr("Document Type").ToString = "DOC" Or dr("Document Type").ToString = "doc" Then
                docImage = (ImageList1.Images.Count - 3)
            ElseIf dr("Document Type").ToString = "PPT" Or dr("Document Type").ToString = "ppt" Then
                docImage = (ImageList1.Images.Count - 2)
            End If

            dis = dr("Last Accessed").Substring(0, dr("Last Accessed").IndexOf(" "))
            items.Add(New ListViewItem(New String() {"", dr("Document Name").ToString, dr("DISnummer"), dr("XPostnummer"), dis, dr("Id").ToString, dr("Document Location").ToString}, docImage))
        End While
        ListView1.Items.AddRange(items.ToArray)
        ListView1.EndUpdate()
            sNumRec = items.Count
            If sFilter <> "" Then
                ToolStripTextBox3.Text = sNumRec + " Documents"
            Else
                ToolStripTextBox3.Text = recordCount.ToString + " Documents"
            End If
        Catch ex As Exception
        MsgBox(ex.Message)
        End Try
    End Sub

Is there a way I can cancel the actions when no records are found and populate the listview again with all records?


Solution

  • Dim sSQL As String
                Dim sSubStr As String
                If sFilter = "" Then
                    sSQL = "SELECT Id,[Document Type],[Document Name],DISnummer,XPostnummer,[Last Accessed],[Document Location] FROM Archief ORDER BY Id DESC"
                Else
                    sSubStr = "WHERE [Document Name] LIKE '%" + sFilter + "%' "
                    sSQL = "SELECT Id,[Document Type],[Document Name],DISnummer,XPostnummer,[Last Accessed],[Document Location] FROM Archief " + sSubStr + " ORDER BY Id DESC"
                End If
                Dim cmd As New OleDbCommand(sSQL, con)
                Using dr As OleDbDataReader = cmd.ExecuteReader
                    Dim dis As String
                    Dim docImage As VariantType
                    Dim items As New List(Of ListViewItem)
    
                    If dr.HasRows() Then
    
                        ListView1.BeginUpdate()
                        ListView1.Items.Clear()
    
                        While dr.Read
                            If dr("Document Type").ToString = "PDF" Or dr("Document Type").ToString = "pdf" Then
                                docImage = (ImageList1.Images.Count - 1)
                            ElseIf dr("Document Type").ToString = "doc" Or dr("Document Type").ToString = "docx" Then
                                docImage = (ImageList1.Images.Count - 3)
                            ElseIf dr("Document Type").ToString = "ppt" Or dr("Document Type").ToString = "pptx" Then
                                docImage = (ImageList1.Images.Count - 2)
                            ElseIf dr("Document Type").ToString = "xls" Or dr("Document Type").ToString = "xlsx" Then
                                docImage = (ImageList1.Images.Count - 4)
                            End If
    
                            dis = dr("Last Accessed").Substring(0, dr("Last Accessed").IndexOf(" "))
                            items.Add(New ListViewItem(New String() {"", dr("Document Name").ToString, dr("DISnummer").ToString, dr("XPostnummer").ToString, dis, dr("Id").ToString, dr("Document Location").ToString}, docImage))
    
                        End While
                        ListView1.Items.AddRange(items.ToArray)
                        ListView1.EndUpdate()
                    Else
                        MsgBox("Nothing was found.",, Title)
                    End If
                    dr.Close()
    
                End Using
    

    I changed my code a little. I put

    Using dr as New OleDbDataReader = cmd.ExecuteReader
    If dr.HasRows() Then