CODEWITHSUNDEEP
vb.netvisual-studio-2012listboxoledb
elmonko
elmonko

Reputation: 685

Removing items from a listbox populated via a data query

I'm currently using the below query to populate a listbox from an access database. I would like to be able to filter the results and remove from the listbox any items that are listed in a text file

Dim da As New OleDb.OleDbDataAdapter("", "")
Dim dt As New DataTable
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.aClients & ""
Dim n As Integer


Dim eSearch As String = AllDetails(n).uCode
Dim fSearch As String = AllDetails(n).uOps

da.SelectCommand.Connection.ConnectionString = conn
da.SelectCommand.CommandText = "SELECT Documents.DocName FROM Documents WHERE (Documents.UnitCode = ?) AND (Documents.OpName = ?) AND Documents.Required = True ORDER BY DocName"
da.SelectCommand.Parameters.AddWithValue("@p1", eSearch)
da.SelectCommand.Parameters.AddWithValue("@p2", fSearch)
da.Fill(dt)
dt.Rows.Add("Add Additional Requirement")
lstRequired.DataSource = dt
lstRequired.DisplayMember = "DocName"
lstRequired.Refresh()

I have used below code for the same purpose when using 2 listboxs that are not populated by a query however I can't seem to implement it in conjunction with the query

  Dim R As IO.StreamReader
  R = New IO.StreamReader(tFiles & "PlanExcluded.txt")
  While (R.Peek() > -1)
  lstRequired.Items.Remove(R.ReadLine)
  End While
  R.Close()

Can anyone point me in the right direction?

Thanks

Upvotes: 0

Views: 650

Answers (1)

Steve
Steve

Reputation: 216353

This happen because the Items is a collection of DataRow not a collection of strings.
To be able to remove a particular item you should work on the DataSource binded to the ListBox (the datatable)

Dim dt as DataTable = CType(lstRequired.DataSource, DataTable)
Using R = New IO.StreamReader(tFiles & "PlanExcluded.txt")
    While (R.Peek() > -1)
        Dim rows() = dt.Select("DocName = '" + r.ReadLine + "'")
        For Each row in rows
             row.Delete()
        Next
        dt.AcceptChanges()
    End While
End Using

If your DocName data to remove contains a string with single quotes remember to double the single quote

Dim rows() = dt.Select("DocName = '" + r.ReadLine.Replace("'", "''") + "'")

Upvotes: 1

Related Questions