Run SQL Server Profiler and examine the exact command you're sending to SQL Server. (Assuming your SQL Server version is not Express).

It is also possible that your SSMS has different settings that ASP.NET application does and the difference in settings plays a role in this behavior somehow.

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