I have a stored procedure that selects a table according to several criteria, it is a search type if the user does not enter any parameter all the records in the table must be shown, in case the user decides to fill in a criterion of search only returns the records that match
USE [aaa]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcListarAnunciosPortal]
(
@ID_Anuncio BIGINT =NULL ,
@Icono VARCHAR(30) =NULL ,
@Titulo VARCHAR(200) =NULL ,
@Cuerpo VARCHAR(Max) =NULL ,
@UrlArchivo VARCHAR(255) =NULL ,
@PaginaPrincipal BIT =NULL ,
@FechaDesde DATE =NULL ,
@FechaHasta DATE =NULL ,
@ID_UsuarioAlta INT =NULL ,
@FechaAlta DATETIME =NULL ,
@ID_UsuarioModifica INT =NULL ,
@FechaModificacion DATETIME =NULL
)
AS
BEGIN
SET NOCOUNT OFF;
BEGIN TRY
SELECT
ID_Anuncio,
Icono,
Titulo,
Cuerpo,
UrlArchivo,
PaginaPrincipal,
FechaDesde,
FechaHasta,
ID_UsuarioAlta,
FechaAlta,
ID_UsuarioModifica,
FechaModificacion
FROM ANUNCIOSPORTAL WHERE
(ID_Anuncio=@ID_Anuncio OR @ID_Anuncio IS NULL) AND
(Icono=@Icono OR @Icono IS NULL) AND
(Titulo=@Titulo OR @Titulo IS NULL) AND
(Cuerpo=@Cuerpo OR @Cuerpo IS NULL) AND
(UrlArchivo=@UrlArchivo OR @UrlArchivo IS NULL) AND
(PaginaPrincipal=@PaginaPrincipal OR @PaginaPrincipal IS NULL) AND
(FechaDesde=@FechaDesde OR @FechaDesde IS NULL) AND
(FechaHasta=@FechaHasta OR @FechaHasta IS NULL) AND
(ID_UsuarioAlta=@ID_UsuarioAlta OR @ID_UsuarioAlta IS NULL) AND
(FechaAlta=@FechaAlta OR @FechaAlta IS NULL) AND
(ID_UsuarioModifica=@ID_UsuarioModifica OR @ID_UsuarioModifica IS NULL) AND
(FechaModificacion=@FechaModificacion OR @FechaModificacion IS NULL) AND
(Eliminado <> 1)
END TRY
BEGIN CATCH
EXEC ProcMensajeError
END CATCH
END
I'm using an sqlDataSource for this but I have problems when I want to send the empty search criteria so that I return all the records in the table, this is my aspx page script:
<asp:GridView DataKeyNames="ID_Anuncio" SkinID="gridGeneral" DataSourceID="sqlAnunciosPortal"
AllowSorting="True" ID="gvAnuncios" runat="server" AutoGenerateColumns="False" CellPadding="4"
ForeColor="Black" GridLines="None" AllowPaging="True" EmptyDataText="No se encontraron resultados..."
Visible="False" PageSize="15" >
<Columns>
<asp:BoundField DataField="ID_Anuncio" SortExpression="ID_Anuncio" HeaderText="Id">
<ItemStyle Wrap="False" Width="80px" HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="Titulo" HeaderText="Titulo" SortExpression="Titulo">
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="Cuerpo" HeaderText="Cuerpo" SortExpression="Cuerpo">
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="BtnEditar" runat="server" PostBackUrl="DatosProfesion.aspx"
ImageUrl="~/Images/editar.png" AlternateText="Editar" ToolTip="Editar"
CommandName="2" CommandArgument='<%# Eval("ID_Anuncio") %>' />
</ItemTemplate>
<ItemStyle VerticalAlign="Top" />
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="BtnEliminar" runat="server" PostBackUrl="DatosProfesion.aspx"
ImageUrl="~/Images/eliminar.png" AlternateText="Eliminar" ToolTip="Eliminar"
CommandName="3" CommandArgument='<%# Eval("ID_Anuncio") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Left" />
<SelectedRowStyle Font-Bold="True" ForeColor="Black" CssClass="Green" BackColor="Yellow" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="sqlAnunciosPortal" runat="server" SelectCommand="ProcListarAnunciosPortal" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="ID_Anuncio" Type="Int32" DefaultValue="" ConvertEmptyStringToNull="false" />
<asp:Parameter Name="Titulo" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" />
<asp:Parameter Name="FechaDesde" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" />
<asp:Parameter Name="FechaHasta" Type="String" DefaultValue="" ConvertEmptyStringToNull="false" />
</SelectParameters>
</asp:SqlDataSource>
and in my codebehind:
protected void BtnSearch_Click(object sender, EventArgs e)
{
try
{
this.sqlAnunciosPortal.SelectParameters["ID_Anuncio"].DefaultValue = this.txtIdAnuncio.Text.Trim();
this.sqlAnunciosPortal.SelectParameters["Titulo"].DefaultValue = this.txtTitulo.Text.Trim();
this.sqlAnunciosPortal.SelectParameters["FechaDesde"].DefaultValue = this.txtFechaI.Text.Trim();
this.sqlAnunciosPortal.SelectParameters["FechaHasta"].DefaultValue = this.txtFechaF.Text.Trim();
this.gvAnuncios.DataBind();
this.gvAnuncios.Visible = true;
}
catch (Exception exc)
{
this.Page.ClientScript.RegisterClientScriptBlock(Page.GetType(), "Script", Util.Alert(exc.Message.Replace("\n", "").Replace("\t", "").Replace("'", ""))); }
}
}
I really do not know what I'm doing wrong, because when I try to send several parameters like this in the above code it does not show me anything in my GridView and if I leave only the first parameter that is ID_Announcement if it shows me the matching record