How to use LIKE statement in SqlParameter?

Posted by Poster under ASP.NET on 12/3/2008 | Views : 7393 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

Latest Posts