-2

My table Student has 4 columns ID, Name, FamilyName and Age.

I ask user to give specific ID and then one of the columns to update with query. My update query CommandText is as follows:

public void Update (OleDbConnection connection, string Name, int ID)
{
    _command.Connection = connection;
    _command.CommandText = String.Format("UPDATE Student SET Name = Name WHERE ID = ID");
    _command.ExecuteNonQuery();
}

In main() by the use of a switch I am trying to manage which column the user wants to update. like this:

Console.WriteLine("which do you want to edit, name, family name or age?");

var answer2 = Console.ReadLine().Trim().ToLower();

switch(answer2)
{
    case "name":

My question is: in my query in CommandText, is it possible to write 

UPDATE Student 
SET Name = Name || FamilyName = Name || Age = Name 
WHERE ID = ID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bbb
  • 11
  • 3
  • 2
    No, that's not valid SQL. I'm not even sure of your intent with that query. It looks like you're trying to match `Name` to any of those columns (which is still incorrect syntax), but you don't have it as part of the `WHERE` clause. – itsme86 Sep 24 '19 at 15:48
  • 1
    What is `...SET Name = Name` supposed to do? – Ňɏssa Pøngjǣrdenlarp Sep 24 '19 at 15:50
  • 1
    Also, as you have written the current query command, you are not passing the values for ID and Name. You need to use a parameterized query – Steve Sep 24 '19 at 15:50
  • maybe consider using Entity Framework where the property changes are handled automagically ? – devlin carnate Sep 24 '19 at 15:53
  • in ...SET Name = Name , the first Name is the name of a column in my database and the second Name is what user is giving as an input to update. – bbb Sep 24 '19 at 15:55
  • sorry:( I am not familiar with parameterized query and Entity framework. What comes to my mind is, writing 3 queries and call them by the 3 cases I am writing in the switch. – bbb Sep 24 '19 at 15:57
  • You should look into Expression Tree's, but a primitive solution would be to use reflection. But I would implore you to leverage one of the many tools rather than roll your own. Object Relational Mappers are a common tool and have a significant amount of domain logic behind them. – Greg Sep 24 '19 at 15:57
  • 1
    `SET Name = Name` does not update the `Name` column with the user's value. You'd need a parameter to do that. – devlin carnate Sep 24 '19 at 15:58
  • [Why do we always prefer using parameters in SQL statements](https://stackoverflow.com/questions/7505808/)? – Dour High Arch Sep 24 '19 at 15:59
  • I still don't understand why you're trying to set all 3 of those values to `Name`. Setting `Age` to `Name` doesn't seem to make any sense. Are you saying `Name` is just a variable? If so, it would be much clearer if you used something like `Value`. – itsme86 Sep 24 '19 at 21:03

1 Answers1

-1

No, that's not a correct SQL query. SET Name = Name will not update Name column like this. You need to pass the parameter value to your query.

Programming_SelfCode
  • 1
  • 2
  • 4
    This is just a rewrite of the comments above as an "answer". A very low quality answer. At least try to explain why "Name = Name" doesn't work and try to add an example of a parameterized query before writing an "answer" – Steve Sep 24 '19 at 16:47