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.

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.

Is Working. Thanks for advance.

Member Avatar for stbuchok

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.

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.

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

            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.

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.