[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

ASP.NET SelectCommand Syntax Question

I'm trying to add a simple keywords search to my ojbect datasource and am having trouble with the syntax.  I basically want to say 'select * from xyz where Description like '%The keywords from textbox%'.  How do I represent the "%" character in my SelectCommand?

It works fine when I put: Description = @Keywords, but I need the "%"s.
I tried the following which didn't work:
"Description like '%@Keywords%'"

Here's my complete SelectCommand:
SelectCommand="SELECT [RecID], [TrackTitle], [Composer], [Filename], [Popularity], [Time], [Description], [Category], [SubCategory], [Keywords], [BPM], [CDTitle], [IsVocal] FROM [metadata] WHERE Composer like @Artist AND Status = @Status AND Description like @Keywords"
0
JT_SIRO
Asked:
JT_SIRO
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Göran AnderssonCommented:
Description is a reserved keyword. Put brackets around it:

  ... AND [Description] like @Keywords
0
 
Göran AnderssonCommented:
...right, and the wildcards. Concatenate the strings:

  ... AND [Description] like '%' + @Keywords + '%'
0
 
guru_samiCommented:
something like this:

 WHERE ([Keywords] LIKE '%' + @Keywords+ '%'

Use the SqlDataSource wizard and there if you use Where to add that, it should generate SelectCommand for you.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
guru_samiCommented:
oops my bad...forgot to refresh...
0
 
JT_SIROAuthor Commented:
Great, that worked!  Now how would account for the user leaving the keywords field blank.  With this syntax "AND [Description] like '%' + @Keywords + '%'", it requires that there be something in Keywords.  How would I add some "if not null" clause?
0
 
JT_SIROAuthor Commented:
FYI - I certainly know how to do this in code behind, but I'd like to utilize the object datasource.  I'm using it to save time, and it's wasting me time....  Uggg.  
0
 
Göran AnderssonCommented:
If the @Keywords parameter is an empty string, you can just leave it like it is, and it will produce the string '%%' which matches anything.

If the @Keywords parameter can be null, use the isnull function:

  AND [Description] like '%' + isnull(@Keywords, '') + '%'
0
 
cubaman_24Commented:
WHERE ([Keywords] LIKE '%' + @Keywords+ '%' + 'OR @Keywords IS NULL '
0
 
kovilpattiBaluCommented:
try this..
store @keywords in a variable, and than assign the variable in the selectcommand

store in a variable..
string searchString = "%" + @keywords + "%"
assign it..
"AND Description like '" + searchString + "'"
0
 
JT_SIROAuthor Commented:
Thanks everyone!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now