Sqlparameter

humayunlalzad

The following does not work

   cmd = con.CreateCommand();
   cmd.CommandText = @"select \* from tblcustomer where @column = @value";
   cmd.Parameters.Add("@column", SqlDbType.NVarChar , 50);
   cmd.Parameters.Add("@value", SqlDbType.NVarChar , 100);
   cmd.Parameters\["@column"\].Value = comboBox1.SelectedItem.ToString();
   cmd.Parameters\["@value"\].Value = txtSearch.Text;

But this workds

cmd = con.CreateCommand();
cmd.CommandText = @"select * from tblcustomer where " + comboBox1.SelectedItem.ToString() + " = @value";
//cmd.Parameters.Add("@column", SqlDbType.NVarChar , 50);
cmd.Parameters.Add("@value", SqlDbType.NVarChar , 100);
//cmd.Parameters["@column"].Value = comboBox1.SelectedItem.ToString();
cmd.Parameters["@value"].Value = txtSearch.Text;

Can someone tell me whats the problem with the first code snippet

Estys

Yep, it's as if you had written :

@"select * from tblcustomer where '" + comboBox1.SelectedItem.ToString() + "'= @value"

Note the single quotes. You can't insert a column name as a parameter. Cheers

I don't like my signature at all

humayunlalzad

Ok I get it now. But is there any other way of doing it?

Estys

Maybe, haven't tested this :

Select blabla FROM YourTable WHERE
CASE
WHEN @a = 'YourColumn1' THEN YourColumn1
WHEN @a = 'YourColumn2' THEN YourColumn2
ELSE YourColumn1
END
= @value

If this works it has the drawback is that when columns are added or changed, you need to edit your query. Cheers [edit] forgot the END :zzz: [/edit]

I don't like my signature at all

modified on Saturday, July 3, 2010 8:51 AM

Not Active

Column names are not simple strings, they are literals that represent values from the sys.columns table. In oder to do this you need to construct the sql dynamically and I highly and strongly recommend you do it in a stored procedure As an example:

declare @value nvarchar(255)
declare @column nvarchar(255)

set @value = 'Some value'
set @column = 'ColumnName'

declare @sql nvarchar(1024)

set @sql = 'select * from tblcustomer where ' + @column + ' = ''' + @value + ''''
exec(@sql)

Notice that @value needs to be in quotes since it is a string. Also, don't use * to return results. It is more efficient to return named columns and better documented.

I know the language. I've read a book. - _Madmatt

Not Active

Very poor example that opens up sql injection attacks

I know the language. I've read a book. - _Madmatt