Parameters in SQL Statement not working

[Grant Stanley]

I'm writing a database client program in C#, and it accesses a MS SQL V7
database. In one part of my program I am using a SqlCommand class to run a
SQL Statement, the command text uses parameters, but these do not seem to be
functioning (as it does not find the record), it's probably some really
stupid error I've made, but I've spend several hours trying to find it and
its now driving me crazy. Also something very similar else where in the
program works fine.

The table I'm using is called Suppliers and it has a field called
AccountCode and on of the records has the data value of Rice in the
AccountCode field.

If I write the statement so that it is as follows, the code finds the
record:
sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE AccountCode = 'Rice';";

But when using parameters it doesn't work, here is the code with parameters:

SqlCommand sqlCommand = new SqlCommand("", dbConnection);
SqlDataReader dReader;
sqlCommand.CommandType = CommandType.Text;

SqlParameter param1 = new SqlParameter("@searchField", "AccountCode");
sqlCommand.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@searchString", "Rice");
sqlCommand.Parameters.Add(param2);

sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE @searchField = '@searchString';";
// Also tried the @searchString with out the ' ' each side of it
//" WHERE AccountCode = 'Rice' ; "; // It works if modifed to
this
dReader = sqlCommand.ExecuteReader();
if(dReader.HasRows == true)
{
.....
}

Anyone have any ideas why the parameter version doesn't work?
No exceptions are thrown, the data reader just doesn't return any data.

Thank You for any help,
Grant

[newsreader]

Parameters are used with stored procedures.
I do not understand why are you trying to use paramaters with
CommandType.Text.

"Grant Stanley" <ag**@kent.ac.uk.delete> wrote in message
news:rQ********************@brightview.com...

I'm writing a database client program in C#, and it accesses a MS SQL V7
database. In one part of my program I am using a SqlCommand class to run a
SQL Statement, the command text uses parameters, but these do not seem to be functioning (as it does not find the record), it's probably some really
stupid error I've made, but I've spend several hours trying to find it and its now driving me crazy. Also something very similar else where in the
program works fine.

The table I'm using is called Suppliers and it has a field called
AccountCode and on of the records has the data value of Rice in the
AccountCode field.

If I write the statement so that it is as follows, the code finds the
record:
sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE AccountCode = 'Rice';";

But when using parameters it doesn't work, here is the code with parameters:
SqlCommand sqlCommand = new SqlCommand("", dbConnection);
SqlDataReader dReader;
sqlCommand.CommandType = CommandType.Text;

SqlParameter param1 = new SqlParameter("@searchField", "AccountCode");
sqlCommand.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@searchString", "Rice");
sqlCommand.Parameters.Add(param2);

sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE @searchField = '@searchString';";
// Also tried the @searchString with out the ' ' each side of it
//" WHERE AccountCode = 'Rice' ; "; // It works if modifed to
this
dReader = sqlCommand.ExecuteReader();
if(dReader.HasRows == true)
{
....
}

Anyone have any ideas why the parameter version doesn't work?
No exceptions are thrown, the data reader just doesn't return any data.

Thank You for any help,
Grant

[Eliyahu Goldin]

My guess is that you can use parameters only for literal values and not for
field names. You can't treat your query as a sort of macro expression where
parameter values substitute their placeholders no matter what they mean.
Rather parameters are used in run-time to pass values to the Select
statements or stored procedures.

HTH,

Eliyahu

"Grant Stanley" <ag**@kent.ac.uk.delete> wrote in message
news:rQ********************@brightview.com...

I'm writing a database client program in C#, and it accesses a MS SQL V7
database. In one part of my program I am using a SqlCommand class to run a
SQL Statement, the command text uses parameters, but these do not seem to be functioning (as it does not find the record), it's probably some really
stupid error I've made, but I've spend several hours trying to find it and its now driving me crazy. Also something very similar else where in the
program works fine.

The table I'm using is called Suppliers and it has a field called
AccountCode and on of the records has the data value of Rice in the
AccountCode field.

If I write the statement so that it is as follows, the code finds the
record:
sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE AccountCode = 'Rice';";

But when using parameters it doesn't work, here is the code with parameters:
SqlCommand sqlCommand = new SqlCommand("", dbConnection);
SqlDataReader dReader;
sqlCommand.CommandType = CommandType.Text;

SqlParameter param1 = new SqlParameter("@searchField", "AccountCode");
sqlCommand.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@searchString", "Rice");
sqlCommand.Parameters.Add(param2);

sqlCommand.CommandText = "SELECT * FROM " + "[" + this.tableName + "]" +
" WHERE @searchField = '@searchString';";
// Also tried the @searchString with out the ' ' each side of it
//" WHERE AccountCode = 'Rice' ; "; // It works if modifed to
this
dReader = sqlCommand.ExecuteReader();
if(dReader.HasRows == true)
{
....
}

Anyone have any ideas why the parameter version doesn't work?
No exceptions are thrown, the data reader just doesn't return any data.

Thank You for any help,
Grant