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