CODEWITHSUNDEEP
c#asp.netsql
LIH
LIH

Reputation: 933

How to give a variable inside sql in c#?

I am inserting data to a data list in asp.net. I need to modify the Select Command according to my needs. I did something like this,

string query = "SELECT [movieName], [sDate], [eDate], [IMDb], [imageUrl] FROM [movieDrama] WHERE ([category]='Drama' AND [movieName] like '%@key%') ORDER BY [movieName]";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@key", key);
SqlDataSource1.SelectCommand = query;

But this is not working. I think I did something wrong when defining '@key'. How to do it in correct way? Thanks in advance...

Upvotes: 0

Views: 141

Answers (3)

Dancharim
Dancharim

Reputation: 26

Try giving your sqlCommand the connection parameter:

    SqlCommand cmd = new SqlCommand(query,YOURCONNECTIONSTRING);
    cmd.Parameters.AddWithValue("key", key)

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98740

Use it like;

LIKE '%' + @key + '%'

instead of 

LIKE '%@key%'

For full query;

string query = "SELECT [movieName], [sDate], [eDate], [IMDb], [imageUrl] FROM [movieDrama] WHERE ([category]='Drama' AND [movieName] LIKE '%' + @key + '%') ORDER BY [movieName]";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@key", key);

And actually, you don't need square brackets every column of your query, you just need to use when you want use some reserved keywords as a column names.

Upvotes: 6

King King
King King

Reputation: 63317

Just this:

string query = "SELECT [movieName], [sDate], [eDate], [IMDb], [imageUrl] FROM [movieDrama] WHERE ([category]='Drama' AND [movieName] like @key) ORDER BY [movieName]";

then

cmd.Parameters.AddWithValue("@key", "%"+ key + "%");

Upvotes: 2

Related Questions