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

Discussion in 'ASP .Net' started by Guest, Aug 18, 2007.

  1. Guest

    Guest Guest

    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
     
    Guest, Aug 18, 2007
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    doesn't work even with "correct" SQL references Re: Proper way to use LIKE operator with SqlDataSource and control parameters in declarative markup?

    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%'))">

    <> wrote in message
    news:%23fQf6%...
    > 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
    >
     
    Guest, Aug 18, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    SOLVED Re: doesn't work even with "correct" SQL references Re: Proper way to use LIKE operator with SqlDataSource and control parameters in declarative markup?

    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 + '%'))">


    <> wrote in message
    news:...
    >
    > 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%'))">
    >
    > <> wrote in message
    > news:%23fQf6%...
    >> 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
    >>

    >
    >
     
    Guest, Aug 18, 2007
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=

    proper way to pass parameters ??

    =?Utf-8?B?c2VyZ2UgY2FsZGVyYXJh?=, Jan 30, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    479
    Karl Seguin [MVP]
    Jan 30, 2006
  2. =?Utf-8?B?SmVmZkRvdE5ldA==?=

    Declarative use of sqlDataSource in ASP.net 2.0 and Exception Hand

    =?Utf-8?B?SmVmZkRvdE5ldA==?=, Feb 1, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    2,999
    Steven Cheng[MSFT]
    Feb 2, 2006
  3. Guest
    Replies:
    4
    Views:
    534
    Steven Cheng[MSFT]
    Jul 22, 2006
  4. jobs
    Replies:
    1
    Views:
    373
    Siva M
    Oct 21, 2006
  5. Tastic
    Replies:
    2
    Views:
    402
    Tastic
    Feb 16, 2007
  6. Ken Cox [Microsoft MVP]
    Replies:
    3
    Views:
    572
    Walter Wang [MSFT]
    Jun 20, 2007
  7. Serg Koren

    Proper way to RDoc markup?

    Serg Koren, Feb 15, 2008, in forum: Ruby
    Replies:
    11
    Views:
    416
    Tony Strauss
    Sep 27, 2008
  8. H. Wade Minter
    Replies:
    8
    Views:
    605
    Robin
    Apr 25, 2004
Loading...