SqlCommand.ExecuteNonQuery() does NOT update my database

Question

I am designing a database app, and have a form which populates with data from a sql database. If a user double clicks on any of the text boxes on the form they are able to change the value using an input box, which then executes the following code to update the database.

 private void ProcessChanges(string strField, string strCurrentValue)
    {
        //...Connect To Database...//

        string strCaseNo = txtCaseNo.Text;
        string strConnect = BuildConnectionString();
        SqlConnection linkToDB = new SqlConnection(strConnect);
        linkToDB.Open();

        //...Request User Input New Value...//

        string strMessage = "Enter ammended details and click OK," + Environment.NewLine +
                                "or click Cancel to exit.";
        string strInput = Interaction.InputBox(strMessage, "Case Details", strCurrentValue);

        //...Send User Input to Database...//

        string commandText = "UPDATE tblCases SET @FieldVal = @InputVal WHERE CaseNo = @CaseNoVal;";
        SqlCommand sqlCom = new SqlCommand(commandText, linkToDB);
        sqlCom.Parameters.Add("@FieldVal", SqlDbType.Text);
        sqlCom.Parameters.Add("@InputVal", SqlDbType.Text);
        sqlCom.Parameters.Add("@CaseNoVal", SqlDbType.VarChar);
        sqlCom.Parameters["@FieldVal"].Value = strField;
        sqlCom.Parameters["@InputVal"].Value = strInput;
        sqlCom.Parameters["@CaseNoVal"].Value = strCaseNo;
        int intQuery = sqlCom.ExecuteNonQuery();
        MessageBox.Show(intQuery.ToString());
    }

The problem is the database does not update at all. I know the connection is ok because the same ConnectionStringBuilder is used throughout my app. I have also added the messagebox at the end which tells me the return value of ExecuteNonQuery() which is '1', so that suggests a row has been updated. However nothing changes in my database and its really annoying me now.

Answer 1

You can't use variables for column names. You have to construct your sql string that the column names are embedded into the string.

string commandText = 
  "UPDATE tblCases SET [" + strField + "] = @InputVal WHERE CaseNo = @CaseNoVal;"

But you have to check the value of strField for sql injection attacks.

Answer 2

If you update the CommandText line as follows:

string commandText = "UPDATE tblCases SET @FieldVal = " + strField +  " WHERE CaseNo = @CaseNoVal;";

and remove the lines

sqlCom.Parameters.Add("@FieldVal", SqlDbType.Text);
sqlCom.Parameters["@FieldVal"].Value = strField;

Be aware though that by doing this you are potentially opening yourself to sql injection attacks, so you need to really trust the values being supplied into this method or do some work to make sure that any value of strField does not contain actual SQL statements.

e.g. if strField contains ;[some malicious SQL here] then this will be run with the permissions of the user assigned to the connection.