ASP.NET not executing Oracle query
-
Hi all When I execute this query in Oracle I get perfect results. 1. SELECT * FROM TOTAL_CASES 2. WHERE (TRIM("NTN") = TRIM(:NTN) 3. OR '%'||upper("NAME")||'%' LIKE '%'||upper(:NAME)||'%') 4. AND (upper(TRIM("STATUS")) LIKE nvl(upper(TRIM(:STATUS)),'%'||''||'%')) It means that if :STATUS is NULL then give all results matching step 3 or 4. If there is a value for :STATUS then also filter results according to that value. This query gives accurate results when executed in SQL. However, I also use this to populate a Data Grid in ASP.NET. When STATUS is NULL in ASP.NET, the Data Grid shows no results. However, when I select value for the STATUS, then it shows results according to the value chosen for STATUS. Please help me as to how to populate Data Grid when I select no value for STATUS. Thanks. Hammad
-
Hi all When I execute this query in Oracle I get perfect results. 1. SELECT * FROM TOTAL_CASES 2. WHERE (TRIM("NTN") = TRIM(:NTN) 3. OR '%'||upper("NAME")||'%' LIKE '%'||upper(:NAME)||'%') 4. AND (upper(TRIM("STATUS")) LIKE nvl(upper(TRIM(:STATUS)),'%'||''||'%')) It means that if :STATUS is NULL then give all results matching step 3 or 4. If there is a value for :STATUS then also filter results according to that value. This query gives accurate results when executed in SQL. However, I also use this to populate a Data Grid in ASP.NET. When STATUS is NULL in ASP.NET, the Data Grid shows no results. However, when I select value for the STATUS, then it shows results according to the value chosen for STATUS. Please help me as to how to populate Data Grid when I select no value for STATUS. Thanks. Hammad
hi hammad it is possible that your page is passing a blank string instead of NULL to the query. check if the following works in line 4
AND (upper(TRIM("STATUS")) LIKE ('%' || upper(TRIM(nvl(:STATUS,'%')) || '%')
i havent been able to check it but hope that helps.