Why AddWithValue when is dcd_first_name SQL Server Profile gives me default? and when store procedure execute crash the execution

Question

I have this code:

var SQL ="SP_PROC";

SqlCommand cmd = new SqlCommand(SQL, connection)
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@dcd_first_name", name);
cmd ExecuteNonQuery();

Why does AddWithValue when is dcd_first_name SQL Server Profile gives me default? And when stored procedure executes, it crashes the execution.

Profile gives me: EXEC SP_PROC default

I thought when I pass a null in parameter, in this case name automatically AddWithValue convert to DBNull.Value. I have to specify it.

Is it the best method to use AddWithValue the parameter?

Just noticed you didn't do `cmd.CommandType = CommandType.StoredProcedure;` — DavidG, Sep 04 '14 at 16:19
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Sep 04 '14 at 16:49
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Sep 04 '14 at 16:50

score 1 · Answer 1 · answered Sep 04 '14 at 16:21

1

You haven't set the command type to be a stored procedure. After you instantiate the cmd object, add this line:

cmd.CommandType = CommandType.StoredProcedure;

answered Sep 04 '14 at 16:21

DavidG

113,891
12
217
223

I forgot to add here sorry – Dave Sep 04 '14 at 16:23
And it is not the answer I am looking for, I need some why no code correction – Dave Sep 04 '14 at 16:24

score 1 · Accepted Answer · edited May 23 '17 at 10:26

AddWithValue doesn't automatically convert from null to DBNull.Value.

From the MSDN documentation on AddWithValue(string parameterName, Object value):

value - The value to be added. Use DBNull.Value instead of null, to indicate a null value.

Changing the call to the following will fix it:

cmd.Parameters.AddWithValue("@dcd_first_name", 
                             name == null ? DBNull.Value : (object)name);

Edit

In the comments you ask if AddWithValue is the best way of adding parameters. In my opinion the answer to that is "no".

As per the comment from @marc_s:

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results..

For an example of problems that can occur see this SO question - c# SQL Server : DATETIME datatype

My prefered approach is to use the overload of Add that takes the SqlDbType and then set the Value property afterwards:

cmd.Parameters.Add("@dcd_first_name", SqlDbType.VarChar)
              .Value = name == null ? DBNull.Value : (object)name;

It is what I did as you indicate in your answer, but I hadsome curiosity to ask. It was I imagined. Anyway,is it the best method to set parameters? — Dave, Sep 05 '14 at 07:36

Why AddWithValue when is dcd_first_name SQL Server Profile gives me default? and when store procedure execute crash the execution

2 Answers2