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.Comm andText = "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.Comm andType = CommandType.Tex t;

SqlParameter param1 = new SqlParameter("@ searchField", "AccountCod e");
sqlCommand.Para meters.Add(para m1);
SqlParameter param2 = new SqlParameter("@ searchString", "Rice");
sqlCommand.Para meters.Add(para m2);

sqlCommand.Comm andText = "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.Exec uteReader();
if(dReader.HasR ows == 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]

Re: Parameters in SQL Statement not working

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

"Grant Stanley" <ags2@kent.ac.u k.delete> wrote in message
news:rQ6dnaUKMp 8L77miU-KYvw@brightview .com...[color=blue]
> 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[/color]
be[color=blue]
> 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[/color]
and[color=blue]
> 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.Comm andText = "SELECT * FROM " + "[" + this.tableName + "]" +
> " WHERE AccountCode = 'Rice';";
>
> But when using parameters it doesn't work, here is the code with[/color]
parameters:[color=blue]
>
> SqlCommand sqlCommand = new SqlCommand("", dbConnection);
> SqlDataReader dReader;
> sqlCommand.Comm andType = CommandType.Tex t;
>
> SqlParameter param1 = new SqlParameter("@ searchField", "AccountCod e");
> sqlCommand.Para meters.Add(para m1);
> SqlParameter param2 = new SqlParameter("@ searchString", "Rice");
> sqlCommand.Para meters.Add(para m2);
>
> sqlCommand.Comm andText = "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.Exec uteReader();
> if(dReader.HasR ows == 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
>
>[/color]

[Eliyahu Goldin]

Re: Parameters in SQL Statement not working

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" <ags2@kent.ac.u k.delete> wrote in message
news:rQ6dnaUKMp 8L77miU-KYvw@brightview .com...[color=blue]
> 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[/color]
be[color=blue]
> 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[/color]
and[color=blue]
> 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.Comm andText = "SELECT * FROM " + "[" + this.tableName + "]" +
> " WHERE AccountCode = 'Rice';";
>
> But when using parameters it doesn't work, here is the code with[/color]
parameters:[color=blue]
>
> SqlCommand sqlCommand = new SqlCommand("", dbConnection);
> SqlDataReader dReader;
> sqlCommand.Comm andType = CommandType.Tex t;
>
> SqlParameter param1 = new SqlParameter("@ searchField", "AccountCod e");
> sqlCommand.Para meters.Add(para m1);
> SqlParameter param2 = new SqlParameter("@ searchString", "Rice");
> sqlCommand.Para meters.Add(para m2);
>
> sqlCommand.Comm andText = "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.Exec uteReader();
> if(dReader.HasR ows == 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
>
>[/color]