Unfortunately I am running SQL Server Express 2008 R2.
>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.
