Call to Stored Procedure not updating as expected

Question

I have a mystery with a stored procedure that I'm calling from code behind(C#). I am baffled because I have added watchpoints my code on the C# side and everything seems to be having the values that they should be going into the call to the stored procedure however, the procedure runs without any errors that I can tell and yet my table doesn't get updated with the values that I feel they should.

The SP gets three values passed to it.

Record ID (@Record_ID), Column to update (@UpdColumn), and the value to place in that column (@UpdValue).

Here is my SP that I am calling:

ALTER PROCEDURE [dbo].[Single_Col_Update] 
-- Add the parameters for the stored procedure here
@Record_ID  INT,
@UpdColumn  CHAR,
@UpdValue   NVARCHAR
AS
BEGIN
SET NOCOUNT ON;

IF @UpdColumn = 'TicketNumber' 
    UPDATE dbo.csr_refdata_ip360_HostVulnerabilityCSV
    SET TicketNumber = @UpdValue
    WHERE RecID = @Record_ID;

IF @UpdColumn = 'TicketClosed'
    UPDATE dbo.csr_refdata_ip360_HostVulnerabilityCSV
    SET TicketClosed = @UpdValue
    WHERE RecID = @Record_ID;

IF @UpdColumn = 'Notes'
    UPDATE dbo.csr_refdata_ip360_HostVulnerabilityCSV
    SET Notes = @UpdValue
    WHERE RecID = @Record_ID;

IF @UpdColumn = 'Exception_ID'
    UPDATE dbo.csr_refdata_ip360_HostVulnerabilityCSV
    SET ExceptionID = @UpdValue
    WHERE RecID = @Record_ID;
END

Here is the code segment calling the SP:

foreach (string record in recordnumber)
{
SqlConnection con = new SqlConnection("Data Source=MyDataSource");
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Single_Col_Update";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.AddWithValue("@Record_ID", Convert.ToInt32(record));
cmd.Parameters.AddWithValue("@UpdColumn", Session["UpdColumn"]);
cmd.Parameters.AddWithValue("@UpdValue", Session["UpdValue"]);

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

Since all the variables are right, I'm not sure why this isn't updating. Hoping some of you may see an error here.

UPDATED 5/19/2017 1:40PM Central - Steve, I attempted to implement the call as you prescribed below. I only made to variations to what you provided:

1. 'cmd.Parameters.Add("@UpdValue", SqlDbType.NVarChar, 1024);' // instead of 255 because the column I'm feeding there is an NVarChar(MAX) I will likely have to go back and modify this to be greater than 1024. There didn't appear to be a MAX value that I could put in there so for testing the 1024 will suffice.
2. omitted the 'transaction.Rollback();' // I kept red lining on the word 'transaction' and despite what I tried I couldn't get it to validate it.

Bottom line is that after implementing the code below the results were exactly the same as before. The code executed without reporting any errors either via the Consol.Write I added or through the VS 2017 IDE.

SqlTransaction transaction;
try
{
    using (SqlConnection con = new SqlConnection("Data Source=MyDataSource"))
    using (SqlCommand cmd = new SqlCommand("Single_Col_Update", con))
    {
        con.Open();
        transaction = con.BeginTransaction();
        cmd.Transaction = transaction;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Record_ID", SqlDbType.Int);
        cmd.Parameters.Add("@UpdColumn", SqlDbType.NVarChar, 255);
        cmd.Parameters.Add("@UpdValue", SqlDbType.NVarChar, 1024);

        foreach (string record in recordnumber)
            {
                cmd.Parameters["@Record_ID"].Value = Convert.ToInt32(record);
                cmd.Parameters["@UpdColumn"].Value = Session["UpdColumn"].ToString();
                cmd.Parameters["@UpdValue"].Value = Session["UpdValue"].ToString();
                cmd.ExecuteNonQuery();
            }

        transaction.Commit();
    }
}
catch (Exception ex)
{
    Console.Write(ex.ToString());
}

So I'm still where I was, but I have taken notice of what you shared and I concur with all you stated. I hadn't noticed that I was opening and closing the connection there and was not aware of other things you had shared.

However the quandary remains!

Update 05/22/2017 10:45AM Central time: I realized that I was trying to stuff NVarchar type into to a Varchar type in my stored procedure. Once corrected the modifications that I made based on Steve's feedback worked just fine. I haven't tried it but I'm assuming that what I had to begin with would have worked if the types had matched to begin with, but Steve's example is cleaner so I am not even going back to test the old way. Thanks again Steve!

Answer 1

CHAR should only be used when a column is a fixed length. When you use it with varying length strings, the results will be usually not what you expect because the parameter/column will be padded with spaces which is why your IF statements are failing.

Don't use the CHAR type for @UpdColumn. Use NVARCHAR instead for this column and also it's a good practice to specify a length for both this parameter and the UpdValue parameter in your stored procedure and then match this closely when calling the stored procedure from your C# code.

Answer 2

The problem is in the declaration of this parameter

@UpdColumn  CHAR,

in this way the Stored Procedure expects a SINGLE char, not a string.
Thus all the following if statements are false and nothing will be updated

Change it to

@UpdColumn  NVARCHAR(255)

The same is true for the @UpdValue parameter. Again, only a single char is received by the stored procedure. Doesn't matter if you pass a whole string.
If you don't specify the size of the NVARCHAR or CHAR parameters the database engine will use only the first char of the passed value.

I want also to underline the comment above from Alex K. While it should not give you a lot of gain it is preferable to open the connection and create the command with the parameters outside the loop. Inside the loop just change the parameters values and execute the sp

SqlTransaction transaction;
try
{
    using(SqlConnection con = new SqlConnection(.....))
    using(SqlCommand cmd = new SqlCommand("Single_Col_Update", con))
    {
        con.Open();
        transaction = con.BeginTransaction())
        cmd.Transaction = transaction;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Record_ID", SqlDbType.Int);
        cmd.Parameters.Add("@UpdColumn", SqlDbType.NVarChar, 255);
        cmd.Parameters.Add("@UpdValue", SqlDbType.NVarChar, 255);
        foreach (string record in recordnumber)
        {
            cmd.Parameters["@Record_ID"].Value = Convert.ToInt32(record));
            cmd.Parameters["@UpdColumn"].Value = Session["UpdColumn"].ToString();
            cmd.Parameters["@UpdValue"].Value = Session["UpdValue"].ToString();
            cmd.ExecuteNonQuery();
        }
        transaction.Commit();
    }
}
catch(Exception ex)
{
     // show a message to your users
     transaction.Rollback();
}

I have also added all your loop inside a transaction to confirm all the inserts as a whole or reject all in case of errors.