Q: what is problem here

[JIM.H.]

Hello,

I am trying to send Where clause as a parameter. Here is the detail:

Select statment:
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT * " +
" FROM myTable " +
" WHERE @WClause ";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@WClause",
System.Data.SqlDbType.NVarChar, 255, "WClause"));
In the code, I do:
string wClause = "(MyStr LIKE 'zyx%')";
sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
sqlDataAdapter1.Fill(dataSet11);

I got this error: Line 1: Incorrect syntax near '@WClause'.

I tried this in the query analyzer, it works fine:
select *
from myTable
where (MyStr LIKE 'zyx%')

What is problem?

[Marina]

You can't do that. Parameters are meant to send literal values (strings,
integers, dates, etc). Not executable statements (or pieces of them).

You can make the query: "Select * From MyTable WHERE MyCol like @val"

Then add a variable called "@val", and set its value to whatever you need it
to be.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:68**********************************@microsof t.com...

Hello,

I am trying to send Where clause as a parameter. Here is the detail:

Select statment:
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT * " +
" FROM myTable " +
" WHERE @WClause ";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@WClause",
System.Data.SqlDbType.NVarChar, 255, "WClause"));
In the code, I do:
string wClause = "(MyStr LIKE 'zyx%')";
sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
sqlDataAdapter1.Fill(dataSet11);

I got this error: Line 1: Incorrect syntax near '@WClause'.

I tried this in the query analyzer, it works fine:
select *
from myTable
where (MyStr LIKE 'zyx%')

What is problem?

[JIM.H.]

The problem is with that: I need to add or remove constraints based on the
user input. For string fields I use %. However, for example "Where (myId =
@ID) " if user did not give @ID, that should not have any impact on the query
result. How can I do this?

"Marina" wrote:

You can't do that. Parameters are meant to send literal values (strings,
integers, dates, etc). Not executable statements (or pieces of them).

You can make the query: "Select * From MyTable WHERE MyCol like @val"

Then add a variable called "@val", and set its value to whatever you need it
to be.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:68**********************************@microsof t.com...

Hello,

I am trying to send Where clause as a parameter. Here is the detail:

Select statment:
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT * " +
" FROM myTable " +
" WHERE @WClause ";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@WClause",
System.Data.SqlDbType.NVarChar, 255, "WClause"));
In the code, I do:
string wClause = "(MyStr LIKE 'zyx%')";
sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
sqlDataAdapter1.Fill(dataSet11);

I got this error: Line 1: Incorrect syntax near '@WClause'.

I tried this in the query analyzer, it works fine:
select *
from myTable
where (MyStr LIKE 'zyx%')

What is problem?

[Marina]

You can adjust your query based on input. Just leave that condition out of
the whereclause.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:8D**********************************@microsof t.com...

The problem is with that: I need to add or remove constraints based on the
user input. For string fields I use %. However, for example "Where (myId =
@ID) " if user did not give @ID, that should not have any impact on the
query
result. How can I do this?

"Marina" wrote:

You can't do that. Parameters are meant to send literal values (strings,
integers, dates, etc). Not executable statements (or pieces of them).

You can make the query: "Select * From MyTable WHERE MyCol like @val"

Then add a variable called "@val", and set its value to whatever you need
it
to be.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:68**********************************@microsof t.com...

> Hello,
>
> I am trying to send Where clause as a parameter. Here is the detail:
>
> Select statment:
> // sqlSelectCommand1
> //
> this.sqlSelectCommand1.CommandText = "SELECT * " +
> " FROM myTable " +
> " WHERE @WClause ";
> this.sqlSelectCommand1.Connection = this.sqlConnection1;
> this.sqlSelectCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@WClause",
> System.Data.SqlDbType.NVarChar, 255, "WClause"));
>
>
> In the code, I do:
> string wClause = "(MyStr LIKE 'zyx%')";
> sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
> sqlDataAdapter1.Fill(dataSet11);
>
> I got this error: Line 1: Incorrect syntax near '@WClause'.
>
> I tried this in the query analyzer, it works fine:
> select *
> from myTable
> where (MyStr LIKE 'zyx%')
>
> What is problem?
>

[JIM.H.]

Thanks for the reply. I used this format it works Where (myId = @ID OR @ID IS
NULL)

"Marina" wrote:

You can adjust your query based on input. Just leave that condition out of
the whereclause.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:8D**********************************@microsof t.com...

The problem is with that: I need to add or remove constraints based on the
user input. For string fields I use %. However, for example "Where (myId =
@ID) " if user did not give @ID, that should not have any impact on the
query
result. How can I do this?

"Marina" wrote:

You can't do that. Parameters are meant to send literal values (strings,
integers, dates, etc). Not executable statements (or pieces of them).

You can make the query: "Select * From MyTable WHERE MyCol like @val"

Then add a variable called "@val", and set its value to whatever you need
it
to be.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:68**********************************@microsof t.com...
> Hello,
>
> I am trying to send Where clause as a parameter. Here is the detail:
>
> Select statment:
> // sqlSelectCommand1
> //
> this.sqlSelectCommand1.CommandText = "SELECT * " +
> " FROM myTable " +
> " WHERE @WClause ";
> this.sqlSelectCommand1.Connection = this.sqlConnection1;
> this.sqlSelectCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@WClause",
> System.Data.SqlDbType.NVarChar, 255, "WClause"));
>
>
> In the code, I do:
> string wClause = "(MyStr LIKE 'zyx%')";
> sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
> sqlDataAdapter1.Fill(dataSet11);
>
> I got this error: Line 1: Incorrect syntax near '@WClause'.
>
> I tried this in the query analyzer, it works fine:
> select *
> from myTable
> where (MyStr LIKE 'zyx%')
>
> What is problem?
>

[Scott Allen]

Hi Jim:

You just can't use a parameter for the entire WHERE clause. What you
would be trying to do in query analyzer would essentially be:

DECLARE @clause varchar(255)
SET @clause = '(MyStr LIKE ''zyx%'')'

SELECT *
FROM dbo.Categories
WHERE @clause

which yields the same error:

Line 6: Incorrect syntax near '@clause'.

--
Scott
http://www.OdeToCode.com/blogs/scott/

On Fri, 3 Jun 2005 12:35:02 -0700, JIM.H.
<JI**@discussions.microsoft.com> wrote:

Hello,

I am trying to send Where clause as a parameter. Here is the detail:

Select statment:
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT * " +
" FROM myTable " +
" WHERE @WClause ";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
this.sqlSelectCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@WClause",
System.Data.SqlDbType.NVarChar, 255, "WClause"));
In the code, I do:
string wClause = "(MyStr LIKE 'zyx%')";
sqlDataAdapter1.SelectCommand.Parameters["@WClause"].Value = wClause;
sqlDataAdapter1.Fill(dataSet11);

I got this error: Line 1: Incorrect syntax near '@WClause'.

I tried this in the query analyzer, it works fine:
select *
from myTable
where (MyStr LIKE 'zyx%')

What is problem?