Pass C# variable and use it as a Column Value in SQL Statement Query

Question

I need to use the value of a variable as one of my fields in my SQL insert query that I am generating. In PHP I do this:

if($currency = "USD")
{
 $columntoinsert = "USD_Currency";
} 
else
{
 $columntoinsert = "";
}

Then in my SQL statement I would execute the query like so:

$sqlinsertpayment = sqlsrv_query($connect,"INSERT INTO Payments_TBL(column1,column2,$columntoinsert,columnn)VALUES(????)",$params) or die("An error occurred trying to execute the statement");

I want to do the same in C#. I have the following code to set the column I want to use

var amountfield = "";
  if (save_currency != "USD")
    {
      amountcolumn = "Amount";

     }
  else
   {
      amountcolumn = "USD_Amount";
   }

But I can't execute my sql query in since trying to use amountcolumn in the query string generates an Invalid column name error which is expected. How can I use the variable in this manner:

var sql = new sqlCommand("INSERT INTO Payments_TBL(column1,column2,myc#variablevaluehere,column3,....)Values(@value,@value,@value)",new sqlConnection(conn));

Open to other alternatives. All help will be appreciated.

I don't think specifying columns in parameters will work to be used in the query. — Maxi
– Maxi, Commented Oct 5, 2012 at 13:07

tpeczek · Accepted Answer · 2012-10-05 12:47:33Z

8

Because the value for amountfield comes from your code logic, you can safely do this:

var sql = new sqlCommand(String.Format("INSERT INTO Payments_TBL(column1,column2,{0},column3,....)Values(@value,@value,@value)", amountcolumn),new sqlConnection(conn));

But never do things like this with strings coming from your users because that would make you open for SQL injection.

answered Oct 5, 2012 at 12:47

tpeczek

24.2k3 gold badges78 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maxi Over a year ago

Thanks dude. Worked like a charm. Learned something new today.

Maxi Over a year ago

Actually the value does not come from the user directly but from the database and it was checked for any xss vulnerabilities or sql injection before saving.

Nikhil Agrawal · Accepted Answer · 2012-10-05 13:15:11Z

3

Its

var amountfield = save_currency != "USD" ? "Amount" : "USD_Amount";

var sql = new sqlCommand("INSERT INTO Payments_TBL(column1,column2," 
+  amountcolumn + 
",column3,....)Values(@value,@value,@value)",new sqlConnection(conn));

OR

var sql = new sqlCommand(
                   String.Format("INSERT INTO Payments_TBL(column1,column2,  
                   {0},column3,....)Values(@value,@value,@value)", amountfield), 
                   new sqlConnection(conn));

edited Oct 5, 2012 at 13:15

answered Oct 5, 2012 at 12:46

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

Collectives™ on Stack Overflow

Pass C# variable and use it as a Column Value in SQL Statement Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related