[FIXED] How to use parameters in Visual Studio 2022 with an Access Database

Issue

I have created a database in Access 2019

I have created a basic form to display the data from the above table

I would like to filter the data to show only certain countries – like below

The where clause is hard code and so my question is how can I dynamically change the filter clause say from ‘Aus’ to ‘UK’.
a) I have tried using a parameter ‘CountryName’ as see in the Fill, GetData (CountryName), but I am unable to use the parameter in the Query Builder. How can this be done if possible?

b) Is there a way to change the Fill Query Property (CommandText) by code as I am unable to see the correct properties to use – see below

Solution

It sounds like you are creating a typed DataSet. In that case, just leave the default query as it is for each table adapter. You can then call Fill or GetData on a table adapter to get all the data in the corresponding table. If you want to be able to filter the data, add a new query with method names that reflect the filter, e.g. if you want to filter by the CountryName column then name the methods FillByCountryName and GetDataByCountryName. In the Query Builder, you have to use ? as parameter placeholders rather than names like @CountryName, e.g.

SELECT * FROM MyTable WHERE CountryName = ?

In code, you would then do something like this:

Dim countryName = "UK"

myTableAdapter.FillByCountryName(myDataSet.MyTable, countryName)

Answered By - jmcilhinney

Answer Checked By - Mildred Charles (PHPFixing Admin)