Naomi,

I tried setting the "ConvertEmptyStringToNull" property to both true and false. Neither setting affected the results.

>Hi Elgin,
>
>There is a property of SQLDataSource that converts empty values to nulls ConvertEmptyStringToNull. Set this property to true.
>>Naomi,
>>
>>Thank you for the blog post info on making my query more efficient. And I have implemented the DYNAMIC methods of the WHERE clause successfully.
>>
>>BUT, this still doesn't address my initial problem. My real issue is NOT the query. Even the inefficient one was working correctly. I am able to get proper results on my query when I enter any one (or more) of a number of optional parameters. It only works properly in the SQL Management Studio Express and when I use the Test Query button in MS Visual Web Developer Express' datasource designer.
>>
>>When I call my ASPX page with ONLY a contactcode specified (as I do in SSMS), the Gridview returns only records that DO NOT have a null value for any of the other optional parameters, which are sub_name, co_equipno, and serial_no. When I run the query in SSMS with just a contactcode, it correctly returns ALL records for that contact regardless of the value of the other optional parameter fields.
>>
>>Why does the query work properly in SSMS and not in my web page?
>>
>>Here's the DataSource Definition on my aspx page. I removed some brackets in order for formatting here to work:
>>
>>
>>

>>
>>asp:SqlDataSource ID="SqlEquipmentWithContactCode" runat="server" ConnectionString="%$ ConnectionStrings:dataCS %"
>>    SelectCommand="getEquipment" SelectCommandType="StoredProcedure"
>>   SelectParameters
>>        asp:ControlParameter ControlID="ddlContactCode" Name="contactcode" PropertyName="SelectedValue" Type="String" DefaultValue="" 
>>        asp:ControlParameter ControlID="ddlUpsino" Name="upsino" PropertyName="SelectedValue" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" 
>>        asp:ControlParameter ControlID="txtSubName" Name="sub_name" PropertyName="Text" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" 
>>        asp:ControlParameter ControlID="txtCoEquipNo" Name="co_equipno" PropertyName="Text" Type="String" DefaultValue="" ConvertEmptyStringToNull="false"
>>        asp:ControlParameter ControlID="txtSerialNo" Name="serial_no" PropertyName="Text" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" 
>>    SelectParameters
>>asp:SqlDataSource
>>
>>
>>

>>
>>and here's my new stored more efficient stored procedure using a dynamically built WHERE clause:
>>
>>

>>
>>
>>create procedure dbo.getEquipment(
>>	@contactcode varchar(20)= null,
>>	@upsino varchar(7) = '%',
>>	@sub_name varchar(50) = null,
>>	@co_equipno varchar(50) = null,
>>	@serial_no varchar(30) = null)
>>
>>as
>>
>>declare @sql nvarchar(4000)
>>
>>set @sql= "select eq.upsino as 'Upsino',
>>			eq.sub_name as 'Sub Name',
>>			eq.co_equipno as 'Company Equip No',	
>>			mfg.code_name as 'Manufacturer',
>>			eq.serial_no as 'Serial No',	
>>			eq.gallons as 'Gallons',
>>			fluidtype.code_name as 'Fluid Type',
>>			eq.valves as 'Valves',
>>			eq.mfg_date as 'Mfg Date',	
>>			eqtype.code_name as 'Equipment Type',		
>>			eq.high_volt as 'High Voltage',
>>			eq.low_volt as 'Low Voltage',
>>			eq.impedance as 'Impedance',
>>			eq.kva as 'KVA Rating',
>>			eq.phase_cycle as 'Phase/Cycle'
>>		from lab.equipment eq
>>		inner join sales.contacts c on eq.id_contact = c.id
>>		left join meta.codes eqtype on eq.id_equiptype = eqtype.id
>>		left join meta.codes mfg on eq.id_mfg = mfg.id
>>		left join meta.codes fluidtype on eq.id_fluidtype = fluidtype.id
>>		where c.contactcode = @contactcode and 
>>			 eq.upsino like @upsino + '%'" 
>>
>>if @sub_name is not null
>>	set @sql = @sql + " and eq.sub_name LIKE @sub_name + '%' "
>>	
>>if @co_equipno is not null
>>	set @sql = @sql + " and eq.co_equipno LIKE @co_equipno + '%' "
>>
>>if @serial_no is not null
>>	set @sql = @sql + " and eq.serial_no LIKE @serial_no + '%' "
>>		
>>set @sql = @sql + " order by eq.upsino, eq.sub_name, eq.co_equipno "
>>
>>exec sp_executesql @sql,N'@contactcode varchar(20), 
>>							@upsino varchar(7), 
>>							@sub_name varchar(50), 
>>							@co_equipno varchar(50), 
>>							@serial_no varchar(30)',
>>							@contactcode,
>>							@upsino,
>>							@sub_name,
>>							@co_equipno,
>>							@serial_no
>>
>>
>>

>>
>>
>>
>>
>>Thanks.
>>
>>>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.