Hi Elgin,

The way you're doing it is not optimal. Please take a look at this blog post
Do you use ISNULL(...). Don't, it does not perform


>Hi,
>
>I am designing a DotNetNuke module that is a gridview that pulls data from my SQL database using a stored procedure. I am designing the module to allow the user to enter all or part of multiple criteria. I finally have a WHERE clause that works exactly as I need. That is, the user enters any or all of the three criteria to broaden or narrow the lookup as much or as little as desired.
>
>The three dynamic criteria are co_equip_no, sub_name, and serial_no. I have two other criteria in the WHERE clause, they are contactcode and upsino. For this issue assume the contactcode is fixed at a single value and the the upsino is left blank.
>
>The procedure works perfectly both in SQL Management Studio and in the "Test Query" function of the datasource designer in MS Web Developer Express. That is, when I specify, for example "T-1" for the co_equip_no it will retrieve the records correctly whether or not the sub_name contains data or is empty (null) , either.
>
>My problem is that when I try to use this datasource as the datasource for my gridview in my aspx page, it will not pull records that have a null or empty sub_name, or null or empty co_equip_no, or where any of the three criteria are empty. Like I said, it all works fine in SQL Studio but not for my asp:gridview.
>
>My code (partial) is below:
>
>Here's the WHERE clause:
>

>   
>   SELECT  
>    ...... FROM....
>        
>       WHERE c.contactcode = @contactcode and
>	(@upsino IS NULL OR eq.upsino LIKE @upsino + '%') and
>	(@sub_name IS NULL OR eq.sub_name LIKE @sub_name + '%') and
>	(@co_equipno IS NULL OR eq.co_equipno LIKE @co_equipno + '%') and 
>	(@serial_no IS NULL OR eq.serial_no LIKE @serial_no + '%')
>        ORDER BY eq.upsino, eq.sub_name, eq.co_equipno
>
>Here's the ASP datasource definition:
>
>  WELL, this system won't let me submit the ASP Datasource definition.  I don't know how.  Too many tags I suppose.
>
>
>

>WHY won't the GRIDVIEW show the same data that the SQL Studio and the "Test Query " function shows. I have tried the ConvertEmptyStringToNull both TRUE and FALSE, no difference.
>
>
>
>Thank you.