Solved

ASP.NET SelectCommand Syntax Question

Posted on 2010-09-20
10
354 Views
Last Modified: 2013-11-07
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
Comment
Question by:JT_SIRO
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 33721359
Description is a reserved keyword. Put brackets around it:

  ... AND [Description] like @Keywords
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 42 total points
ID: 33721370
...right, and the wildcards. Concatenate the strings:

  ... AND [Description] like '%' + @Keywords + '%'
0
 
LVL 41

Assisted Solution

by:guru_sami
guru_sami earned 42 total points
ID: 33721399
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 41

Expert Comment

by:guru_sami
ID: 33721404
oops my bad...forgot to refresh...
0
 

Author Comment

by:JT_SIRO
ID: 33721501
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
 

Author Comment

by:JT_SIRO
ID: 33721504
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
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 33724661
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
 
LVL 8

Assisted Solution

by:cubaman_24
cubaman_24 earned 41 total points
ID: 33724710
WHERE ([Keywords] LIKE '%' + @Keywords+ '%' + 'OR @Keywords IS NULL '
0
 
LVL 7

Expert Comment

by:kovilpattiBalu
ID: 33725157
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
 

Author Comment

by:JT_SIRO
ID: 33727587
Thanks everyone!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
asp day pilot 3 20
SqlDataBase 7 48
JQuery JavaScripot loop though RadioButtonList 4 25
ASP.NET MVC identity 6 27
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question