Skip sql parameter value in SqlDataSource in WebForm

Question

I am doing a webform in asp.net and I want to display a gridview with data from asp: SqlDataSource
My problem occurs when I try to skip (pass null) values ​​for some parameters.

Here are some snippets of my code

In an 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 is as follows:

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

And problems arise in the code. One of the parameters for my stored procedure is optional; if I pass it as null in SQL, the stored procedure will still be executed.

I am trying to skip this 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(); } } 

Answer 1

You will need to edit your SQL (or saved proc) to be able to handle the optional parameter.

i.e.: 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 the selection parameter to "" : SqlDataSource1.SelectParameters["art_sifra"].DefaultValue = "";

You must also set CancelSelectOnNullParameter="false" to your data source.

Answer 2

 DBNull.Value; //Cannot implicitly convert type 

Have you tried pouring it? For instance.

 DBNull.Value.Tostring()