Problem creating a querry with an embeded variable when using access datafile as connection source

When a Data Connection is established using

Microsoft SQL Server Database File (SqlClient) as a data source

the following code works perfectly

SELECT *
FROM ProdMaster
WHERE (PRODMASTER.DESCRIPTION LIKE '%' + @DESCRIPTION + '%')

The ToolStrip on the form opens an input box so you can type in a choice of DESCRIPTION and when the button is clicked the dataset returns only those values

that meet the criteria input

BUT

If you use a Data Connection established using

Microsoft Access Database File (OLE DB)

when you attempt to establish the querry above you get the following error

Error in WHERE clause near '@'.
Unable to parse query text.

Can anyone help me here

Answer this question

Problem creating a querry with an embeded variable when using access datafile as connection source

FONTANA Guillaume
pclement wrote:
OLEDB doesn't support "named" parameters. Use the example posted by Blair.
Actually, it's the OLEDB Provider for SQL Server that doesn't support them. You can use named parameters with Access without issue. I guess Jet supports them but some other Providers don't, including SQL Server.
gen

I don't know. . .

Everything I have seen says OleDb does not support named parameters and you must use in your query text.
Join

Access OLEDB provider supports named parameters

In the Query text I know you can name them after you specify the query text.

How do you name them in the command
DevGuy_
I will double check to make sure, but AFAIK OLEDB parameters are resolved by their ordinal position in the Command (at least in ADO.NET). You can use names but it would purely be to make your code more readable. The names are essentially ignored.
LDesmons
Maybe that's true as I can't say I've ever added parameters in a different order to how they appear in the SQL statement. I do recall however that I did once use the OLEDB Provider for SQL Server and tried to use parameter names in my SQL statement and it produced a run time exception. I had to change the parameter names to question marks to make it work. When I have used parameter names with Access it has never caused an issue, so there is something different there.
SamD
I believe that SqlCommand requires something like: SELECT * WHERE SomeColumn = @SomeColumn

SQL Server OLEDB Provider requires something like: SELECT * WHERE SomeColumn =

Jet OLEDB Provider can take either, and I've used each at various times when interacting with Access.
IraD

note: code may contain errors, as I am just spitting it out

dim connStr as string = [your connection string]
dim sql as string = "SELECT  * FROM    PRODMASTER " &_
                                 "WHERE  DESCRIPTION LIKE "
Dim dt as new DataTable
Using con as New OleDbConnection(connstr)
    Using da as new OleDbDataAdapter(sql, con)
        cmd.Parameters.Add("description", string.Format("%{0}%", DescriptionTextBox.Text))
        da.Fill(dt)
    End Using
End Using
Trevor L.
OLEDB doesn't support "named" parameters. Use the example posted by Blair.
Simn

Blair thank you for the input

forgive my ignorance but where do I place the code you have provided me
Crumbs
You can put it anywhere. I like overriding the OnLoad of my forms:
note: code may contain errors, as I am just spitting it out

dim connStr as string = [your connection string]
dim sql as string = "SELECT * FROM PRODMASTER " &_
"WHERE DESCRIPTION LIKE "
Dim dt as new DataTable

protected override sub OnLoad(ByVal e as EventArgs)
Using con as New OleDbConnection(connstr)
Using da as new OleDbDataAdapter(sql, con)
cmd.Parameters.Add("description", string.Format("%{0}%", DescriptionTextBox.Text))
da.Fill(dt)
End Using
End Using
MyBase.OnLoad(e)
end sub
Problem creating a querry with an embeded variable when using access datafile as connection source

Answer this question