ExecuteNonQuery中的SQL Update命令错误 [英] SQL Update command error in ExecuteNonQuery

问题描述

当我尝试连接到数据库以在MVC中编辑数据表时.当我尝试访问我的视图时，执行命令时出现错误.错误是:

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:'((附近的语法不正确.字词SET附近的语法不正确.

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

但是我无法弄清楚我的语法错误.我是一个初学者，所以我仍在学习基础.非常感谢您的帮助.谢谢！.这是我的代码

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);
}

推荐答案

您的代码有几个问题

1. UPDATE 的语法不正确.它应该是 UPDATE SET columnName = value ...
2. 使用参数化查询，因为目前您的代码容易受到SQL注入的攻击
3. 在 try 块内移动 myCommand.ExecuteNonQuery(); 来捕获任何异常

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

请查看我对您代码的更新:

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());
}

如评论中所述，您实际上应该在调用 UpdateDataBase()之前，在 HttpPost 方法中执行更新并验证值.

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

这篇关于ExecuteNonQuery中的SQL Update命令错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！