pass several sqlDataSource parameters to a stored procedure

1

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

    
asked by Ivxn 15.11.2017 в 08:00
source

2 answers

0

The solution was that the data was not being sent as null as required by the SP, just place a condition when the data is received in the code behind

            this.sqlAnunciosPortal.SelectParameters["Titulo"].DefaultValue = txtTitulo.Text.Trim() == "" ? null : txtTitulo.Text.Trim();
            this.sqlAnunciosPortal.SelectParameters["FechaDesde"].DefaultValue = txtFechaI.Text.Trim() == "" ? null : txtFechaI.Text.Trim();
            this.sqlAnunciosPortal.SelectParameters["FechaHasta"].DefaultValue = txtFechaF.Text.Trim() == "" ? null : txtFechaF.Text.Trim();
            this.sqlAnunciosPortal.CancelSelectOnNullParameter = false;
    
answered by 16.11.2017 / 22:18
source
0

It's a bit complex to follow everything, but I suspect that your problem is because of the parameters FechaDesde and FechaHasta . Your stored procedure expects DATETIME , and you are passing strings.

First, you must change the type of your parameters:

<asp:Parameter Name="FechaDesde" Type="DateTime"/>
<asp:Parameter Name="FechaHasta" Type="DateTime"/>

And then convert your dates to DateTime by giving value to the parameters:

this.sqlAnunciosPortal.SelectParameters["FechaDesde"].DefaultValue = DateTime.Parse(this.txtFechaI.Text.Trim());
this.sqlAnunciosPortal.SelectParameters["FechaHasta"].DefaultValue = DateTime.Parse(this.txtFechaF.Text.Trim());
    
answered by 15.11.2017 в 09:40