sqldatasource problem

Question

I have a query that works when I test it in the sqlDataSource control (wizard test button) but it doesn't work when I run the page. I made sure the gridview has the correct sqlDataSource control as its source. This is driving me crazy.

Has this happened to anyone else?

edit:

It has something to do with this line in the where clause (it's an oracle database)

Where    (upper(AA.Email_Address)=UPPER(:Email_Address) OR :Email_Address IS NULL) AND 
 (upper(AA.Display_Name) Like  UPPER('%' || :Display_Name || '%')   OR :Display_Name IS NULL)

The page works with each of the search clauses individually but not when they are both there( it works in sqlDataSource wizard test with both paramters but not when the page runs)

=====================================================================================

Edit:

Thanks for the query advice that does look cleaner.

I did have the default CancelSelectOnNullParameter property set to "true". When I change it to false the page won't display. I get this message

"Server Application Unavailable The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request. Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur. "

================================

Here is the query that works except when I run the page

SELECT * 
FROM a Left Join b on b.institution_code=a.institution_code
WHERE 
      (upper(a.Login_Name)=UPPER('%' || :Login_Name || '%') OR :Login_Name IS NULL)
  AND (upper(a.Display_Name) Like  UPPER('%' || :Display_Name || '%')   OR :Display_Name IS NULL) 
  AND (upper(a.Email_Address)=UPPER(:Email_Address) OR :Email_Address IS NULL) 
  AND ((a.institution_code=:institution_code) OR :institution_code IS NULL)  
  AND (upper(b.institution_desc) Like  UPPER('%' || :institution_desc || '%')   OR :institution_desc IS NULL

Answer 1

First of all, you'll do better writing the query like this to remove the 'OR's from the where clause:

WHERE
        Upper(AA.Email_Address)  =  Upper(COALESCE(:Email_Address,AA.Email_Address))
    AND Upper(AA.Display_Name) LIKE Upper('%' || COALESCE(:Display_Name,AA.Display_Name) || '%')

As for your specific question, you're probably running into the CancelSelectOnNullParameter property. Just set it to "false" and you'll be fine.

Answer 2

Did you set correct ProviderName property of your datasource?

Try setting

Provider="System.Data.OracleClient"

attribute in your SqlDataSource tag.