Ad-hoc query built by user

[696796]

Hello all,

This is a toughy, for me anyway...

I am using an access97 database, with visual studio 2003..

I am creating a 'search' form so that a user can search through a set of records. Now, i will have controls on the form so that they can select the criteria for the query. These controls will be listboxes, comboboxes or date/timepickers.

At the moment i have these controls on the form, and they are pulling data from their respective tables. for example...

form load of search form

    Private Sub frmSearchCAR_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim populating As dbaCAR = New dbaCAR
        populating.PopulateLists("tblSupplier", Me.lstSuppliers)
        populating.PopulateLists("tblMaterial", Me.lstMaterials)
    End Sub

this is the way the list is populated in another module

    Public Sub PopulateLists(ByVal strTable As String, ByVal lstControl As ListBox)
        ds = New DataSet
        da = New OleDbDataAdapter("SELECT * FROM " + strTable, conn)
        da.Fill(ds, strTable)
        lstControl.DataSource = ds.Tables(0)
        lstControl.ValueMember = ds.Tables(0).Columns(0).ToString
        lstControl.DisplayMember = ds.Tables(0).Columns(1).ToString
        lstControl.SelectedIndex = -1
    End Sub

(i get my combobox data in the same way)

Now the real question is how do i get the selected values from each list, into a variable. Secondly, how do i get it all into a string that i can push into the database and perfrom a select query?

Any ideas/help/code/pointers will be very much apreciated.

Alex

 

[696796]

Trying to get values into a string:-

        For Each i In lstSuppliers.SelectedItems
            strSuppliers = strSuppliers & i & ","
        Next

 

[rjoubert]

You could try building your WHERE clause within your loop.
I'm assuming that "Supplier" is the name of the field in your table...replace that with whatever field you need to query against.

Dim objItem as ListItem

strWhere = "WHERE"

For each objItem in lstSuppliers.Items
   If objItem.Selected Then
      strWhere = strWhere & " Supplier='" & objItem.Value & "' OR"
   End If
Next

strWhere = strWhere.Substring(0, strWhere.length - 2)

The last line of my code cuts off the last "OR". At this point you would append your WHERE clause to the rest of your SQL statement.

 

[tperri]

I didn't see a method called SelectedItems on the ListBox control while looking in VS. So I suppose I would try this:

        Dim i as ListItem

        For Each i In lstSuppliers.Items
            If i.Selected = True
            
               strSuppliers += Convert.ToString(i) & ","

            End If
        Next

 

[696796]

Thanks for the reply guys, the dim i as Listitem - listitem isnt recognised...

        Dim i As ListViewItem

It comes back saying specified cast is not allowed if i change it to listView item

 

[rjoubert]

Or you could make your Suppliers listbox a ListView control instead of the Listbox control. The ListView control allows you to access a SelectedItems collection.

Dim objItem as ListViewItem

For each objItem in lstSuppliers.SelectedItems
  strWhere = strWhere & " Supplier='" & objItem.Text & "' OR"
Next

strWhere = strWhere.Substring(0, strWhere.length - 2)

 

[rjoubert]

Here's some additional info on the ListView Control...

http://msdn2.microsoft.com/en-us/library/6dwb14tw.aspx

I use it quite frequently, as it has a lot more functionality than the old ListBox control.

Good Luck!

 

[696796]

Thanks rjRobert - i think your right and the listview will perform better - i am seaching the msdn site but cannot see how i can 'bind' it to the data from the database (like i did before with the listbox, below)

    Public Sub PopulateLists(ByVal strTable As String, ByVal lstControl As ListView)
        ds = New DataSet
        da = New OleDbDataAdapter("SELECT * FROM " + strTable, conn)
        da.Fill(ds, strTable)
        lstControl.DataSource = ds.Tables(0)
        lstControl.ValueMember = ds.Tables(0).Columns(0).ToString
        lstControl.DisplayMember = ds.Tables(0).Columns(1).ToString
        lstControl.SelectedIndex = -1
    End Sub

is it something to do with .DataBindings?

 

[rjoubert]

In my current application, I am looping through the DataRows of my DataSet and adding each row to the listview.

Dim objRows as DataRow()
Dim objItem as ListViewItem
Dim i as integer

objRows = ds.Tables(0).Select()

For i = 0 to objRows.Length - 1
   objItem = Me.lstSuppliers.Items.Add(objRows(i).Item("SupplierID"))
   objItem.SubItems.Add(objRows(i).Item("SupplierName"))
Next

In my code example, you would first need to add columns to your ListView. That's easy enough to do in your form Design. Also, in the properties for your ListView, be sure to select "Details" for the "View" property. This will give it the Listbox-like of appearance.