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:SqlDataSourceand 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_noThanks.
>> >> 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.