Asp.Net C# How to pass null value parameter to datasource

Question

I have a gridview with a parameter. It is bound to a datasource which is a SQL query. The query returns rows when executed in SQL Server when the value of the parameter is null. When I try to pass the parameter a null value via the following line of code I get no results.

FormDS.SelectParameters["ServiceFormCompletedId"].DefaultValue = Request.QueryString["sfcId"];

The querystring does not exist so it passes a null value to the FormDS Datasource. The SelectParameters said it was a null value.

So I tried giving it an empty string.

FormDS.SelectParameters["ServiceFormCompletedId"].DefaultValue = "";

with the following in the markup of ASP.NET

<asp:Parameter Name="ServiceFormCompletedId" ConvertEmptyStringToNull="true" />

There was also no results.

I tested the Datasource with the following values in the Test Query section of the Configure Data Source and got rows back.

What am I missing about passing null values to the SelectParameters of a SqlDataSource?

How to I use DbNull ? I just tried it. Can not implicity convert type 'System.DBNull' to a 'string'. I tried DbNull.ToString and that does not work as it is an empty string and that is not working. — Darren Wood
– Darren Wood, Commented Apr 4, 2017 at 4:41
The answer can be found here : Stack Overflow : pass null value to DB via sqldatasource. — Waze Android
– Waze Android, Commented Apr 4, 2017 at 6:28
Maybe You can use ternary if the string is empty / the length is 0, pass DBNull.Value, otherwise, pass your string. — toha
– toha, Commented Apr 4, 2017 at 6:39
@WazeAndroid is correct. The previous solution worked well. I initially ignored it as I could not see it working. Do you want to make it an answer for this question? — Darren Wood
– Darren Wood, Commented Apr 4, 2017 at 21:28

TylerH · Accepted Answer · 2025-08-19 20:57:11Z

5

Try this:

SqlCommand cmd = new SqlCommand("ProcedureName");
cmd.Connection = new SqlConnection("yourConnString");
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters[0] = new SqlParameter("YourParaameter", SqlDbType.NVarChar);
cmd.Parameters[0].Value = DBNull.Value;
cmd.Parameters[0].Direction = ParameterDirection.Input;           

cmd.ExecuteNonQuery(); // or any other command that you want to execute

edited Aug 19 at 20:57

TylerH

21.3k84 gold badges84 silver badges122 bronze badges

answered Apr 4, 2017 at 6:06

Vandita

7784 silver badges13 bronze badges

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

1 Comment

Darren Wood Over a year ago

Although I am sure your solution works, it was not the exact solution I was looking for. CancelSelectOnNullParameter="false" in the SqlDataSource markup of ASP.NET works.

Darren Wood · Accepted Answer · 2019-09-04 02:36:36Z

2

The solution is to put CancelSelectOnNullParameter="false" in the SqlDataSource markup like the following.

<asp:SqlDataSource ID="IDxxx" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringxxx %>" CancelSelectOnNullParameter="false" SelectCommand="SELECT * FROM Table Where @Parameter1 = Table.Column1" >
    <SelectParameters>
        <asp:Parameter Name="Parameter1" />                                        
    </SelectParameters>
</asp:SqlDataSource>

edited Sep 4, 2019 at 2:36

answered Aug 29, 2019 at 2:48

Darren Wood

1,5665 gold badges27 silver badges54 bronze badges

Collectives™ on Stack Overflow

Asp.Net C# How to pass null value parameter to datasource

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related