SQL Update command error in ExecuteNonQuery

Answer 06/17/2020 Developer FAQ 6

<h3>Question</h3>

When i try to connect to my database to Edit a datatable in MVC. When I try to acces to my view i have an error when I execute my command. the error is:

System.Data.SqlClient.SqlException: 'incorrect syntax near ('. incorrect syntax near the kewword SET.

</blockquote>

but i can not figure out my syntax errors. I am a Beginer so i am still learning the basis. It would be really grateful for any help. Thanks!. here is my code

private void UpdateDataBase(int EmailId, string userName, string title, string Email, string description)
{
    var sqlstring = string.Format("UPDATE Email (Email, Description, UserName, Title) " +
        "SET ('{0}',  '{1}',  '{2}',  '{3}')", Email, description, userName, title +
        "WHERE ID=" + EmailId);

    var myConnection = getconection();
    SqlCommand myCommand = new SqlCommand(sqlstring, myConnection);
    myCommand.ExecuteNonQuery();

    try
    {
        myConnection.Close();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
}

public ActionResult Edit (int EmailId, string userName, string title, string Email, string description)
{          
    UpdateDataBase(EmailId, userName, title, Email, description);

    return View("EmailData");
}

[HttpPost]
public ActionResult Edit (ModelTemplateEmail  EditEmailData)
{       
    if (ModelState.IsValid)
    {                
      return RedirectToAction("EmailData");
    };
    return View(EditEmailData);
}

<h3>Answer1:</h3>

There are a couple of problems with your code

<ol><li>The syntax for UPDATE is incorrect. It should be UPDATE SET columnName = value...</li> <li>Use parameterised queries, because at the moment your code is vulnerable to SQL injection</li> <li>Move myCommand.ExecuteNonQuery(); inside the try block to catch any exceptions</li> </ol>

Please see my update to your code:

var sqlstring = @"UPDATE Email SET Email = @email, Description = @description, UserName = @username, Title = @title WHERE ID = @id");

var myConnection = getconection();
SqlCommand myCommand = new SqlCommand(sqlstring, myConnection);

// add parameters
myCommand.Parameters.AddWithValue("@email", email);
myCommand.Parameters.AddWithValue("@description", description);
myCommand.Parameters.AddWithValue("@username", userName);
myCommand.Parameters.AddWithValue("@title", title);
myCommand.Parameters.AddWithValue("@id", emailId);

try
{
    // execute the command in the try block to catch any exceptions
    myCommand.ExecuteNonQuery();
    myConnection.Close();
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

As mentioned in the comments, you should really perform the update in the HttpPost method and validate the values before calling UpdateDataBase().

来源：https://stackoverflow.com/questions/60811442/sql-update-command-error-in-executenonquery