"and" and "or" function in vb.net 2005

Can you do that disabling the 2nd and the 3rd combobox ? By default you leave combo 1 enable and the other disable. Then when the user select the first one (on selected index changed) you enable the 2nd and the same for the third.

jpaulino,
but how do you do this?

Imagine a datatable containing employees which includes location, department and jobTitle.  The users need to be able to select from that, for display in a DataGridView, just those employees in a particular location and/or from a particular department and/or with a particular jobTitle.  Does that, in principle, describe what you are trying to do?

If so, at the simplest level, you put all locations in one combo, all departments in a second and all jobTitles in the third.  When the selectedindex changes on any of the comboboxes, you construct a filter and apply it to the DataView (or BindingSource) which is the DataSource of the DataGridView.  If no selection is made in any of the comboboxes, the filter is blank.  If a selection is made in only one of the comboboxes, the filter is just, for instance

     "Department = '" & DeptCombo.SelectedValue & "'"

If selections are made in two or more comboboxes a statement similar to that is constructed for each and the full filter statement is made up of those with " AND " between them, for example

     "Department = '" & DeptCombo.SelectedValue & "' AND jobTitle = '" & JobCombo.SelectedValue & "'"

I stress, first, that that is "at the simplest level" and, second, that the specific coding for the filter would depend on details I don't know.

If you want to add bells and whistles, it is possible, for instance, when a selection is made in one combobox to limit the entries in the other comboboxes to those that would give only "valid" combinations for the data that you have.  So for instance, if Location1 does not have a DepartmentB but all other locations do have a DepartmentB, it would be possible, as soon as Location1 was selected in the location combobox to remove the DepartmentB entry from the department combobox: and to put it back again as soon as a different location was selected.

So, in principle, it looks as though what you want to do is eminently possible.  But the details of doing it will depend on a much closer definition of the details of what you want to do.  That last point is another way of putting weellio's comment in your other question on this

https://www.experts-exchange.com/questions/22741287/selecting-1-value-from-3-different-combo-boxes-at-one-time-and-individually-with-visual-basic-05.html

Roger

systems_ax Sancler as already made a good explanation how it works.

Sancler,
thank you, that is exactly what I need for my 3 combo boxes, i need to be able to individualy select 1 value and than to be able to select 1 value from 3 combo boxes and the result to show in the datagridview..  Sorry for displaying this question on two different blogs but I need this function as soon as i can get it to work.
I am still searching for good tutorial on how to do this function, as I do not understand how to create a view.
thanks

>>
as I do not understand how to create a view.
<<

Something like this

      Dim dvEmployees As New DataView(<EmployeeDataTable>)
      <EmployeesDataGridView>.DataSource = dvEmployees

You stick the appropriate objects in where I've enclosed things in <>.

Or, if your datagridview already has a BindingSource as its DataSource (with that BindingSource being in turn bound to the datatable concerned) you can simply use the .Filter property of the BindingSource.

Is that enough to get you going?

Roger

Roger,
thank, I am not sure how to create a view period, I found several articles on how to create sql view but I am using Access database for storage purposes.  Is there a good link where you can point me to?
thank you

Roger,
will the statement go under the combo box's index change event:
Dim dvEmployees As New DataView(<EmployeeDataTable>)
      <EmployeesDataGridView>.DataSource = dvEmployees

thanks

In terms of databases (as opposed to VB.NET) what would in SQL be called a "view" would in Access normally be called a "query".  In both cases, rather than just displaying all the data from a table as it stands, the view or query would display that data filtered in some way.  What I was referring to was neither of those, but a "dataview".  That is a construct internal to VB.NET.  It's similar (in database terms) to a view or query but it assumes that there is a datatable in the VB.NET application that has been filled with data from the database table, and it allows the data from that datatable to be filtered - in the app itself - in some way.

There are various ways of combining these bits and pieces from the two ends of the system (the database at one end and the VB.NET app at the other).  So, if you know beforehand that you will want a database table's data always to be filtered in particular ways for the purpose of your VB.NET app, you can create the necessary query in your Access database and fill your application's datatable from that.  Or you can fill your application's datatable with all the data from your database's table and use a dataview to filter it within the application.  Or you can dynamically create a statement within your application which will filter the data as it brings it over from the database.

Data is usually brought over from a database into an application with a dataadapter or a tableadapter.  Those work by the program (or a configuration wizard) creating a Select statement.  At its simplest, such a Select statement will just say something like "SELECT thisField, thatField FROM thatTable".  But a filter can be added to that with a Where clause.  Then it would go something like "SELECT thisField, thatField FROM thatTable WHERE thisField = thisValue".

Forgive me if that all sounds terribly pedestrian, or even patronising.  It isn't meant to be, but I really need to know how much you already know and whether you've made any choices (and if so what) about which of those routes to go down with the app you are developing.  What, so far, does your application contain in terms of dataset/s and/or datatable/s and dataadapter/s and/or tableadapter/s?  You say you are working with VB.NET 2005, and that you've got (or will have) comboboxes and a datagridview.  Are those controls already bound to any datatables?  And, if so, how did you accomplish that: in code or by using the drag and drop facilities that VB.NET 2005 provides?

My current thinking - having re-read both your original post and the rest of the thread - is that rather than using either a "view" (that is, a "query" in Access) or a "dataview" it might be best dynamically to code Select statements in the light of the selections made in the comboboxes.  But I certainly don't want to send you down that line if it's not consistent with what you want or what you have already done.  So, can you please try and give a bit more detail in the light of what I've said above?

For the moment, I don't think directing you to links about one approach or the other would be particularly helpful.  We might come back to that when it's clearer what the detailed approach should be.

Roger

Roger,
I placed the dataview on my form:
but can you explain what these lines mean:
Dim dvEmployees As New DataView(<EmployeeDataTable>)
      <EmployeesDataGridView>.DataSource = dvEmployees
"Department = '" & DeptCombo.SelectedValue & "' AND jobTitle = '" & JobCombo.SelectedValue & "'"

thank you

Roger,
I have an unbound dataset and this is the entire code to diplay the report within a datagridview after the user has made a selection from the combo box:

Private Sub Populate_Report(ByVal Location As String)
        DataGridView1.Columns.Clear()
        OpenDB()
        Location = "'%" & Location & "%'"
        If ComboBox2.Text = "" Then
            MessageBox.Show("Please Enter the Name!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else

            Dim Sql As String = "SELECT Report FROM LocationTable Where Location LIKE " & Location
            Dim cmd As New OleDbCommand(Sql, Conn)
            Dim da As New OleDbDataAdapter
            Dim ds As New DataSet
            Dim dt As DataTable
            da.SelectCommand = cmd

            Try
                da.Fill(ds, "Data")
                dt = ds.Tables("Data")
                DataGridView1.DataSource = dt
                DataGridView1.Columns(0).HeaderText = "Reports"

                For Each dgrow As DataGridViewRow In DataGridView1.Rows
                    If Not IsDBNull(dgrow.Cells("Report").Value) AndAlso dgrow.Cells("Report").Value IsNot Nothing Then
                        dgrow.Cells("Report").Value = dgrow.Cells("Report").Value.ToString.Replace("d:\", "").Replace("D:\", "").Replace(".pdf", "")
                    End If
                Next
                         Catch ex As Exception
                MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            Finally
                da = Nothing
                ds = Nothing
                dt = Nothing
                cmd.Dispose()
                CloseDB()
            End Try
        End If
    End Sub

This is my combo box code:
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        Populate_Report(ComboBox2.text)
    End Sub

Each combo box contains data that is located in different tables and I have 3 tables.  I am not sure which wayto proceed, but I would do the  "dataview" code within vb,net instead of access programming.

this is what I have:
Table 1: No selection
number 1
number 2
number 3

Table 2: No selection
letter a
letter b
letter c

Table 3: No selection
year 1
year 2
year3

So, when user selects number 2 from only 1 combo box, only the reports based on this selection will show, then if they decide to keep this selection in place and select "year 3" from another combo box, the reports that have "number 2 and year 3" will show, and then they can deselect "year 3" by choosing "no selection" and the reports based only on that value from only 1 combo box will display.

My combo box searching works but I am still confused about the dataview creation.
Thank you so much for your help

OK, I've got the picture.  But I'm afraid (I'm in the UK) it's my bed-time.  Sorry to hold you up but, even if I were to write now, I wouldn't trust myself to help rather than hinder as I'm tired.  I'll post a proper answer in (my) morning.

Roger

You know a lot more than I feared may be the case.  You're nearly there.  Again, apologies if I appeared to be talking down to you.

I don't now think a dataview is the right approach here.  This is because, so far as I can see, the criteria against which selections are going to be made are not present in the data in the app.  You could only use a filter such as "[Table 1] = 'number 1'" in a dataview if the datatable IN THE APP for that dataview contained a field/column called Table 1.  On your current approach although you are using Location as a selection criterion you are only actually bringing the one field/column - Report - from Access over into your app's datatable.  So you could not, in effect, say "just show me those records from that datatable that have 'number 1' in the Table 1 field": and that's what dataviews are about.

So what I suggest you do is just extend the approach that you are already using to select reports, from Access itself, by Location.  You already have a Where clause in that.  All that needs to happen is for that to be extended by adding the other selection criteria into it with AND.  So, instead of the SQL statement being just

    "SELECT Report FROM LocationTable Where Location LIKE " & Location

if you made it

    "SELECT Report FROM LocationTable Where Location LIKE " & Location & " AND [Table 1] = '" & table1Combo.Text & "'"

your current code would, it looks to me, do what you want.  

You would need to call it in a slightly different way.  First, I think you would want to call the sub from the SelectedIndexChanged event from each of the comboboxes.  Secondly, I think you would need to generalise it so that, rather than a specific argument being passed from each combobox to it, the sub itself (or another sub which that sub called) checked the status of each combobox to build up an SQL statement reflecting the combined picture.  Something like

    Dim SQL As String = "SELECT Report FROM LocationTable Where Location LIKE " & Location
   If table1Combo.Text <> "No selection" Then
       SQL &= " AND [Table 1] = '" & table1Combo.Text & "'"
   End If
   If table2Combo.Text '... etc

Does that make sense?

If it does, but you are having trouble with the detailed implementation, let me know what the problems are.  If the precise wording of the SQL statement is giving problems, one tip is to use Access itself to develop a query that does what you want and then use the SQL of that as a pattern.

Or, if it doesn't make sense, let me know that.

Roger

Roger,
I have inputted this code under the Dim SQL As String = "SELECT Report FROM LocationTable Where Location LIKE " & Location

'If ComboBox2.Text <> "No selection" Then
    '    Sql &= " AND [Days] = '" & ComboBox2.Text & "'"

    'End If
    'If ComboBox5.Text <> "No selection" Then '... etc
    '    Sql &= " AND [Months] = '" & ComboBox5.Text & "'"
    'End If


    'If ComboBox3.Text <> "No selection" Then '... etc
    '    Sql &= " AND [Years] = '" & ComboBox3.Text & "'"
    'End If

and the code breaks, AM I missing something?
thank you

What datatypes are the fields in the database for [Days], [Months] and [Years]?  If they are numerical, as looks possible, then you won't need the single quotes.  So

'If ComboBox2.Text <> "No selection" Then
    '    Sql &= " AND [Days] = " & ComboBox2.Text

rather than

'If ComboBox2.Text <> "No selection" Then
    '    Sql &= " AND [Days] = '" & ComboBox2.Text & "'"

etc.

If that doesn't fix it.  Can you tell me what the full error message is?  I assume it is occuring in the catch block.  Temporarily add this line

                Debug.WriteLine (ex.ToString)
                Debug.WriteLine (cmd.CommandText)

immediately before or after the present

                MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")

and copy the output from that into a post here.

I'll not be about for long now though.  It's my bedtime again ;-)

Roger

Roger,
I think I just did not think it through enought but I think I finally came up with another I think better approach.
Right now I have 3 data tables where the data is pulled from.
I AM NOT USING DAYS AND MONTHS, SORRY IT IS JUST AN EXAMPLE.
I AM GOING TO STICK WITH 3 CRITERIA : BOOK/MAGAZINE, 100/200/300, DAY 1, DAY 2, DAY 3.
I am thinking about putting everything in one table for instance:
I will have a field for:
1) book 100 day 1
2) book 100 day 2
3) magazine 100 day 1
4) magazine 200 day 1

and then have 3 same combo boxes:
combo #1:
book
magazine

combo #2:
100
200
300

combo #3:
Day 1
Day 2
Day 3

so whenever the user selects "book" from combo #1, the search will return all of the reports that have word Book in them.
then they will keep the selection of "book" and select "100" from combo #2 and only reports that have words "book and 100" will display.  Then they will keep these selections active and will select "day 1" from combo #3 and every report in this 1 single table will show all reports that contain "book and 100 and day 1".

Can you please help, or should I stick to the original design, I am comlpetely lost.

I have also tried this with no success as I still do not understand it:
Private Sub Populate_Condition(ByVal MenuConditionName As String)
        DataGridView1.Columns.Clear()
        OpenDB()
        MenuConditionName = "'%" & MenuConditionName & "%'"
        If cmbCategory.Text = "" Then
            MessageBox.Show("Please Enter the Recipe Name!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
                     Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like " & MenuConditionName
            If cmbCategory.Text <> "book" Then
           Sql &= " AND [MenuCalories] = '" & cmbCategory.Text
            End If
         
           
            Dim cmd As New OleDbCommand(Sql, Conn)
            Dim da As New OleDbDataAdapter
            Dim ds As New DataSet
            Dim dt As DataTable
            da.SelectCommand = cmd

            Try
                da.Fill(ds, "Data")
                dt = ds.Tables("Data")
                DataGridView1.DataSource = dt
                DataGridView1.Columns(0).HeaderText = "Menu Reports"
                For Each dgrow As DataGridViewRow In DataGridView1.Rows
                    If Not IsDBNull(dgrow.Cells("MenuReport").Value) AndAlso dgrow.Cells("MenuReport").Value IsNot Nothing Then
                        dgrow.Cells("MenuReport").Value = dgrow.Cells("MenuReport").Value.ToString.Replace("d:\", "").Replace("D:\", "").Replace(".pdf", "")
                    End If
                Next
                'If x <= 0 Then
                          Catch ex As Exception
                Debug.WriteLine(ex.ToString)
                Debug.WriteLine(cmd.CommandText)
                MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            Finally
                da = Nothing
                ds = Nothing
                dt = Nothing
                cmd.Dispose()
                CloseDB()
            End Try
        End If
    End Sub

I am recieving this message:
syntax error in string in query expression 'menuconditionname like '%book%' and [menu calories]='100'0
Thank you so much, I will be working on this this weekend, because I am really lost in how to do this function.
thank you

Yes, I'll give you a hand on the alternative approach - which will involve a dataview ;-) - but in the morning.  But in preparation for that, can you please clarify a bit on the single table idea.  You say

>>
I will have a field for:
1) book 100 day 1
2) book 100 day 2
3) magazine 100 day 1
4) magazine 200 day 1
<<

A more "normal" table design, and much easier to work with, would be to have separate fields for

Medium: which would have a value of either Book or Magazine
Calories: which would have a value of 100, or 200, or 300
Day: which would have a value of 1, or 2, or whatever
MenuReport: ???
MenuConditionName: ???

That, I must say, was my impression (with different field names) of your current Access table.  If that impression is wrong, then it may account for our lack of success so far.  So I'd like to get it clearer in my own mind what the Access table structure is before going off on any new tack (or even proceeding any further down this one).

For now, the error message appears to arise because, on the code you show here

>>
                     Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like " & MenuConditionName
            If cmbCategory.Text <> "book" Then
           Sql &= " AND [MenuCalories] = '" & cmbCategory.Text
<<

there is an opening single quote before but no closing single quote after cmbCategory.Text.  Either the datatype of MenuCalories is numeric, in which case you don't need a single quote beforehand, or it is text/string, in which case you do need a single quote after it.  But mix'n'match won't work.

I say "appears to arise" because the error message, as you have quoted it, shows an opening and closing quote but then an extra 0.  If that is the exact error message, I don't at the moment understand it.  But the code I have cited above is so obviously in error in the way I have described that I think you should try correcting that in any event.

Goodnight ;-)  I'll check in again in the morning.

Roger

I've constructed a demo of the new approach.  So as to make the data set-up as similar as I can to what I think you might have, I've based it on Northwind - which I assume will be somewhere on your system.  To set up the demo, you first need to create a new query in that.  If you open a new query in SQL mode and paste the following into it

SELECT Products.ProductID, Products.ProductName AS Product, Suppliers.CompanyName AS Supplier, Categories.CategoryName AS Category, Str([Products]![ReorderLevel]) AS Reorder
FROM Suppliers INNER JOIN (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) ON Suppliers.SupplierID = Products.SupplierID;

and then save it as qDemo, that should do the trick.  Although this is actually a Query in Access it will be treated, for the purposes of the VB.NET demo as a Table.  My aim in setting it up was to get a number of String fields among which selections could be made in comboboxes.

Now for the VB.NET part.  Open a new project and on Form1 place one datagridview - called dgvProduct - three comboboxes - called cboSupplier, cboCategory and cboReorder - and a textbox - called txtLetter.  Then copy the following code into its code pane, replacing _everything_ that is there when the code pane first opens.  You will probably need to change the DatabasePath to the proper one for your system.  Apart from that it should work as it stands.

Imports System.Data.OleDb

Public Class Form1

    Private SQLMain As String = "SELECT ProductID, Product, Supplier, Category, Reorder FROM qDemo"
    Private DatabasePath As String = "C:\MSOffice\Access\Samples\Northwind.mdb"
    Private conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabasePath
    Private con As New OleDbConnection(conString)
    Private daMain As New OleDbDataAdapter(SQLMain, con)
    Private dtMain As New DataTable("Main")
    Private dvMain As New DataView(dtMain)
    Private dtSuppliers As New DataTable
    Private dtCategories As New DataTable
    Private dtReorder As New DataTable
    Private loading As Boolean = True

    Private Sub filltables()

        Dim dr As DataRow
        daMain.Fill(dtMain)

        dvMain.Sort = "Supplier"
        dtSuppliers = dvMain.ToTable("Suppliers", True, New String() {"Supplier"})
        dr = dtSuppliers.NewRow
        dr(0) = "Any"
        dtSuppliers.Rows.InsertAt(dr, 0)

        dvMain.Sort = "Category"
        dtCategories = dvMain.ToTable("Categories", True, New String() {"Category"})
        dr = dtCategories.NewRow
        dr(0) = "Any"
        dtCategories.Rows.InsertAt(dr, 0)

        dvMain.Sort = "Reorder"
        dtReorder = dvMain.ToTable("Reorder", True, New String() {"Reorder"})
        dr = dtReorder.NewRow
        dr(0) = "Any"
        dtReorder.Rows.InsertAt(dr, 0)

        dvMain.Sort = "Product"

    End Sub

    Private Sub bindcontrols()

        cboSuppliers.DataSource = dtSuppliers
        cboSuppliers.DisplayMember = "Supplier"

        cboCategories.DataSource = dtCategories
        cboCategories.DisplayMember = "Category"

        cboReorder.DataSource = dtReorder
        cboReorder.DisplayMember = "Reorder"

        dgvProduct.AutoGenerateColumns = False
        Dim col As New DataGridViewTextBoxColumn
        col.HeaderText = "Product Name"
        col.DataPropertyName = "Product"
        col.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        dgvProduct.Columns.Add(col)

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        filltables()
        bindcontrols()
        loading = False
    End Sub

    Private Sub applyMainFilter()

        If loading Then Exit Sub

        If txtLetter.Text = "" Then
            MsgBox("You must enter a letter")
            dgvProduct.DataSource = Nothing
            txtLetter.Focus()
            Exit Sub
        End If

        Dim SQL As String = "Product LIKE '%" & txtLetter.Text & "%'"
        If cboSuppliers.Text <> "Any" Then
            SQL &= " AND Supplier = '" & cboSuppliers.Text & "'"
        End If
        If cboCategories.Text <> "Any" Then
            SQL &= " AND Category = '" & cboCategories.Text & "'"
        End If
        If cboReorder.Text <> "Any" Then
            SQL &= " AND Reorder = " & cboReorder.Text
        End If

        dvMain.RowFilter = SQL
        dgvProduct.DataSource = dvMain

    End Sub

    Private Sub cboCategories_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCategories.SelectedIndexChanged
        applyMainFilter()
    End Sub

    Private Sub cboReorder_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboReorder.SelectedIndexChanged
        applyMainFilter()
    End Sub

    Private Sub cboSuppliers_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboSuppliers.SelectedIndexChanged
        applyMainFilter()
    End Sub
End Class

The real meat is in the sub applyMainFilter, but I ought to explain some other aspects of how it works.  I decided it might be better to do that in narrative form rather than in commenting the code, but I tried to divide the code into logical lumps with line spacing.

First, just one datatable - dtMain - is structured, and filled with data, by a single dataadapter.  Then a dataview is created pointing to that table.  The main purpose of that dataview is to allow the filtering in the sub applyMainFilter but it is first put to a different use.  We need the various comboboxes to be filled with distinct items from the relevant fields/columns in the main datatable among which valid selections can be made.  The [dataview].ToTable method allows us to do that.  So this line

        dtSuppliers = dvMain.ToTable("Suppliers", True, New String() {"Supplier"})

makes the dtSuppliers datatable into a datatable called "Suppliers" - that's the first argument - containing only DISTINCT values - that's the True as the second argument - from the single column "Supplier" in the main datatable - that's the third argument, which has to be in the form of a String array.  The dataview has first been sorted on the Supplier field - that's this line

        dvMain.Sort = "Supplier"

- so the resulting Suppliers datatable is in alphabetical order.  But an "Any" item is then added at its start, to allow for the cancellation of the selection.

Although that may appear to depart from the "one table" idea, it doesn't really, as the sole purpose of creating this "extra" suppliers datatable is to make sure that the suppliers combobox has in it only items compatible with those in the main table.  That's achieved by binding the suppliers combobox to the suppliers datatable.  These two lines do that

        cboSuppliers.DataSource = dtSuppliers
        cboSuppliers.DisplayMember = "Supplier"

I've split up the data manipulation and the binding into two subs, but they could all go together if you preferred.

A similar process is adopted for the other two comboboxes.

Once, in the filltables sub, the dataview has been used for these secondary purposes, it is sorted on Product, so that those will appear in alphabetic order in the datagridview.

In the bindcontrols sub, once the comboboxes have been sorted out, there is code to set up the datagridview.  If we let it autogenerate columns it will show all the fields from the main datatable, but we only want to show one.  So the code makes an appropriate column, with appropriate binding, for that.

I've included the textbox just so as to try and replicate the "X LIKE '%Y%'" part of your existing code, but I'm not really sure what that's doing.

The final point is that bit at the beginning

    Private loading As Boolean = True

The purpose of that is to stop unwanted execution of the applyMainFilter sub.  The comboboxes' SelectedIndexChanged events will fire when the comboboxes are being databound in code.  The loading flag stops that causing unwanted effects.

Give it a try, and let me know how you get on (a) with this as a demo and, if that is OK, (b) with trying to translate it into your own set-up.

Roger

Roger,
I am very sorry for getting back soo late.  But I think I am going to go with only 1 table.  This is the table:
Menu ID     MenuCategories                      MenuReports
1               Heart 100 Day 1                    d:\Heart 100 Day1.pdf
2               Heart 200 Day 2                       d:\Heart 200 Day 2.pdf
3               Heart 300 Day 3       d:\Heart 300 Day 2.pdf
4               Liver 100 Day 1                              d:\Liver 100 Day 1.pdf
5               Liver 200 Day 2                        d:\Liver 200D ay 2.pdf
6               Liver 300 Day 3                           d:\Liver 300 Day 3.pdf
and then 3 combo boxes:
combo box #1: heart, liver
combo box #2: 100, 200, 300
combo box #3:  1, 2, 3,

So, I think this is a COMPLETELY different approach.  So, when user selects "heart", it will pull all the reports that have word "heart" in them.  Then the user will leave the heart and select "100" from combo box #2 and that will narroe the search down to only heart and 100 and will show all the pdf's.  Then I will select "3" from combo box #3 and the search will show ONLY "heart 100 day 3" reports.
I am very sorry for changing things around all the time but I AM SURE THAT this is THE EASIEST APPROACH TO WORK WITH, I just need to re-code my SQL statements and I have tried everything.  Do I need to create VIEW, IS IT MANDATORY TO CREATE A VIEW WHEN YOU WORK WITH ONLY 1 TABLE.
Thank you so much, I am still reading your 2 latest posts.
thank you

It is not MANDATORY to create a View at all: whether you work with one table or three tables or whatever.

Roger

Roger,
I actually tried it my less experienced way and I made 2 changes, so now I am able to select 1 value from combo box #1 and then when I select a value from combo box #2 it DOES narrow the search.  BUT, the third combo box generates an error so I am still working on it.  I think it is this line that is A KEY:

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%'" & cmbDays.Text

I think that I am EXCLUDING the combo box #3.
This is a complete code:

Private Sub Populate_Condition(ByVal MenuConditionName As String)
        DataGridView1.Columns.Clear()
        OpenDB()
        'MenuConditionName = "'%" & MenuConditionName & "%'"==TOOK THIS PART OUT
        If cmbCategory.Text = "" Then
            MessageBox.Show("Please Enter the Recipe Name!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
         
            Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%'" & cmbDays.Text===MADE CHANGE HERE BUT IT EXCLUDES THE THIRD COMBO BOX SO I AM STILL WORKING ON IT

            Dim cmd As New OleDbCommand(Sql, Conn)
            Dim da As New OleDbDataAdapter
            Dim ds As New DataSet
            Dim dt As DataTable
            da.SelectCommand = cmd
            Try
                da.Fill(ds, "Data")
                dt = ds.Tables("Data")
                DataGridView1.DataSource = dt
                DataGridView1.Columns(0).HeaderText = "Menu Reports"
                For Each dgrow As DataGridViewRow In DataGridView1.Rows
                    If Not IsDBNull(dgrow.Cells("MenuReport").Value) AndAlso dgrow.Cells("MenuReport").Value IsNot Nothing Then
                        dgrow.Cells("MenuReport").Value = dgrow.Cells("MenuReport").Value.ToString.Replace("d:\", "").Replace("D:\", "").Replace(".pdf", "")
                    End If
                Next
                Catch ex As Exception
                Debug.WriteLine(ex.ToString)
                Debug.WriteLine(cmd.CommandText)
                MsgBox("Error while executing: (" & cmd.CommandText & ")" & vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "Error")
            Finally
                da = Nothing
                ds = Nothing
                dt = Nothing
                cmd.Dispose()
                CloseDB()
            End Try
        End If
    End Sub

CAN YOU PLEASE OFFER AN ADVISE, I SPENT ABOUT 2 HOURS NOW TRYING TO FIGURE OUT THIS SQL STATEMENT THAT I KNOW I'M DOING WRONG THAT IS WHY IT EXCLUDES THE COMBO BOX #3:

Try this

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%' AND MenuConditionName Like '%" & cmbDays.Text & "%'"

instead of

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%'" & cmbDays.Text

Roger

Roger,
You are genious, it actually works.  But it is still buggy because I have two items in my combo box : "heart" and "liver" and every time I select "heart" the datagridview shows all the reports within the database including liver, so I am working on this bug right now.
And also every time i have 3 items selected and then I select "No selection" in one combo box and leave the other 2 filled, the reports dissapear.

Thank you so much for your help.

The code that you showed (even after my amendment) only took account of selections from 2 comboboxes - cmbCalories.Text and cmbDays.Text.  So it's not surprising that the Heart/Liver wasn't filtered.

And then, if you just use that code when one of the comboboxes says "No Selection" the SQL would turn out something like this

"SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%100%' AND MenuConditionName Like '%No Selection%'"

As none of the MenuConditionName values in your table will match the second part of that, nothing will be included in the datatable returned.

You need to go back to your post #19666609 above, and adapt that to the table setup and names you are now using.

Roger

Roger,
I have tried this and now it does filter by every selection in each combo box appropriately:

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%' AND MenuConditionName Like '%" & cmbCategory.Text & "%' AND MenuConditionName Like '%" & cmbDays.Text & "%'"

But it still does not keep the reports when "no selection" is chosen
Thank you so much,
I will work on this "no selection" problem tonight.

Roger,
I am still working on th "on selection", can you post your answer on this:
https://www.experts-exchange.com/questions/22741287/selecting-1-value-from-3-different-combo-boxes-at-one-time-and-individually-with-visual-basic-05.html

so that i can give you 1000 combined points as you helped me out very much

Thanks, but posting double answers to get double points is against hte spirit, even if not against the letter, of the rules.  So no thanks.

What's the remaining problem with the "no selection"?  As the earlier posts discussed, what you need to do to cope with that is build your total SQL statement up in bits, only including the bit for a particular combobox if that combobox does NOT show "no selection".

Roger

Roger,
this is the coding and it does not seem to work still.  the cycling works when I exclude the "if" statements, but when I do include them underneath the SQL statement I am generating an error, am I even coding this correctly?

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%' AND MenuConditionName Like '%" & cmbCategory.Text & "%' AND MenuConditionName Like '%" & cmbDays.Text & "%'"

        If cmbCategory.Text <> "(No Selection)" Then
            Sql &= cmbCategory.Text & " '"
        End If
        If cmbCalories.Text <> "(No Selection)" Then
            Sql &= cmbCalories.Text & "'"
        End If
        If cmbDays.Text <> "(No Selection)" Then
            Sql &= cmbDays.Text & "'"
        End If

Thank you

Let's go back to first principles.  What you are trying to do is to construct a string that will be used by your application to get hold of specific records from your database.  If you want _all_ records from your database it would be simply

       "SELECT MenuReport FROM MenuCondition"

If you want only _some_ records, the string has to tell the database how to distinguish which records to include.  It does that by adding to the above (for instance)

       " Where MenuConditionName Like '%" & cmbCalories.Text & "%'"

You can either declare all of that as one string

       Dim SQL As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%'"

Or you can build the string up with concatenation

       Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
       SQL &= " Where MenuConditionName Like '%" & cmbCalories.Text & "%'"

Whichever way the string is constructed it will, before it is actually used, have the value from cmbCalories.Text put into it.  So, if the value of cmbCalories.Text when this code is run is 100, the string that will actually be sent to the database will be

    SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%100%'

As some of the values in MenuConditionName do include 100 and some of them don't, that will cause the database only to send to your app those records which do include it.

We can now add another condition

   " AND MenuConditionName Like '%" & cmbCategory.Text & "%'"

Again, we can either put everything in the string to start with

       Dim SQL As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%' AND MenuConditionName Like '%" & cmbCategory.Text & "%'"

or we can build it up bit by bit

       Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
       SQL &= " Where MenuConditionName Like '%" & cmbCalories.Text & "%'"
       SQl &= " AND MenuConditionName Like '%" & cmbCategory.Text & "%'"

Again, when it is actually used the values of both comboboxes will be substituted in it so if cmbCalories shows 200 and cmbCategories shows Heart what will actually be sent to the database will be

    SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%100%' AND MenuConditionName Like '%Heart%'

Similarly with the third condition.

Now, when a combobox shows "(No selection)" what does it mean?  Use cmbCalories as the example.  It does not mean that the relevant part of the string that we are building up wants to say, after the value in the combobox has been substituted

    SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%(No selection)%' AND MenuConditionName Like '%Heart%'

Rather, it means that the string does not want to include any reference to that combobox at all.  We would want it to say

    SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%Heart%'

The implication of all that is that you cannot simply use the string that assumes that all comboboxes have values in them - this one

Dim Sql As String = "SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%" & cmbCalories.Text & "%' AND MenuConditionName Like '%" & cmbCategory.Text & "%' AND MenuConditionName Like '%" & cmbDays.Text & "%'"

- and then tack onto the end code to deal with cases where there is no selection in one or more of the comboboxes.

It means that every time you want to use the string you have to build it up by checking if there is a selection in each combobox and, if only if there is, including the relevant bit in the string.

Those are the principles.  But there is then the question of the detailed syntax.  The "bits" that have to be added if they are needed are not just the names of the comboboxes with the odd bit of punctuation thrown in, like this

        If cmbCategory.Text <> "(No Selection)" Then
            Sql &= cmbCategory.Text & " '"
        End If
        If cmbCalories.Text <> "(No Selection)" Then
            Sql &= cmbCalories.Text & "'"
        End If
        If cmbDays.Text <> "(No Selection)" Then
            Sql &= cmbDays.Text & "'"
        End If

As illustrated above, if we need to add things the first one we add has to be in the form

       SQL &= " Where MenuConditionName Like '%" & <comboname>.Text & "%'"

and any second or subsequent one has to be in the form

       SQL &= " AND MenuConditionName Like '%" & <comboname>.Text & "%'"

I could write the specific code for you, but I don't really want to do that.  It's important when developing to understand what the code is doing and how and why.  And I don't think that, if I just provide code which you copy, that will happen.

Roger

Roger,
thank you I will try to figure it our as I do want to understand the functionality
thanks again

Roger,
I think I understand now, the code does not work though, this is my latest code:Am I on the right path to getting it finally done?

Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
          If cmbCategory.Text <> "(No Selection)" Then
             SQL &= "Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        Else
            SQL &= " & cmbCategory.Text " = "" & ""
        End If

         If cmbCalories.Text <> "(No Selection)" Then
            SQL &= "Where MenuConditionName Like '%" & cmbCalories.Text & "%'"
        Else
            SQL &= " & cmbCalories.Text " = "" & ""
        End If

        If cmbDays.Text <> "(No Selection)" Then
              SQL &= "Where MenuConditionName Like '%" & cmbDays.Text & "%'"
        Else
            SQL &= " & cmbDays.Text " = "" & ""
        End If

thank you

The way to check it out is to do what I did in my last post and see what the string would actually work out to be in different conditions.  Like this

     Dim SQL As String = "SELECT MenuReport FROM MenuCondition"

The resulting string is

        SELECT MenuReport FROM MenuCondition

So far so good.  Now, if this condition is met

         If cmbCategory.Text <> "(No Selection)" Then

the code that executes is

             SQL &= "Where MenuConditionName Like '%" & cmbCategory.Text & "%'"

So, assuming cmbCategory.Text is Heart the string would become

       SELECT MenuReport FROM MenuConditionWhere MenuConditionName Like '%Heart%'

That's nearly right, but not quite.  "MenuConditionWhere" should be "MenuCondition Where".  So that means that this line

             SQL &= "Where MenuConditionName Like '%" & cmbCategory.Text & "%'"

should have a space at the start, becoming

             SQL &= " Where MenuConditionName Like '%" & cmbCategory.Text & "%'"

But what happens if that condition is NOT met: that is, if cmbCategory.Text = "(No Selection)"?  These lines get executed instead

        Else
            SQL &= " & cmbCategory.Text " = "" & ""

That doesn't actually make sense, but even if it did we wouldn't want it.  If there is no selection we don't want to add anything for that combobox at all.  So, from that point of view, the first few lines should really go

         Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
         If cmbCategory.Text <> "(No Selection)" Then
             SQL &= " Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        End If

I thought, though, that you only wanted to display results if the user had chosen at least a category.  Assuming that is still the case, you do need an Else.  So it would go something like this

         Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
         If cmbCategory.Text <> "(No Selection)" Then
             SQL &= " Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        Else
             MsgBox("You must pick a cateogry")
             Exit Sub
        End If

Then we go to the blocks for the other two combos, with similar results.  In both cases, we need get rid of the Else and the line following it: in those other cases, you don't need a message box.  But that's still going to leave a bit of a problem.  Assume that cmbCategory still shows Heart and cmbCalories shows 100 (and add the space in at the start of the line in the cmbCalories code).  The string when the code has executed that far is going to be

       SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%Heart%' Where MenuConditionName Like '%100%'

That's not quite right, because the second Where should be And.  It should read

       SELECT MenuReport FROM MenuCondition Where MenuConditionName Like '%Heart%' And MenuConditionName Like '%100%'

Does that make it clearer?

Roger

Roger,
thank you very much, the code is perfect; however, I still working on it because "unless" a category is selected, the code breaks into syntax error.
So, if I select "category" the code cycles perfectly, when I put "No selection" under category and select either a "calorie" or "days" I receive the following syntax error:

"error while executing : (select menureport from menuconditionand menuconditionname like '%1800')
msg. syntax error in From clause"

I am pretty positive that it has something to do with "where" and "and" clauses in between the bits.
Am I correct?  thank you so much

A where clause in an SQL statement is equivalent in terms of VB coding to

     If Condition Then
         'include record
     Else
         'exclude record
     End if

Condition, in that, is some expression that evaluates to True or False.  So in practice it would be, for instance

     If Calories=100 Then
         'include record
     Else
         'exclude record
     End if

or

     If Day=1 Then
         'include record
     Else
         'exclude record
     End if

The logical operators - AND, OR, etc. - allow True and False to be combined to produce one overall result.  True AND True = True.  True AND False = False.  False AND True = False.  And so on.  So it is possible to combine conditions like this

     If Calories=100 AND Day=1 Then
         'include record
     Else
         'exclude record
     End if

It is only if it is both True that Calories=100 and True that Day=100 that the overall result will be True so that the record will be included.

Coming back to SQL statements, the WHERE in those is equivalent to the If in the VB coding.  It _must_ be there, and it _must_ be the first word in the clause.  After that, if there is only one condition, all that is needed is the condition: like Calories=100 or, in your case, "MenuConditionName Like '%" & cmbCalories.Text & "%'".  If there is more than one condition those conditions _must_ be separated by AND (and there must be spaces either side of the AND).

The SQL statement that is reporting the error doesn't comply with those rules.

select menureport from menuconditionand menuconditionname like '%1800'

does not say WHERE before the condition of "menuconditionname like '%1800'".  Nor, in the event, does it even say AND, but that is because the space before the intended AND is missing.  Even if it did say AND it would be wrong because AND is supposed to go _between_ two conditions not before the first of them.  It is WHERE that is supposed to go before the first condition.

The reason that this is happening is because the word WHERE is hard-coded into this bit

         Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
         If cmbCategory.Text <> "(No Selection)" Then
             SQL &= " Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        End If

whereas the word AND is hard-coded into the other bits (perhaps without a leading space).  The approach of hard-coding the words WHERE and AND into different bits will only work if the bit in which the word WHERE appears is always used, and always used first.  That's why I went on, in post #19713432, to say

>>
I thought, though, that you only wanted to display results if the user had chosen at least a category.  Assuming that is still the case, you do need an Else.  So it would go something like this

         Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
         If cmbCategory.Text <> "(No Selection)" Then
             SQL &= " Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        Else
             MsgBox("You must pick a cateogry")
             Exit Sub
        End If
<<

If you've now changed your mind on that, the code will need to be changed.

Is the explanation above enough to enable you to do that yourself?

Roger

Roger,
Here is what I tried, but INSTEAD of receiving an error the datagridview is completely cleared out.  When I first pull up the form I CAN go to calories and select the calories and ONLY reports that have those calories DO display, same applies to days, but when I select "no selection" under category the datagrid clears out but NO errors are generated. I am still working on this code but I do need to be able to generate reports based on whatever criteria so the search will not depend on the category selection
Also, I am trying to get the "No selection" to appear within the combo box upon load if the from so that the combo boxes are not blank.  BUt this is the current code:

This is the code:
Dim SQL As String = "SELECT MenuReport FROM MenuCondition"
        If cmbCategory.Text <> "(No Selection)" Then
            SQL &= " where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        Else
cmbCategory.Text = "(No Selection)"
            Exit Sub
end if

I am still working on it, thank you for your great help.

We don't seem to be getting very far.

I think the simplest thing would be as follows.  In this particular setting there are only 8 possible combinations

no selection in any combo
a selection in combo1 only
s selection in combo2 only
a selection in combo1 and combo2
a selection in combo3 only
a selection in combo1 and combo3
a selection in combo2 and combo3
a selection in all three combos

If we set those all out in a string array, like this

        Dim Conditions(7) As String
        Conditions(1) = "Where MenuConditionName Like '%" & cmbCategory.Text & "%'"
        Conditions(2) = "Where MenuConditionName Like '%" & cmbCalories.Text & "%'"
        Conditions(3) = "Where MenuConditionName Like '%" & cmbCategory.Text & "%' and MenuConditionName Like '%" & cmbCalories.Text & "%'"
        Conditions(4) = "Where MenuConditionName Like '%" & cmbDays.Text & "%'"
        Conditions(5) = "Where MenuConditionName Like '%" & cmbCategory.Text & "%' and MenuConditionName Like '%" & cmbDays.Text & "%'"
        Conditions(6) = "Where MenuConditionName Like '%" & cmbCalories.Text & "%' and MenuConditionName Like '%" & cmbDays.Text & "%'"
        Conditions(7) = "Where MenuConditionName Like '%" & cmbCategory.Text & "%' and MenuConditionName Like '%" & cmbCalories.Text & "%' and MenuConditionName Like '%" & cmbDays.Text & "%'"

we can then use code like this

        Dim selections As Integer = 0
        If cmbCategory.Text <> "(No Selection)" Then
            selections += 1
        End If

        If cmbCalories.Text <> "(No Selection)" Then
            selections += 2
        End If

        If cmbDays.Text <> "(No Selection)" Then
            selections += 4
        End If

to discover which to use.  If we then say

        Dim SQL As String = "SELECT MenuReport FROM MenuCondition " & Conditions(selections)

that will give you the right SQL statement for whichever combination it is.

Let me stress that that is not the only approach.  Indeed, it is not the approach I have been trying to guide you towards.  But, for this setting, it's simple and straightforward and (I think) you should be able to see how it works.

Roger

Roger,
there was no condition 8 where there is "no selection in any combo'
This is the additon that I have made, but this is what happens:
1) I select a category, which works properly
2) select a calorie in additon to category, which works properly
3) select a day in addition to category and calorie, which works properly
4) then I deselect day, which works properly
5) deselect calorie, which works properly
6) deselct category, HERE THE DATA GRID IS CLEARED
7) so now everything is set to "no selection" and when I go to select Day OR Calorie, THE CODE BREAKS AT THIS LINE:
Dim SQL As String = "SELECT MenuReport FROM MenuCondition " & Conditions(selections)

WITH THIS ERROR:
IndexOutofRangeException was unhandled.

This is what I have added for the eight condition
Conditions(8) = "Where MenuConditionName not Like '%" & cmbCategory.Text & "%' and MenuConditionName Like '%" & cmbCalories.Text & "%' and MenuConditionName Like '%" & cmbDays.Text & "%'"

and then this BUT it does not work properly:
Dim selections As Integer = 0
        If cmbCategory.Text <> "(No Selection)" Then
            selections += 1
        Else
            selections += 8
        End If

Am I on the right path?

Indexes to arrays begin at 0.  We put nothing in Conditions(0).  It is an empty string.  If there is no selection in any category then selections will, on the code I gave, end up as 0.  So the last line will produce just "SELECT MenuReport FROM MenuCondition ".  That means it will show _all_ records.  Is that not what you want?

If you add lines of code saying

        Else
            selections += 8

then whenever that line is hit you are bound to get "IndexOutofRangeException was unhandled" when it reaches Conditions(selections).  The Conditions array has been dimensioned like this

        Dim Conditions(7) As String

It does not have an index of 8.  The highest number that can validly reference it is 7.

Roger

Roger,
thank you very much for all your great help.  Can you recommend a book that was beneficial to you?
thanks again

Sorry.  It's many years since I read a book on programming so - even if I could remember what it was, which I can't - it would be out of date and out of print.

Glad it's sorted.  Thanks for the points.

Roger

Thanks Roger,
I would give more but my max is 500.