Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Proper way to use LIKE operator with SqlDataSource and control parameters in declarative markup?

Reply
Thread Tools

Proper way to use LIKE operator with SqlDataSource and control parameters in declarative markup?

 
 
Guest
Posts: n/a
 
      08-18-2007
I'm trying to use LIKE operators in SqlDataSource with Control Parameters.
The following should express my intent, but it does not work in the way I
want it to (returns no results):

SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
'102') AND (byline LIKE '%@byline%')) OR ((categoryid = '102') AND (keywords
LIKE '%@keywords%')) OR ((categoryid = '102') AND (keywords LIKE
'%@jobid%')) OR ((categoryid = '102') AND (title LIKE '%@keywords%'))">

Besides generating the string that should go with the SelectCommand
programmatically, is there a way to make this work right declaratively?

Subbing in literal values returns a whole lot of results:

SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
'102') AND (byline LIKE '%president%')) OR ((categoryid = '102') AND
(keywords LIKE '%president%')) OR ((categoryid = '102') AND (jobid LIKE
'%president%')) OR ((categoryid = '102') AND (title LIKE '%president%'))">

Help out there? MSDN managed newsgroup support should be linked to this
account in, oh, 12 hours or so.

Thanks!

-KF


 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      08-18-2007

Yeah, I know that that first SQL statement seemed a little funny, but the
"corrected" version doesn't help either:

SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
'102') AND (byline LIKE '%@byline%')) OR ((categoryid = '102') AND (keywords
LIKE '%@keywords%')) OR ((categoryid = '102') AND (jobid LIKE '%@jobid%'))
OR ((categoryid = '102') AND (title LIKE '%@title%'))">

<(E-Mail Removed)> wrote in message
news:%23fQf6%(E-Mail Removed)...
> I'm trying to use LIKE operators in SqlDataSource with Control Parameters.
> The following should express my intent, but it does not work in the way I
> want it to (returns no results):
>
> SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
> '102') AND (byline LIKE '%@byline%')) OR ((categoryid = '102') AND
> (keywords LIKE '%@keywords%')) OR ((categoryid = '102') AND (keywords LIKE
> '%@jobid%')) OR ((categoryid = '102') AND (title LIKE '%@keywords%'))">
>
> Besides generating the string that should go with the SelectCommand
> programmatically, is there a way to make this work right declaratively?
>
> Subbing in literal values returns a whole lot of results:
>
> SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
> '102') AND (byline LIKE '%president%')) OR ((categoryid = '102') AND
> (keywords LIKE '%president%')) OR ((categoryid = '102') AND (jobid LIKE
> '%president%')) OR ((categoryid = '102') AND (title LIKE
> '%president%'))">
>
> Help out there? MSDN managed newsgroup support should be linked to this
> account in, oh, 12 hours or so.
>
> Thanks!
>
> -KF
>



 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      08-18-2007
This CAN be done from declarative code.

The answer is to use concatenation operators and single quotes around the
percent symbols as follows:

SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
'102') AND (byline LIKE '%' + @byline + '%')) OR ((categoryid = '102') AND
(keywords LIKE '%' + @keywords + '%')) OR ((categoryid = '102') AND (jobid
LIKE '%' + @jobid + '%')) OR ((categoryid = '102') AND (title LIKE '%' +
@title + '%'))">


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Yeah, I know that that first SQL statement seemed a little funny, but the
> "corrected" version doesn't help either:
>
> SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
> '102') AND (byline LIKE '%@byline%')) OR ((categoryid = '102') AND
> (keywords LIKE '%@keywords%')) OR ((categoryid = '102') AND (jobid LIKE
> '%@jobid%')) OR ((categoryid = '102') AND (title LIKE '%@title%'))">
>
> <(E-Mail Removed)> wrote in message
> news:%23fQf6%(E-Mail Removed)...
>> I'm trying to use LIKE operators in SqlDataSource with Control
>> Parameters. The following should express my intent, but it does not work
>> in the way I want it to (returns no results):
>>
>> SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
>> '102') AND (byline LIKE '%@byline%')) OR ((categoryid = '102') AND
>> (keywords LIKE '%@keywords%')) OR ((categoryid = '102') AND (keywords
>> LIKE '%@jobid%')) OR ((categoryid = '102') AND (title LIKE
>> '%@keywords%'))">
>>
>> Besides generating the string that should go with the SelectCommand
>> programmatically, is there a way to make this work right declaratively?
>>
>> Subbing in literal values returns a whole lot of results:
>>
>> SelectCommand="SELECT title, conid FROM Contentitems WHERE ((categoryid =
>> '102') AND (byline LIKE '%president%')) OR ((categoryid = '102') AND
>> (keywords LIKE '%president%')) OR ((categoryid = '102') AND (jobid LIKE
>> '%president%')) OR ((categoryid = '102') AND (title LIKE
>> '%president%'))">
>>
>> Help out there? MSDN managed newsgroup support should be linked to this
>> account in, oh, 12 hours or so.
>>
>> Thanks!
>>
>> -KF
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SqlDataSource: No valid declarative parameter value for a datetime? Ken Cox [Microsoft MVP] ASP .Net 3 06-20-2007 02:36 AM
Multiple (unrelated) declarative parameters Tastic ASP .Net 2 02-16-2007 10:50 PM
Declarative use of sqlDataSource in ASP.net 2.0 and Exception Hand =?Utf-8?B?SmVmZkRvdE5ldA==?= ASP .Net 1 02-02-2006 05:35 AM
proper way to pass parameters ?? =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?= ASP .Net 3 01-30-2006 01:56 PM
Proper way to use an imported constant under 'use strict'? H. Wade Minter Perl Misc 8 04-25-2004 12:58 AM



Advertisments