CODEWITHSUNDEEP
c#asp.netparametersnull
Nils
Nils

Reputation: 514

cmd.Parameters.AddWithValue() when value is null?

i would think this is simple but is there a way of using Isnull(field,[something]) when value is null?

/*************Connectionstring is located in Web.config ******************/
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

/*************Exec stored Procedure from tblActivity ******************/
using (SqlConnection con = new SqlConnection(CS))
{
    SqlCommand cmd = new SqlCommand("sp_tblActivity", con);
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Activity", txtActivity.Text); // , Isnull(txtActivity.text, *changetosomething*)
    cmd.Parameters.AddWithValue("@Comment", txtComment.Text);
    con.Open();
    cmd.ExecuteNonQuery();
    BindGridviewActivity();


}

cmd.Parameters.AddWithValue("@Activity", isnull(txtActivity.Text, ***[dosomething]***) is this possible to do insted of using a if() statement in the begining?

Upvotes: 3

Views: 2854

Answers (3)

Chris Catignani
Chris Catignani

Reputation: 5306

I used a method extension for DBNULL...

public static class Extensions
{
    public static SqlParameter AddWithNullValue(this SqlParameterCollection collection, string parameterName, object value)
    {
        if (value == null)
            return collection.AddWithValue(parameterName, DBNull.Value);
        else
            return collection.AddWithValue(parameterName, value);
    }

}

So...instead of using AddWithValue....you would use AddWithNullValue

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

First of all, you should probably move away from using AddWithValue() in the first place.

Setting that aside for the moment, you're looking at a Control's Text property. Something has to go really wrong for a Text property to ever result in null. You probably just need to check if it's empty (or maybe only has whitespace) instead:

cmd.Parameters.AddWithValue("@Activity", string.IsNullOrEmpty(txtActivity.Text) ? "change to something" : txtActivity.Text);

But maybe you want to send an actual SQL NULL to the database. SQL NULL is different from a C#/.Net null. In that case, you could do this:

cmd.Parameters.AddWithValue("@Activity", string.IsNullOrEmpty(txtActivity.Text) ? DBNull.Value : txtActivity.Text);

Upvotes: 4

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

You can use the null-coalescing operator:

cmd.Parameters.AddWithValue("@Activity", txtActivity.Text ?? "something else");

Upvotes: 1

Related Questions