Skip setting value of sql parameter in SqlDataSource in WebForm

Question

I am making a WebForm in asp.net and I want to display gridview with data from asp:SqlDataSource
My problem becomes when I try to skip (pass null) values for some of the Parameters.

Here is some snippets of my code

In aspx file sqldatasource looks like

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" 
        SelectCommand="art_bat_art" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter Name="art_naz" DefaultValue="HLA"  ConvertEmptyStringToNull="true" Type="String" />
            <asp:Parameter Name="art_sifra"     DbType="String"  Direction="Input"/>
            <asp:Parameter Name="vrsta_id" DefaultValue="3"   ConvertEmptyStringToNull="true" Type="Int32" />     
            <asp:Parameter Name="podvrsta_id" DefaultValue="13"   ConvertEmptyStringToNull="true" 
                Type="Int32"  />
            <asp:Parameter Name="podgrupa2"  DefaultValue="1365"   ConvertEmptyStringToNull="true" Type="Int32"      /> 
            <asp:Parameter Name="podosobine"   DefaultValue="1:9241"  ConvertEmptyStringToNull="true" 
                Type="String"  />
            <asp:Parameter Name="podosobineOR" DefaultValue="true"   ConvertEmptyStringToNull="true" 
                Type="Boolean"  />
        </SelectParameters>

My grid looks like this:

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="id" DataSourceID="SqlDataSource1">

And troubles comes in code behind One of parameters for my stored procedure is optional, If i pass it as null in SQL the stored procedure is going to execute anyway.

I am trying to skip and pass that parameter as follows

public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {


        }
        protected void Button1_Click1(object sender, EventArgs e)
        {
            SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = DBNull.Value; //Cannot implicitly convert type 
            SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = System.Data.SqlTypes.SqlString.Null;   //Cannot implicitly convert type 
            SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = System.Data.SqlTypes.SqlString.Null.ToString();   //IT compiles but i get NULL as text in SQL
            //IF I Just skip seting of this parametar, My SqlDataSource won't call stored proce 
            SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = string.Empty; //Again no execution of stored proce
            //Even if I call SqlDataSource1.DataBind(); //nothing cames to sql 
            GridView1.DataBind();
        }
    }

JumpingJezza · Accepted Answer · 2011-09-08 05:09:44Z

6

You'll have to edit your SQL (or stored proc) to be able to handle an optional parameter.

ie: if you have:
SELECT blah FROM yourTable WHERE art_sifra = @art_sifra

change it to:
SELECT blah FROM yourTable WHERE (art_sifra = @art_sifra OR @art_sifra = '')

and then just change ConvertEmptyStringToNull="false" and set your select parameter to "": SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = "";

Also need to set CancelSelectOnNullParameter="false" on your datasource.

answered Sep 8, 2011 at 5:09

JumpingJezza

5,66013 gold badges75 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

openshac · Accepted Answer · 2011-03-24 10:44:57Z

1

DBNull.Value; //Cannot implicitly convert type

Have you tried casting it? E.g.

DBNull.Value.Tostring()

answered Mar 24, 2011 at 10:44

openshac

5,1936 gold badges48 silver badges83 bronze badges

2 Comments

adopilot Over a year ago

SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = DBNull.Value.ToString(); Do not call stored procedure at all. I do not get any error but I also do not get any data

openshac Over a year ago

@adopilot: Do you want to post your stored procedure so we can check that that NULL parameters are being handled correctly?

Collectives™ on Stack Overflow

Skip setting value of sql parameter in SqlDataSource in WebForm

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related