How to use parameters in Visual Studio 2022 with an Access Database-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

You can try this:

DECLARE @CountryName AS NVARCHAR(50) = 'uk'

SELECT CountryName FROM MyTable

WHERE CountryName = @CountryName

How this would translate to code:

Public Sub GetCountry()
    DefaultCatalog = "MyTable"
    Dim selectStatement = "SELECT CountyrName  FROM MyTable WHERE CountyrName = @CountryName"
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
            cmd.Parameters.AddWithValue("@CountryName", "uk")
            cn.Open()
            Dim reader = cmd.ExecuteReader()
            If reader.HasRows Then
                reader.Read()
                Console.WriteLine(reader.GetString(0))
            End If
        End Using
    End Using
End Sub