Can't filter Oracle SELECT in ASPX

Question

Part of an ASP.Net 2 datasource:

  SelectCommand="SELECT BU.P_GEAC_CORP_CD AS Corp_Code,
                        BU.Business_unit  as Abbreviation,
                        CC.DEPTID         AS Cost_Center,
                        CC.DESCR          AS Description
                 FROM fstst.PS_P_CATR_BUDPT_VW CC,
                      fstst.ps_p_bus_unit_cnv  BU 
                 WHERE BU.Business_unit = CC.Business_unit">

This feeds a GridView which works. The display shows that

CC.DESCR          AS Description

is text (non-numeric).

I want to use a textbox as a "contains" filter, i.e., if I put "Recovery" in the box, I want the datasource to add

AND CC.DESCR Like '%Recovery%'

to the SQL. If I hard-code that line, it works.

But if I add

 <SelectParameters>
    <asp:ControlParameter ControlID="Dept_Name"
                          Name="DName"
                          PropertyName="Text"
                          Type="string" />
 </SelectParameters>

without changing the SQL, I get no rows returned. Then if I put

AND CC.DESCR Like '%' + :DName + '%'

into the SQL, I get no results when the textbox is blank, and ORA-01722: invalid number as soon as I put characters in it.

Answer 1

you need to use single quotes around the text when using a like statement with a string.

It needs to look like

AND CC_DESCR LIKE '%VALUE%'

Answer 2

Thanks for the attempt, "birdlips."

Unfortunately, the Oracle server had only minimal logging turned on.

On top of that, while I was visiting the DBA, stackoverflow somehow ended up on our company's list of forbidden sites. So I could not share the answer.

This must be a little known fact about Oracle, as our two DBAs didn't catch it either: Oracle thinks plus is for numbers no matter what the context.

It worked as soon as I changed + to ||