0
        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.

4
Contributors
8
Replies
21
Views
3 Years
Discussion Span
Last Post by gahhon
1

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.

0

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.

0

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.

0

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

0
            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.

0

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.

Edited by JorgeM

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.