C# sqlserver stored procedure parameters

Question

I have a stored procedure that has an update statement. It takes 3 parameters: ID,Status,Date

I have written a C# program to call this procedure. If the status is -1, I want the date to be null in the table and if status is 1, date should be current date.

Int32 rowsAffected = 0;
string datenow =null;

using (SqlConnection connection = new    SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString))
{
    connection.Open();
    SqlCommand cmd = new SqlCommand(
        "usp_UpdateProc", connection);

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@status", SqlDbType.Int);
    cmd.Parameters["@status"].Value = status;
    if(status != 1)
        datenow = DateTime.Now.ToString(@"MM\/dd\/yyyy h\:mm tt");

    cmd.Parameters.AddWithValue("@datenow", @datenow);
    cmd.Parameters.Add(
        new SqlParameter("@ID", @ID));

    try
    {
        rowsAffected = cmd.ExecuteNonQuery();
    }
    catch (Exception ep)
    {  //throw ep
    }

When I do this status and date fields are both nulls in Database. I am not sure why this is happening? Thanks Rashmi

I assume the @ in the second parameter of cmd.Parameters.AddWithValue("@datenow"... is a typo? — pquest
– pquest, Commented Feb 18, 2014 at 19:57
" I am not sure why this is happening" maybe not swallowing the exception would give you some insight? — D Stanley
– D Stanley, Commented Feb 18, 2014 at 19:58
@pquest that should be fine - you can add a @ to the front of a c# variable name - it's used to use reserved words as variable names (like @class) — D Stanley
– D Stanley, Commented Feb 18, 2014 at 19:59
Where are you setting status? If it's null, then datenow will be null, and that's your answer. — D Stanley
– D Stanley, Commented Feb 18, 2014 at 20:02
So you are passing a parameter of type string when the database expects a datetime. I bet that if you add a message box to your exception handler to see your exception message you will get a rather clear message — Steve
– Steve, Commented Feb 18, 2014 at 20:16

Larry · Accepted Answer · 2014-02-18 20:52:32Z

I see some issues in your code:

The test is wrong and need to be fixed so a NULL is used as @datenow value if status is -1
I am not sure about the DateNow which is passed as a string. What dont you use the native .NET DateTime value ?
The results highly depends on the way the stored procedure is implemented.

Please do not put empty try/catches and display the exception instead so you can better see what is going wrong.

I suggest this:

SqlCommand cmd = new SqlCommand("usp_UpdateProc", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@status", status);
if(status == -1)
    cmd.Parameters.AddWithValue("@datenow", DBNull.Value);
else
    cmd.Parameters.AddWithValue("@datenow", DateTime.Now.ToString(@"MM\/dd\/yyyy h\:mm tt"));

cmd.Parameters.AddWithValue("@ID", @ID);

try
{
    rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ep)
{ 
    MessageBox.Show(ep.ToString());
}

pquest · Accepted Answer · 2014-02-19 14:39:40Z

1

I would try to assign the values like this instead and see if it makes a difference:

SqlParameter stat = cmd.Parameters.AddWithValue("@status" ,status);
stat.dbType = SqlDbType.Int;

DateTime? dt = (status == -1)? null : DateTime.Now;

SqlParameter dateParam = cmd.Parameters.AddWithValues("@datenow", dt ?? DBNull.Value);
dateParam.dbType = SqlDbType.DateTime;

edited Feb 19, 2014 at 14:39

answered Feb 18, 2014 at 20:45

pquest

3,3003 gold badges30 silver badges41 bronze badges

1 Comment

Larry Over a year ago

I am sorry : this will crash because the AddWithValues does not support a .NET "null" as parameter. I suggest to put .AddWithValues("@datenow, dt ?? DBNull.Value); instead.

Collectives™ on Stack Overflow

C# sqlserver stored procedure parameters

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related