Sqldatasource and LIKE command

Question

User324972980 posted

How can I get my sqldatasource to recognize the LIKE wildcard?  My query needs to be like this:

SELECT *
FROM vwUsers
WHERE (submitter LIKE '%g%')

That will return results that has the letter g even if the word starts with g.  

My sqldatasource passes in a parameter but I don't now how to format my query:

<asp:SqlDataSource ID="SqlDataSourceCCBName" runat="server" 
           ConnectionString="<%$ ConnectionStrings:CCBcs %>" 
           SelectCommand="SELECT *
           FROM vwUsers
           WHERE (CCBName LIKE '%' + @CCBName +'%')">

Answer 1

User77042963 posted

Your code looks good.

 

 SelectCommand="SELECT * FROM vwUsers  WHERE (CCBName LIKE '%' + @CCBName +'%') "

Answer 2

User324972980 posted

I built a search.  With my datasource the way it is now, it will not find the first letter.  For example, if the user puts the letter g in the textbox and clicks submit, it will not find words such as good, girl, great. 

Answer 3

User77042963 posted

What data type is CCBName? Make it varchar(1000) and try again.

Answer 4

User324972980 posted

Changing the datatype to varchar fixed my search.  I had nvarchar.  Wonder why it doesn't recognize it with nvarchar?

 

Thanks for your help!

Answer 5

User77042963 posted

You can try to put N in front of your first wildcard to make it work:

 

SELECT * FROM @t  WHERE (CCBName LIKE N'%' + @CCBName +'%') 

Answer 6

User324972980 posted

I just changed the datatype to VarChar.  Again, thanks for your help.