Syntax Error while passing numeric column name in my update query of sql server in c#

Question

I am recently work on project in which I can add column dynamically with the DateTime now and this code works fine; but when I want to update this doesn't work fine and throw syntax error and when ever I write character column name it works fine. Below is my code

cmd = new System.Data.SqlClient.SqlCommand(
 "update attendance set '25.04.2017' =  '" + 
  abcd + 
 "'   where std_id = " + Convert.ToInt32(b) + "  ", con);

cmd.ExecuteNonQuery();
con.Close();

where abcd is my column values which is true or false the issue with this '25.04.2017'

add column dyanmically with the Data time now - seriously now, don't do that! — Jamiec
– Jamiec, Commented Apr 21, 2017 at 11:13

Dmitrii Bychenko · Accepted Answer · 2017-04-21 11:46:02Z

1

Probably, you should swap field name (abcd) and its value (25.04.2017):

// Keep query 
//   1. Readable:     @
//   2. Paramterized: @prm_date, @prm_id
//   3. Formattable:  {abcd} (string interpolation)
string sql = 
  $@"update attendance
        set {abcd} = @prm_date -- field = value
      where std_id = @prm_id";

//DONE: wrap IDisposable into using
using (var cmd = System.Data.SqlClient.SqlCommand(sql, con)) {
  // Parametrize query
  //TODO: AddWithValue is not the best choice, turn into Add + Actual db type
  cmd.Parameters.AddWithValue("@prm_date", new DateTime(2017, 4, 25));
  cmd.Parameters.AddWithValue("@prm_id", b);

  cmd.ExecuteNonQuery();
}

Edit: In (for me very improbable) case that '25.04.2017' is a field name and abcd is its value:

string sql = 
  $@"update attendance
        set [25.04.2017] = @prm_abcd -- eerie field with '25.04.2017' name
      where std_id = @prm_id";

//DONE: wrap IDisposable into using
using (var cmd = System.Data.SqlClient.SqlCommand(sql, con)) {
  // Parametrize query
  //TODO: AddWithValue is not the best choice, turn into Add + Actual db type
  cmd.Parameters.AddWithValue("@prm_abcd", abcd);
  cmd.Parameters.AddWithValue("@prm_id", b);

  cmd.ExecuteNonQuery();
}

edited Apr 21, 2017 at 11:46

answered Apr 21, 2017 at 11:15

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

1 Comment

Dmitrii Bychenko Over a year ago

@Andy Korneyev: I've re-read the question once again, and, yes, I shall confess, you can well be right; but what a weird choice of the field's name! I've edited the answer.

Andrey Korneyev · Accepted Answer · 2017-04-21 11:13:40Z

0

If you really have such a column named 25.04.2017 (I'd personally not recommend to do it) - you have to wrap such a name into square brackets:

"update attendance set [25.04.2017] =  '"

answered Apr 21, 2017 at 11:13

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Comments

Graham · Accepted Answer · 2017-09-22 02:08:41Z

0

If 25.04.2017 is a column name then you want to surround it with []

update attendance set [25.04.2017] =  ....

Your code is vulnerable to sql injection when you append your parameter values as part of the sql string. Use Parameters instead for your values that you are passing.

Also your schema is not well designed any time you start using (what should be) values for columns. Instead I recommend a table with a column AttendedOn of type DateTime2 and capture the value in there. It is very rare that you should ever have to dynamically create columns based on values and if that is the path you are on you should reconsider your design.

edited Sep 22, 2017 at 2:08

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Apr 21, 2017 at 11:13

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Collectives™ on Stack Overflow

Syntax Error while passing numeric column name in my update query of sql server in c#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related