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.