Oledbparameters

  • Thread starter Thread starter Paul Gray via DotNetMonster.com
  • Start date Start date
P

Paul Gray via DotNetMonster.com

Hi

I am having touble determining the correct syntax for my SQL query using
the OleDbCommand parameters and the 'like' operator with the 'where' clause
.. I would like to use the wildcard % at either end of my parameter, but no
matter what I try, nothing seems to work, on the surface the query couldn't
be simpler and works perfectly with text outside of this context. Can
anyone please advise?

Note: txtFirstname.Text refers to the text entered into a textbox

void btn_click(Object sender , EventArgs e)

{


OleDbConnection conAuthors = new OleDbConnection(
"PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=D:\\Mydatabase\\Code\\
Latest28092004\\pg.MDB" );

conAuthors.Open();

string OleString = "Select Code, Description, Retail from Pricelist0804
where Description like '%?%'";

OleDbCommand cmdSelectAuthors = new OleDbCommand( OleString, conAuthors );

cmdSelectAuthors.Parameters.Add ("?", txtFirstname.Text);


dgrdMenu.DataSource = cmdSelectAuthors.ExecuteReader();
dgrdMenu.DataBind();

conAuthors.Close();
 
Hi Paul,

AFAIK, in Access you should use wildcard * rather than % (I'm not sure what
is that in Access 2003).

HTH

Elton Wang
(e-mail address removed)
 
Hi

Thank you, but I was wondering if you could tell me the exact sequence if
possible, as I have tried a number of variations without success, e.g

string OleString = "Select Code, Description, Retail from Pricelist0804
where Description like ' @? ' ";

OleDbCommand cmdSelectAuthors = new OleDbCommand( OleString,
conAuthors );

cmdSelectAuthors.Parameters.Add ("@%?%", txtFirstname.Text);
 
Thank you - I have tried this but there is something else amiss, e.g the
following doesn't work

-------------------------------

string OleString = "Select Code, Description, Retail from Pricelist0804
where Description like ' @*?* ' ";

OleDbCommand cmdSelectAuthors = new OleDbCommand( OleString,
conAuthors );

cmdSelectAuthors.Parameters.Add ("@?", txtFirstname.Text);

--------------------------------

nor does the following and a number of other similar variations

------------------------------------------

string OleString = "Select Code, Description, Retail from Pricelist0804
where Description like '*@?*' ";

OleDbCommand cmdSelectAuthors = new OleDbCommand( OleString,
conAuthors );

cmdSelectAuthors.Parameters.Add ("@?", txtFirstname.Text);
 
Access 2003 with the OleDbConnection/OleDbCommand seems to be expecting the
SQL Server syntax using % as the wild card despite the documentation stating
that * is the wild card. To concatenate the parameter value you'd use the
following syntax. If your version of access requires the *, simply replace
the % signs in the query below and all should be fine.

OleDbCommand cmd = new OleDbComnand("SELECT Code, Description, Retail FROM
Pricelist0804 WHERE Description LIKE '%' + ? + '%'", conAuthors);
cmd.Parameters.Add(new OleDbParameter("p1", OleDbType.VarChar, 15)).Value =
txtFirstname.Text;

On a side note, Access 2003 and the OleDb classes seem to accept the SQL
Server syntax with named parameters (@param). The following also worked...

OleDbCommand cmd = new OleDbComnand("SELECT Code, Description, Retail FROM
Pricelist0804 WHERE Description LIKE '%' + @name + '%'", conAuthors);
cmd.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar, 15)).Value
= txtFirstname.Text;

HTH
 
Dave,

Many thanks - that did the trick

I am using Access 2000 and I checked to see if it would work with named
parameters as you indicated it did with 2003, and strangely enough despite
all indications to the contrary it did.

Anyway, appreciate the help, what should have been relatively
straightforward had become a bit of a headache.

Thanks again

P.S Site looks good - will have a closer peruse through it over the coming
days
 
Back
Top