[RESOLVED] How to SEARCH a database using SQLite?

[The_Hobbyist]

I'm at the stage in my application where I am able to display the data from the database into a datagrid on the Form_Load event and populate the appropriate text boxes, combo boxes and drop down lists with that data. Now, I would like to be able to SEARCH the database using the USER INPUT into one or more of a select few of those text boxes and combo boxes as search criteria.

I have no idea how to start this process. I have no idea what to Google or try to investigate here on the forums to get me going in the right direction. What information do the Gurus here at VB Forums need from me before we can start this part of my journey? I don't know enough to even be able to know what questions I need to ask.

[The_Hobbyist]

Maybe I'll approach my question this way:

I'm using Visual Studio 2019 and VB.NET. My database is SQL.

I have a database called "The_Database". The database has 4 tables within it (ID, Name, Color & Size). My form has a datagrid called "Datagrid1" and 4 comboboxes (cmbID, cmbName, cmbColor & cmbSize). Lastly, I have a single command button (cmdSearch).

ID	Name	Color	Size
1	Name-1	Red	Small
2	Name-2	Green	Large
3	Name-3	Red	Large

When the form loads, the corresponding comboboxes have the drop down lists populated by the datagrid as well as have their text show the data from the first row in the datagrid.

Allowing the user to alter the text in any or all of the comboxes, I would like to generate a search query based on the user input after the button is clicked. Meaning, I would like to be able to generate a search based on current text within one or more any of the comboboxes.

How do I begin this process?

[jmcilhinney]

Firstly, your database is not SQL. SQL is Structured Query Language. It is the language used to query and modify almost all databases. Some such databases are SQL Server, MySQL, SQLite, PostgreSQL and there are various others, some with SQL in the name and some without. You probably mean Microsoft SQL Server, which some people shorten to MS SQL or, lazily and confusingly, just SQL. If you the actual names for things, there can be no confusion.

[The_Hobbyist]

Firstly, your database is not SQL. SQL is Structured Query Language. It is the language used to query and modify almost all databases. Some such databases are SQL Server, MySQL, SQLite, PostgreSQL and there are various others, some with SQL in the name and some without. You probably mean Microsoft SQL Server, which some people shorten to MS SQL or, lazily and confusingly, just SQL. If you the actual names for things, there can be no confusion.

I'm sorry... I did mean to state SQLite.

[wes4dbt]

I'd assume you mean a DataGridView and not a Datagrid.

Allowing the user to alter the text in any or all of the comboxes, I would like to generate a search query based on the user input after the button is clicked. Meaning, I would like to be able to generate a search based on current text within one or more any of the comboboxes.

It's not clear what you want to do. Are you saying you want to do a search on Name or Color or Size? And on any possible combination of the three? How would the search know what you want to search on?

Also, this is all going to depend on how the dgv is populated. My guess is you've already retrieved the data from the database so what you really want to do is search the datatable or bindingsource.

[The_Hobbyist]

I'd assume you mean a DataGridView and not a Datagrid.



It's not clear what you want to do. Are you saying you want to do a search on Name or Color or Size? And on any possible combination of the three? How would the search know what you want to search on?

Also, this is all going to depend on how the dgv is populated. My guess is you've already retrieved the data from the database so what you really want to do is search the datatable or bindingsource.

To start with, Yes! I did mean the DataGridView. I really have to work harder at clearing up the terminology in my head and get better at using the correct names of things! It matters.

Secondly, Yes! I am saying that I would like to do a search based on Name, Color and Size AND on any combination of the three.

If I understand the next part of your question (How would the search know what you want to search on?), I think you mean how would the search know which combobox or boxes to generate its keyword(s) from? If that is what you mean then in my mind and with the 4 possible boxes to pull a keyword from, if a combobox's text was left empty then that combobox would be omitted from the keyword generator. Does this make sense? Is there a more traditional method?

Lastly, yes! The data has already been retrieved and that DataGridView is populated with it on the form_load event. I suppose this means that I would want to search either the datatable or the bindingsource. I don't know which way to go here, though my gut tells me that I would benefit from searching the datatable. How would I know which is the better one to search?

[jmcilhinney]

I'm not sure what the actual problem is either. If you are already working with data then it's hard to believe that you can't find information on how to execute a query and retrieve the result set. Is your issue specifically the WHERE clause of the query? If you need general ADO.NET information then I suggest that you start by following the Database FAQ link in my signature below and check out some of the relevant resources on this site. If you can be more specific about what issue you're having right now, that would be helpful.

[wes4dbt]

If I understand the next part of your question (How would the search know what you want to search on?), I think you mean how would the search know which combobox or boxes to generate its keyword(s) from? If that is what you mean then in my mind and with the 4 possible boxes to pull a keyword from, if a combobox's text was left empty then that combobox would be omitted from the keyword generator. Does this make sense? Is there a more traditional method?

Are you talking about selecting a row from the dgv then use that row contents for the search?
It really doesn't make sense. If the dgv is filled with data from your ScientificData table then,

Id is unique so none of the other columns matter
You have said that the NAME is unique so none of the other columns matter
What is it that you want to do with Color and Size? Get a list of all the stones with that color and size?

As you can see I don't understand what you want to achieve. Sorry can't help.

[jmcilhinney]

I wonder whether what you're actually talking about is filtering the data locally. Are you saying that you retrieve all the data from the database and display it in a grid, then you want to use individual controls to filter that data to show only rows containing data that matches the entered/selected values? The keyword here is "filter", as opposed to "search".

If that is the case, you should populate a DataTable using a data adapter, bind that to a BindingSource and bind that to the grid. You can then filter the data in the grid by setting the Filter property of the BindingSource. Your first port of call should be the documentation for that property.

[wes4dbt]

Ah, your not talking about searching for stones in the scientificData table. You have a "Collection" table that could have multiples of the same type stone. Is that correct?

If so, I'm not sure why you want to select a row from a dgv to do your search. Why not just three comboboxes, name, size, color?

[The_Hobbyist]

Are you talking about selecting a row from the dgv then use that row contents for the search?
It really doesn't make sense. If the dgv is filled with data from your ScientificData table then,

Id is unique so none of the other columns matter
You have said that the NAME is unique so none of the other columns matter
What is it that you want to do with Color and Size? Get a list of all the stones with that color and size?

As you can see I don't understand what you want to achieve. Sorry can't help.

No need at all to apologize. You've been a tremendous help this far and it has been greatly appreciated! Any confusion here is obviously generated from my end and my lack of experience and understanding (I'm working on that though). Reading and research do help me understand but I often get lost and my comprehension slides - its exponential... ....I find that my best results are yielded from sample code from which I can tinker with and learn how it works with a hands on approach. Then I adjust the code to work for my needs and in my environment. Anyway...Enough about my lack luster comprehensive reading ability...

In any event, you referenced the table "ScientificData" in your reply. Yes, my current project contains such a table however, the scenario that I posted doesn't relate to that database or it's tables at all but rather an imaginary one. I thought it would be easier (for me) to get some feedback on a simple database table rather than the seemingly larger and more daunting database like the one which is currently implemented in my application. It would seem as though I was wrong. My ignorance is frustrating, I have no doubt and for that I apologize.

I think that I am starting to see the light though...Maybe? I was under the impression that every time I wanted to query the "data" that I had to do so by opening the database file and handling the information within it. From what I've come to surmise now is that once the DataGridView has been populated with the data from the database then all one must do is filter the databindings for results that match the keywords that are taken from the comboboxes I choose to use for search criteria? Then display the positive results back into the datagridview. From there I can cycle through the datagridview and re-populate my form objects with the new, corresponding data?

Am I on the right track here?

I wonder whether what you're actually talking about is filtering the data locally. Are you saying that you retrieve all the data from the database and display it in a grid, then you want to use individual controls to filter that data to show only rows containing data that matches the entered/selected values? The keyword here is "filter", as opposed to "search".

If that is the case, you should populate a DataTable using a data adapter, bind that to a BindingSource and bind that to the grid. You can then filter the data in the grid by setting the Filter property of the BindingSource. Your first port of call should be the documentation for that property.

I think you might be on to something... From your description, yes, what I want to do is filter the data. My dgv has already been populated with the entire database contents on form_load. What I want to do now I suppose is filter out any data that doesn't match the filters (keywords) I choose to use. Those filters are to be generated by text from within one or more comboboxes.

If my suspicions are correct and filtering is the "bees knees" here, then how do I get started with the filtering process?

[wes4dbt]

You should read the doc's as jmc suggested. Here's an example of how to filter a bindingsource,

Me.scienceBindingsource.Filter = "Name='" & NameTextBox.Text & "' AND Density='" & Me.DensityTextBox.Text & "'"

Name and Density are the underlying DataTable column names.

[wes4dbt]

So, you said

I have a database called "The_Database". The database has 4 tables within it (ID, Name, Color & Size).

Or does The_Database have ONE table with FOUR columns. Big difference. lol

[The_Hobbyist]

So, you said



Or does The_Database have ONE table with FOUR columns. Big difference. lol

OMG!!! Yes!! The_Database has 1 table with 4 fields (columns). Its getting late and I've literally been sitting here for about 9 hours today doing this. I'm getting tired...lol

Anyway, your filter code example is yielding results for me! I'm making something happen and its positive. It's not working 100% yet but its doing something good for me!

I can filter the dgv down to the keyword in the combobox. There is work to do still but I need some time to digest whats going on and tinker with it. Once I get the dgv to only show the filtered results, the dropdown list in the combobox is cleared. That is to be expected since it is populated by a field from the dgv (which is now only showing the filtered results which obviously match the text in the combobox). I have yet to form a plan to repopulate the dgv with all of the original data. Likely will try to do this after the combobox text is cleared.

I'll report back with my results eventually.

Thank you! Both of you!

[jmcilhinney]

Here's what I would do. Add a Panel to your form and put all the filter controls in that. For each of those controls, set the Tag property to the name of the column that it is supposed to filter. You can then do this:

Dim criteria = myPanel.Controls.
                       Cast(Of Control)().
                       Where(Function(c) Not String.IsNullOrWhiteSpace(c.Text)).
                       Select(Function(c) $"{c.Tag} = '{c.Text}'")
Dim filter = String.Join(" AND ", criteria)
 
myBindingSource.Filter = filter

That first expression is a LINQ query, using function syntax. What it does is get an enumerable list of Strings from the filter controls that do not have a blank Text property, each of the form "SomeColumn = 'some value'". The call to String.Join will combine those criteria with a Boolean " AND " operator between each pair, e.g. "Column1 = 'value1' AND Column2 = 'value2'". If no filter controls are populated, that final filter expression will be an empty String and that will cause the BindingSource to display all the data.

Note that, for those who prefer query syntax, that LINQ query could also look like this:

Dim criteria = From c As Control In myPanel.Controls
               Where Not String.IsNullOrWhiteSpace(c.Text)
               Select $"{c.Tag} = '{c.Text}'"

[jmcilhinney]

If you didn't want to use a Panel, you could just explicitly create an array, e.g.

Dim filterControls = {TextBox1, ComboBox1}

You would then use that instead of myPanel.Controls and you wouldn't need a cast either. If you did want to use a Panel but you had Labels for the controls and wanted to exclude them, you could do this:

Dim filterControls = myPanel.Controls.Cast(Of Control)().Except(myPanel.Controls.OfType(Of Label)())

That will give you an enumerable list of all the controls in that Panel except the Labels.

[wes4dbt]

Dim criteria = myPanel.Controls.
                       Cast(Of Control)().
                       Where(Function(c) Not String.IsNullOrWhiteSpace(c.Text)).
                       Select(Function(c) $"{c.Tag} = '{c.Text}'")
Dim filter = String.Join(" AND ", criteria)
 
myBindingSource.Filter = filter

That's a really nice piece of code. Very clever use of the Tag property.

[The_Hobbyist]

I don't know.... Somewhere along the line I have gotten myself terribly confused! My windows form has a DataViewGrid on it which is populated by a DataTable but I just can't seem to untwist in my mind how to FILTER the data within the DGV upon the user's request. I've read and looked at examples along the way and it seems fairly simple: SELECT, FROM using WITH & AND's but somewhere along the path I'm falling short. I think the more I read the more bunged up I get at this point.

I'll try to share my work as best I can while I try to explain my understanding of it. I'll start from the top and share my layout and try to explain how I populate my DGV. Please bare with me:

I have a Database file called "Database.db". The database is constructed of 3 tables with the master table called "ScientificData" and the other two tables are lookup tables called "ChemistryData" and "InventoryData".

The master table ("ScientificData") essentially looks like this:

ID	Name	HardnessA	HardnessB	DensityA	DensityB	MagneticNo	MagneticYes	ChemistryID
1	Amazonite	6	6.5	2.6	2.65	1	0	1
2	Amethyst	6.9	6.5	2.56	2.58	1	0	2
3	Apatite	5	5	3.16	3.23	1	0	3
4	Brecciated Jasper	6.9	7	2.65	2.65	1	0	2

One of the lookup tables ("ChemistryData") looks like this:

ID	ChemicalFormula
1	KAlSi₃O₈
2	SiO₂
3	Ca5(PO4)3(Cl/F/OH)
4	SiO₂

- The 2nd lookup table (InventoryData) is not currently being used as of yet however, the intention is for it to be a place to catalog personal inventory further down in the application. In the meantime, the bulk of the database is acting as an encyclopedia for all intents and purposes. Regardless, this 2nd lookup table looks like this:

ID	Name	DateAcquired	Vendor	PhotoName
1	Brecciated Jasper	09/17/2022	Jacob's Trading Post	Brecciated-Jasper-001.jpg
2	Amethyst	09/22/2022	The Preferred Perch	Amethyst-001.jpg
3	Amethyst	09/27/2022	Jacob's Trading Post	Amethyst-002.jpg
4	Amazonite	09/22/2022	The Preferred Perch	Amazonite-001.jpg

I have done the following in the Public Class:

1. Created a File Path string called Connection:

Private Connection As String = ("Data Source=" & Application.StartupPath & "\Database.db;" & "Version=3;New=False;Compressed=True;")

It is my understanding that I do this as a means to call upon this file path with greater ease. It is cleaner, neater and easier to call "Connection" than it is "Data Source=" & Application.StartupPath & "\Database.db;" & "Version=3;New=False;Compressed=True;" when I need to.

2. Created a Data Adapter called ScienceAdapter:

Private WithEvents ScienceAdapter As New SQLiteDataAdapter("SELECT ID, Name, HardnessA, HardnessB, DensityA, DensityB, MagneticNo, MagneticYes FROM ScientificData", Connection)

It is my understanding that the adapter is the communication link to the database file and allows for functions such as SELECT, INSERT, UPDATE and DELETE.

3. Created a Data Table called ScienceDataTable:

Private ScienceDataTable As New DataTable

It is my understanding that by doing this I am creating a new, internal, unbound data table which will hold my chosen data in "thin air" until I call upon it.

4. Created a data binding source called ScienceBindingSource:

Private ScienceBindingSource As New BindingSource

It is my understanding that I create a data binding source so that I can then bind specific data from the database to objects on the windows form. For example, I have the data bound to a combobox on my form which displays all of the Names from the DGV into it's drop down list.


I do the following on the FORM LOAD event:

1. Call upon the data adapter (ScienceAdapter) to populate the data table (ScienceDataTable):

ScienceAdapter.Fill(ScienceDataTable)

I believe I do this as a means of fetching the data from the database file and then putting it into a state where I can organize it and put it into a state that allows it to be manipulated.

2. Define the source of data for the data binding source:

ScienceBindingSource.DataSource = ScienceDataTable

It is my understanding that by doing this, I have bound the data set within the ScienceDataTable to the ScienceBindingSource.

3. Populate the DataGridView (DGV) with the data from the :

Me.DGV.DataSource = ScienceBindingSource

WHAT I WANT TO HAPPEN FROM HERE:
1. Enter text into one of 6 text boxes and select one of two radio buttons and then filter the data displayed in the DGV based on these entries (any number combination of text boxes/radio buttons used). These text boxes are reflective of the FIELD names in the master table (Name, HardnessA, HardnessB, DensityA, DensityB, ChemistryID) and the radio buttons reflect two more FIELDS from the same master table (MagneticNo, MagenticYes).

2. Once the filter data has been entered by the user, allow the user to submit their filter through the use of a command button "Search".

3. Re-populate the DGV with only the filtered data.

4. If ALL of the filtered criteria is left blank and the search button is executed, I would like for the entire database to once again re-populate the DGV.

To re-cap, I'll show my current code all together:

Imports System.Data.SQLite


Public Class frmMain

     Private Connection As String = ("Data Source=" & Application.StartupPath & "\The_Database.db;" & "Version=3;New=False;Compressed=True;")
     Private WithEvents ScienceAdapter As New SQLiteDataAdapter("SELECT ID, Name, HardnessA, HardnessB, DensityA, DensityB, MagneticNo, MagneticYes, ChemistryID FROM ScientificData", Connection)
     Private WithEvents ChemistryAdapter As New SQLiteDataAdapter("SELECT ID, ChemicalFormula FROM ChemistryData", Connection)
     Private WithEvents InventoryAdapter As New SQLiteDataAdapter("SELECT ID, Name, DateAcquired, Cost, Vendor, PhotoName FROM InventoryData", Connection)


     Private ScienceDataTable As New DataTable

     Private ScienceBindingSource As New BindingSource

     Dim SQLite_Connect As SQLite.SQLiteConnection
     Dim SQLite_Command As SQLite.SQLiteCommand


     Private Sub frmMain_Load(Sender As Object, e As EventArgs) Handles Me.Load

          ScienceAdapter.Fill(ScienceDataTable)
          ChemistryAdapter.Fill(ChemistryDataTable)
          InventoryAdapter.Fill(InventoryDataTable)

          ScienceBindingSource.DataSource = ScienceDataTable
          ChemistryBindingSource.DataSource = ChemistryDataTable

          Me.DGV.DataSource = ScienceBindingSource

          SetUpBindings()

     End Sub


     Private Sub SetUpBindings()

        Me.cmbName.DataSource = ScienceBindingSource
        Me.cmbName.DisplayMember = "Name"
        Me.cmbName.DataBindings.Add("Text", ScienceDataTable, "Name")
        Me.txtHardnessA.DataBindings.Add("Text", ScienceBindingSource, "HardnessA")
        Me.txtHardnessB.DataBindings.Add("Text", ScienceBindingSource, "HardnessB")
        Me.txtDensityA.DataBindings.Add("Text", ScienceBindingSource, "DensityA")
        Me.txtDensityB.DataBindings.Add("Text", ScienceBindingSource, "DensityB")
        Me.rbNo.DataBindings.Add("Checked", ScienceBindingSource, "MagneticNo")
        Me.rbYes.DataBindings.Add("Checked", ScienceBindingSource, "MagneticYes")
        Me.cmbChemicalFormula.DataSource = ScienceBindingSource
        Me.cmbChemicalFormula.DisplayMember = "ChemistryID"
        Me.cmbChemicalFormula.DataBindings.Add("Text", ScienceDataTable, "ChemistryID")

     End Sub

End Class

I know this is a lot to sift through and deal with. I'm lost and very new to all of this, so in an effort to be thorough and share my understanding what I am doing (because I'm not truly sure), I felt it best to share most everything. Please, be patient with me - I am doing my best.

[wes4dbt]

I don't see any code for filtering. What happens when you try? I'd suggest start by filtering with just one or two values.

[wes4dbt]

Also, this doesn't look right,

        Me.cmbChemicalFormula.DataSource = ScienceBindingSource
        Me.cmbChemicalFormula.DisplayMember = "ChemistryID"
        Me.cmbChemicalFormula.DataBindings.Add("Text", ScienceDataTable, "Chemi

I don't see the need for ScienceBingingSource,

        Me.cmbChemicalFormula.DataSource = ChemistryDataTable
Me.cmbChemicalFormula.DisplayMember = "ChemicalFormula"
Me.cmbChemicalFormula.ValueMember = "ID"
Me.cmbChemicalFormula.SelectedValue = "ID"
        Me.cmbChemicalFormula.DataBindings.Add("SelectedValue", ScienceBindingSource, "ChemistryId

[The_Hobbyist]

You're right. I went and deleted it all with the intention of starting over again. I'll report back, showing some effort in time.

[wes4dbt]

You're right. I went and deleted it all with the intention of starting over again. I'll report back, showing some effort in time.

I don't doubt your effort. But we can't diagnose without seeing what your trying to do. jmc did provide a method, I'm not sure why that wont work for you.

[The_Hobbyist]

Also, this doesn't look right,

        Me.cmbChemicalFormula.DataSource = ScienceBindingSource
        Me.cmbChemicalFormula.DisplayMember = "ChemistryID"
        Me.cmbChemicalFormula.DataBindings.Add("Text", ScienceDataTable, "Chemi

I don't see the need for ScienceBingingSource,

        
        Me.cmbChemicalFormula.DataSource = ChemistryDataTable
        Me.cmbChemicalFormula.DisplayMember = "ChemicalFormula"
        Me.cmbChemicalFormula.ValueMember = "ID"
        Me.cmbChemicalFormula.SelectedValue = "ID"
        Me.cmbChemicalFormula.DataBindings.Add("SelectedValue", ScienceBindingSource, "ChemistryId

Maybe I'm not doing this correctly? In case it has been overlooked, I'll point out that the ScientificDataTable holds the data from the database's master table while the ChemistryDataTable is holding the data from one of two lookup tables. The DGV displays the ScientificDataTable contents. The ScientificDataTable has a field within it which directly corrosponds to the ChemistryDataTable using the unique ID number as the lookup field. I am attempting to display components of the DGV into specific textboxes, comboboxes and radio buttons on the windows form. Unfortunately, I realize that I am not calling the ChemistryData properly and instead of showing the data (chemical formula for a specific rock), I'm showing the ID used to call that record by the ScientificDataTable). Its just a hunch but I suspect this is why this doesn't look correct to you? I could be very wrong! I've been wrong about most everything else up to this point. Ha! ....In any event, I'm less concerned about getting this correct at the moment than I am with being able to properly filter the DGV.

I'll continue to look at JMC's fix and attempt to make that work. I'll report back with something, in time. The household is a little crazy here at the moment.

Thank you again!

[wes4dbt]

Did you modify your code like I showed you? If so, it should do exactly what you want. If it doesn't, what happened.

[The_Hobbyist]

Did you modify your code like I showed you? If so, it should do exactly what you want. If it doesn't, what happened.

I'm getting an error: System.FormatException: 'Input string was not in a correct format.' @ this line: Me.cmbChemicalFormula.SelectedValue = "ID"

The database table had the field type set to INTEGER though and by changing it to NUMERIC, this error was fixed. Thank you.

[wes4dbt]

NOTE: I managed to figure that one out though. The database table had the field type set to INTEGER. Changing it to NUMERIC fixe this problem. Thank you.

That doesn't make any sense. There is nothing wrong with ID being an Integer. It should an Integer, especially if it's the Primary Key and AutoIncrement. That what I use for my testing that code and it worked fine.

[The_Hobbyist]

I'm sorry, I did say that I changed the field type from Integer to Numeric. Upon 2nd glance, I changed it to TEXT. I realize that what you're saying is that it shouldn't matter and that integer is the correct type for that field. I'm at a loss here. My screen shots will show how I changed the field type, the error that is caused and then how I changed it back to TEXT and how it works again.

Attachment 186541

Attachment 186542

Attachment 186543

[wes4dbt]

Those attachments don't work. You should just post your code. Especially when your getting an error.

[wes4dbt]

the only thing I can think of is the ChemistryId field in the ScientificData table is not an Integer data type. Make it is also type Integer.

[The_Hobbyist]

Those attachments don't work. You should just post your code. Especially when your getting an error.

Understood. I would like to keep focused on one issue at a time though. I'm having trouble staying focused on the filtering problem. For whatever reason your code seems to work when the field data type is changed from INTEGER to TEXT. Perhaps the type shouldn't matter but for me it seems to at this time. I'm happy to come back to this issue at a later time but for now, filtering is my main concern.

With that said and getting back to my filtering issue. No doubt my lack of understanding is the main hurdle here. I've tinkered with JMC's code (if I understand it correctly, which is likely not the case) and have tried to adapt it to my application. What happens when I run this code is that the DataGridView goes blank. I'll show two different methods that I've tried and both yield the same result. I don't think I'm calling for the DGV to be re-populated correctly.... I don't know.

In any event. its been a long day and I'm logging off for the night. Thank you for your help all the same.

Method #1:

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim filterControls = {Me.txtName.Text, Me.txtHardnessA.Text}
        Dim criteria = filterControls
        Dim filter = String.Join(" AND ", criteria)

        ScienceBindingSource.Filter = filter

        frmMain.DGV_ScienceData.DataSource = ScienceBindingSource.Filter

End Sub

Method #2:

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        ScienceBindingSource.Filter = String.Format("Name LIKE '%{0}%'", Me.txtName.Text)

        frmMain.DGV_ScienceData.DataSource = ScienceBindingSource.Filter

End Sub

[The_Hobbyist]

the only thing I can think of is the ChemistryId field in the ScientificData table is not an Integer data type. Make it is also type Integer.

It is set to INTEGER. It has never changed.

[wes4dbt]

It is set to INTEGER. It has never changed.

Well if you don't want to post the code then I can't help.


As for the filter, check the value of "filter", you'll see there is no "=" signs.

It should look something like,

"Name='someValue1' AND Density='someValue2'"

Get rid of this,

frmMain.DGV_ScienceData.DataSource = ScienceBindingSource.Filter

You just need to set the bindingsource filter property,

ScienceBindingSource.Filter = filter

EDIT:

This throws the error you were getting,

        Me.FormulaComboBox.DataSource = chemistryDataTable
        Me.FormulaComboBox.DisplayMember = "Chemicalformula"
        Me.FormulaComboBox.ValueMember = "ID"
        Me.FormulaComboBox.SelectedValue = "ID"

Look at my example,

        Me.FormulaComboBox.DisplayMember = "Chemicalformula"
        Me.FormulaComboBox.ValueMember = "ID"
        Me.FormulaComboBox.SelectedValue = "ID"
        Me.FormulaComboBox.DataSource = chemistryDataTable

The order is important. That's why you always post the relevant code.

[The_Hobbyist]

As for the filter, check the value of "filter", you'll see there is no "=" signs.

It should look something like,

"Name='someValue1' AND Density='someValue2'"

I can see where you're going with this. I can't quite seem to figure out how to use the text from a textbox as the "values" though. Something in my syntax is incorrect (I think). I get an "End of Statement Expected" error:

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim filterControls = "Name=" Me.txtName.Text And "HardnessA= " Me.txtHardnessA.Text  '<--- Error HERE
        Dim criteria = filterControls
        Dim filter = String.Join(" AND ", criteria)

        ScienceBindingSource.Filter = filter

        MessageBox.Show(filter)

    End Sub

This throws the error you were getting,

        Me.FormulaComboBox.DataSource = chemistryDataTable
        Me.FormulaComboBox.DisplayMember = "Chemicalformula"
        Me.FormulaComboBox.ValueMember = "ID"
        Me.FormulaComboBox.SelectedValue = "ID"

Look at my example,

        Me.FormulaComboBox.DisplayMember = "Chemicalformula"
        Me.FormulaComboBox.ValueMember = "ID"
        Me.FormulaComboBox.SelectedValue = "ID"
        Me.FormulaComboBox.DataSource = chemistryDataTable

The order is important. That's why you always post the relevant code.

Yes! You are correct. I altered my code to represent what you have shown here and this issue is resolved. It now works flawlessly within my application with the auto ID field set to INTEGER.

Please, I don't mean to come across as lazy or unwilling to share my code. The truth is I often don't understand the code well enough to know which parts I should be sharing. Furthermore, I typically boil my code down to minimize it. For example, my intention is to filter the DGV using 8 or 10 different text boxes or radio buttons but I shrink the code down to show only 2. In my mind this SHOULD make for a shorter, more simple question and in turn receive a less complicated answer (easier for me to follow). Perhaps I should stop doing that.

In any event, working with the FILTER aspect of the code again - I am getting an error as mentioned above. I have pointed to the line which is causing this error.

I assume that there is a way to use the text from a textbox to generate the filter criteria? This is what I am trying to achieve in my code above. I'm stuck somewhere though...

[The_Hobbyist]

It should be noted that I have also attempted this method but get the same error:

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim SearchName As String = Me.txtName.Text
        Dim SearchHardnessA As String = Me.txtHardnessA.Text

        Dim filterControls = "Name=" SearchName And "HardnessA= " SearchHardnessA  '<--- ERROR HERE
        Dim criteria = filterControls
        Dim filter = String.Join(" AND ", criteria)

        ScienceBindingSource.Filter = filter

        MessageBox.Show(filter)

    End Sub

[jmcilhinney]

It should be noted that I have also attempted this method but get the same error:

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim SearchName As String = Me.txtName.Text
        Dim SearchHardnessA As String = Me.txtHardnessA.Text

        Dim filterControls = "Name=" SearchName And "HardnessA= " SearchHardnessA  '<--- ERROR HERE
        Dim criteria = filterControls
        Dim filter = String.Join(" AND ", criteria)

        ScienceBindingSource.Filter = filter

        MessageBox.Show(filter)

    End Sub

Maybe you should go back and look at post #12, where you've already been shown one way to do this.

When you get that fixed, it's worth noting that the next two lines are nonsense.

[The_Hobbyist]

Maybe you should go back and look at post #12, where you've already been shown one way to do this.

When you get that fixed, it's worth noting that the next two lines are nonsense.

Thank you. I appreciate that. You're correct, I overlooked the previous post (#12). Regarding the following 2 lines that were rubbish - I felt that I was following your example from above. I must have misunderstood it. I realize that you were referencing a panel but then you shared a way to not use a panel. I opted to go without a panel and I obviously got that all mixed up. There is a lot going on here and I should have been able to catch this one myself, you're right to point that out. Again, thank you. In any event, I have some filtering working but not quite where I want it just yet. With all your help, I got rid of the error with the code below though:

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim SearchName As String = Me.txtName.Text
        Dim SearchHardnessA As String = Me.txtHardnessA.Text

        Dim FilterControls = "Name='" & SearchName & "' AND HardnessA='" & SearchHardnessA & "'"

        ScienceAdapter.Fill(ScienceDataTable)
        ScienceBindingSource.DataSource = ScienceDataTable
        ScienceBindingSource.Filter = FilterControls
        frmMain.DGV_ScienceData.DataSource = ScienceBindingSource

    End Sub

My issue is now that the filter wants to use BOTH of the filter controls (Name & Hardness) when I wish for it to use one, the other or both. I suspect the issue you here is the call for 'AND' in this line: (Dim FilterControls = "Name='" & SearchName & "' AND HardnessA='" & SearchHardnessA & "'"). I'll spend some time with this in a little while to see what I can learn about this.

Thank you. I'll be back.

[wes4dbt]

My issue is now that the filter wants to use BOTH of the filter controls (Name & Hardness) when I wish for it to use one, the other or both. I suspect the issue you here is the call for 'AND' in this line: (Dim FilterControls = "Name='" & SearchName & "' AND HardnessA='" & SearchHardnessA & "'"). I'll spend some time with this in a little while to see what I can learn about this.

Then you would use "OR" instead of "AND".

[The_Hobbyist]

Then you would use "OR" instead of "AND".

Yes. That works. It will return filtered results that match any of the filter controls entered. Now, if I want to return only the filtered results that matched ALL of the filter controls entered (or any combination there of), where might I begin? Is a filtering loop required for this?

Here is where I am at right now:

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        Dim SearchName As String = Me.txtName.Text
        Dim SearchHardnessA As String = Me.txtHardnessA.Text
        Dim SearchHardnessB As String = Me.txtHardnessB.Text
        Dim SearchDensityA As String = Me.txtDensityA.Text
        Dim SearchDensityB As String = Me.txtDensityB.Text

        frmMain.DGV_ScienceData.DataBindings.Clear()
        frmMain.DGV_ScienceData.Refresh()

        Dim FilterControls = "Name='" & SearchName & "' OR HardnessA='" & SearchHardnessA & "' OR HardnessB='" & SearchHardnessB & "' OR DensityA='" & SearchDensityA & "' OR DensityB='" & SearchDensityB & "'"

        ScienceAdapter.Fill(ScienceDataTable)
        ScienceBindingSource.DataSource = ScienceDataTable
        ScienceBindingSource.Filter = FilterControls
        frmMain.DGV_ScienceData.DataSource = ScienceBindingSource

        Me.Close()  '<--- I close the form after filtering because I am using a 2nd form to allow the user to enter the filter controls into.

        MessageBox.Show(FilterControls)

    End Sub

Thank you. I feel that the recent progress is helping lift my spirits!

[wes4dbt]

You already know how to use the "AND" in a search so you answered that question.

If you can't use one of the methods that jmc provided for creating your search criteria then you will have to manually check each control to see if it should be included in the criteria. You could do that using the forms Controls array.

Your RadioButton controls will be more difficult to work with because it doesn't return text. But here is an example of how you can work with them,

Me.scienceBindingsource.Filter = "Name='" & NameTextBox.Text & "' AND MagneticYes=" & CStr(IIf(CInt(Me.MagneticYesRadioButton.Checked) = 0, 0, 1))

As for using a second form for the search, this kind of stuff is messy and prone to failures. Using default value form names can be an issue.

frmMain.DGV_ScienceData.DataSource = ScienceBindingSource

I believe jmc has link in his signature to a tutorial on how to pass data between forms. Or just Google it.

It would be much simpler to just pop up a panel with the search controls on it. All your data and bindings are right there. When your done searching then reset the panel Visible property back to False. either Form or Panel will work but the way your doing it unnecessarily complexed.

[The_Hobbyist]

It would be much simpler to just pop up a panel with the search controls on it. All your data and bindings are right there. When your done searching then reset the panel Visible property back to False. either Form or Panel will work but the way your doing it unnecessarily complexed.

OK....Despite feeling like I've managed to make some progress with this, I'll trust you guys. I don't want things to be overly complicated or messy when it comes down to my code. You folks obviously know what you're doing! Who am I to argue?!!!

So, I'll attempt to go about using an Panel. I don't even know what a panel is or what it does other than I can drop one onto my form from the toolbox. I'll do some reading and see where this takes me.

I appreciate your patience people. I fully recognize how antagonizing my lack of understanding and immense confusion is. For what it's worth, I appreciate your patience.

I'll report back after I spend some time investigating panels and undoubtedly have some stupid question to pose...Bare with me.