How to use LIKE statement in SqlParameter?

Posted by Poster under ASP.NET on 12/3/2008 | Views : 5929 | Status : [Member] | Replies : 3
I am in process of writing DAL in which I have to pass a keyword with that will be checked using LIKE statement.

The actual statement should appear like this "select * from mytable where username = '%keyword%'.

How to pass the value in the SQL Parameter?

Thanks




Responses

Posted by: Raja on: 12/3/2008 [Member] Starter

Up
0
Down
Do like following

Public DataTable Search(string keyword)

{
DataTable dTable = new DataTable();
using (SqlCommand dCmd = new SqlCommand("vs_SearchUserName", conn))
{
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.AddWithValue("@keyword", "%" + keyword + "%");
using (SqlDataAdapter dAd = new SqlDataAdapter(dCmd))
{
dAd.Fill(dTable);
}
}
return dTable;
}


Hope this is exactly what you were looking for.

Regards,
Raja, USA

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Poster on: 12/3/2008 [Member] Starter

Up
0
Down
Yup, Many thanks Raja.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Coolcode on: 12/30/2008 [Member] Starter

Up
0
Down
Don't hardcode the '%' sign from the C# code or VB code while development.

Use the the % sign in your stored proc and concatenate the sproc parameter in query.

e.g.:

@keyword varchar(50)

select * from mytable where username = '%' + @keyword + '%'

And pass the @Keyword from your SQLClient C# code.

Also remember to validate the textbox value that it must not be blank and keyword must not have wildcard characters that are used in SQL query to search.

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response