CODEWITHSUNDEEP
c#sql
PJW
PJW

Reputation: 5417

SqlCommand.ExecuteNonQuery() does NOT update my database

I am designing a database app, and have a form which populates with data from a 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.

Upvotes: 3

Views: 8303

Answers (3)

Bobbles
Bobbles

Reputation: 737

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.

Upvotes: 1

Rich O'Kelly
Rich O'Kelly

Reputation: 41767

@Jan has it. But as an aside you really should be disposing or closing your SqlConnection, from MSDN:

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is actually closed.

The using construct is present in C# for just such a thing:

using (SqlConnection linkToDB = new SqlConnection(strConnect)
{
  // use the linkToDb here
}

Upvotes: 0

Jan
Jan

Reputation: 16048

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.

Upvotes: 8

Related Questions