Q: what is problem here

[Guest]

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.

 

[Guest]

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?

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.

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.

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?

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.

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?

 

[Guest]

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

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

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?

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.

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'.