Database Update Failure.

[gahhon]

        protected void AddTransaction(object sender, EventArgs e)
        {
            SqlConnection conAddTransaction = new SqlConnection(ConfigurationManager.ConnectionStrings["connMSJ"].ConnectionString);
            SqlCommand cmdAddTransaction = new SqlCommand("UPDATE DailyBudget SET @category=@amount WHERE Username=@username", conAddTransaction);
            cmdAddTransaction.Parameters.AddWithValue("@category", "Foods");
            cmdAddTransaction.Parameters.AddWithValue("@amount", Convert.ToDouble("200"));
            cmdAddTransaction.Parameters.AddWithValue("@username", "mhingshiang");
            conAddTransaction.Open();
            cmdAddTransaction.ExecuteNonQuery();
            conAddTransaction.Close();
        }

The Foods is one of the column name in DailyBudget table.
But it can't update to the table while i testing some dummy values.

[gusano79]

You can't use parameters for column names like that. If you really want something that can adapt to different columns, you'll need to format it yourself beforehand.

Something like this:

String.Format("UPDATE DailyBudget SET {0} = @amount ...", "Foods");

Of course, be aware that this is dynamic SQL.

[gahhon]

Is Working. Thanks for advance.

This is not safe to do as it opens you to SQL injections. Use sp_executesql instead to create your dynamic SQL. Allows you to use parameterized queries. Still not foolproof, however a lot better than what was suggested.

[gusano79]

Allows you to use parameterized queries ... a lot better than what was suggested

Does sp_executesql allow you to parameterize column names? I thought it was just another way to do parameterized queries.

A properly better response would be to redesign one's table structure so you don't have to dynamically find the right column. Perhaps with a separate Category table and a corresponding column in DailyBudget.

[gahhon]

could you provide a simplest example to show how sp_executesql used?

[gahhon]

            SqlCommand cmdUpdateTransaction = new SqlCommand(String.Format("UPDATE DailyBudget SET {0}=@amount WHERE (Username=@username AND Date=@date)", txtCategory.Text), conAddTransaction);

is said my = got problem. Please advise me. Thanks a lot.

[JorgeM]

hmm.. i just tested the code in your last post and it worked for me. could it be that you have a character in txtCategory.Text that is causing the problem. Something not SQL friendly?

for example, when my SQL field had a space included in the field name, an exception was thrown. So when I named Field1 to [Field 1] in the table, it didnt work using the text "Field 1" in the text box. So it could be an unfriendly SQL character that you are trying to pass.

[gahhon]

since i am using radio button select to display on textbox by using client mode.
Thus, i change to using radionbutton selected value and solved the problem.