Oledbparameters

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();
Message posted via http://www.webservertalk.comChange it so that the "%" is in your parameter value, not in the original
sql.
-Brock
DevelopMentor
http://staff.develop.com/ballen

> 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
elton_wang@.hotmail.com
"Paul Gray via webservertalk.com" wrote:

> Hi
> I am having touble determining the correct syntax for my SQL query using
> the OleDbCommand parameters and the 'like' operator with the 'where' claus
e
> .. I would like to use the wildcard % at either end of my parameter, but n
o
> 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();
> --
> Message posted via http://www.webservertalk.com
>
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);
Message posted via http://www.webservertalk.com
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);
---
Any suggestions gratefully received
Message posted via http://www.webservertalk.com
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 Fancher
http://davefancher.blogspot.com
"Paul Gray via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:5b695dc1a75849bcab4ece1a5e7a56af@.Do
webservertalk.com...
> 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);
> ---
> Any suggestions gratefully received
> --
> Message posted via http://www.webservertalk.com
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
Message posted via http://www.webservertalk.com