Isolating a listbox.

[bloomineck]

Hi All,
I have a form with 4 listboxes on it connected up to a "books' database.The four boxes are:
1)Authors (this one is a list of authors names)
2)Publishers (A list of the publishers who have published the authors books)
3)YearPublished(A list of the years in which the book was published)
4Genre (the catergory to which the book belonged.

The problem I have is that I have to click on all 4 boxes to get information up.While this works fine I would also like to be able to just click on less than the 4 boxes for info.For example if I wanted to bring up all authors,I would like to click on that authors name,or if I want to use two boxes like Authors and publishers and so on.As I said at the present I have to click on all 4 to get a result.Is there a way I can code this in so I can use the boxes individually or collectively
Does anyone have any suggestions.Many Thanks.Colin

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDb.OleDbConnection

        Dim dbProvider As String
        Dim dbSource As String

        Dim ds As New DataSet

        Dim tables As DataTableCollection = ds.Tables

        Dim source1 As New BindingSource()

        Dim da As New OleDb.OleDbDataAdapter

        Dim sql As String



        Dim aa As String = authorList.Text

        Dim bb As String = publisherList.Text

        Dim cc As String = yearpublishedList.Text

        Dim dd As String = genreList.Text



        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source = C:\Documents and Settings\Administrator\Desktop\Authors.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()



        sql = "SELECT * FROM books WHERE author = '" & aa & "' AND publisher = '" & bb & "' AND yearpublished = '" & cc & "' AND genre = '" & dd & "' "






        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Authors")


        Dim view1 As New DataView(tables(0))

        source1.DataSource = view1

        DataGridView1.DataSource = view1

        DataGridView1.Refresh()











        con.Close()



    End Sub

[PerplexedB]

Well one way would be to add an "<all>" options in the itemlist of the comboboxes, and set that as the default. You would have to adjust your sql to take into account those "<all>" entries in your parameters (mostly by not including the corresponding filtering clause).

Please let us know if that answers your concern, and if we can be on any further help.

Good luck.

[Lawrence_1]

PLEASE IGNORE, NOT A SOLUTION!

Hi bloomineck,

I suggest using a stored procedure that takes four optional parameters for filtering the results, e.g. author, publisher, year, genre. The SP will only filter the results by parameters provided.

[bloomineck]

Hi PerplexedB,
thanks for your reply.I have been trying to include the "<all>" but am not too sure how to do it(I am a newbie) or alter the sql afterwards.I have been experimenting using "" which I believe to be null,but still haven't found a way round it.I will keep trying but any advice greatly appreciated.
Many thanks
col

[PerplexedB]

One way is to load the items in the itemlist of your comboxes in load event of the form (as opposed to have that done automatically through some sort of binding scheme). Get the entries from the db in a datatable or datareader and copy them in the itemlist. Assuming you want your "<add>" entry as the first (top of the list) entry you would first enter that:

You can use this syntax : ComboBox1.Items.Add("<All>")

You seem to know how to get values from the database into a datatable so then you will load the itemlist as follows.

for each adhocRow in adhocDatatable
    Combobox1.Items.Add(adhocRow("adhocField"))
next

You want your "<All>" entry selected so set

`Combobox1.text = "<All>"

Finally your sql becomes something like this:

sql = "SELECT * FROM books WHERE true = true "
if authorCombobox.Text<>"<All>" then
    sql &= " and author = " & "'" & authorCombobox.text & "'"
endif

and you add the same if statement for each of the filtering fields.

Let me go on the record here to state that this is one way of doing it, and that it's not necessarily the best practice way. I did choose this one because I thought it's the easiest to explain here. And I have not tested the code I'm presenting here, so there may be syntax errors, for which I apologize.

Let us know if that works for you and good luck.