C# 更新 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19723545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# update SQL Server table
提问by
I've tried to write an updatestatement that will update some information in my SQL Server table
我试图写一个update语句来更新我的 SQL Server 表中的一些信息
Here is my code so far, I can't see the issue.
到目前为止，这是我的代码，我看不到问题。
protected void Page_Load(object sender, EventArgs e)
{
FirstNameEdit.Text = Session["FirstName"].ToString();
LastNameEdit.Text = Session["LastName"].ToString();
}
protected void SubmitEdit_Click(object sender, EventArgs e)
{
if (FirstNameEdit.Text == "")
{
StatusMessage.Text = "Indtast venligst dit fornavn. ";
}
else
{
if (LastNameEdit.Text == "")
{
StatusMessage.Text = "Indtast venligst dit efternavn. ";
}
else
{
try
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Break;Integrated Security=True");
SqlCommand command = new SqlCommand("SELECT * FROM Users", connection);
command.Connection.Open();
string querystr = "UPDATE Users SET User_FirstName='@User_FirstName', User_LastName='@User_LastName' WHERE User_ID='@User_ID'";
SqlCommand query = new SqlCommand(querystr, connection);
string User_ID = Session["ID"].ToString();
string User_FirstName = FirstNameEdit.Text;
string User_LastName = LastNameEdit.Text;
query.Parameters.Add("@User_ID", User_ID);
query.Parameters.Add("@User_FirstName", User_FirstName);
query.Parameters.Add("@User_LastName", User_LastName);
query.ExecuteNonQuery();
string FirstName = FirstNameEdit.Text;
Session.Add("FirstName", FirstName);
string LastName = LastNameEdit.Text;
Session.Add("LastName", LastName);
StatusMessage.Text = "Din profil er opdateret";
command.Connection.Close();
}
catch
{
StatusMessage.Text = "Noget er galt, pr?v lidt senere";
}
}
}
}
I have also searched a lot, but it's exactly the same I find. Maybe it's something about the SQL query.
我也搜索了很多，但我发现的完全一样。也许它是关于 SQL 查询的。
采纳答案by rcs
What is the issue exactly?
究竟是什么问题？
I think when specifying the parameter using parameterized query, you don't need to use quote anymore, hence
我认为在使用参数化查询指定参数时，您不再需要使用引号，因此
string querystr = "UPDATE Users SET User_FirstName='@User_FirstName', User_LastName='@User_LastName' WHERE User_ID='@User_ID'";
should become
应该成为
string querystr = "UPDATE Users SET User_FirstName=@User_FirstName, User_LastName=@User_LastName WHERE User_ID=@User_ID";
回答by Steve
Don't enclose the parameters placeholders with single quotes. In that way they becomes simply string literals and your query can't work
不要用单引号将参数占位符括起来。这样，它们就变成了简单的字符串文字，而您的查询将无法工作
string querystr = "UPDATE Users SET User_FirstName=@User_FirstName, " +
"User_LastName=@User_LastName WHERE User_ID=@User_ID";
Another thing to consider. Are you sure that your User_ID field is a string (text, nvarchar, varchar) field in the database?. If it is a numeric field then you need to convert the value passed as parameter for that field to a numeric value (I.E. Use Convert.ToInt32)
另一件事要考虑。您确定您的 User_ID 字段是数据库中的字符串（文本、nvarchar、varchar）字段吗？。如果它是一个数字字段，那么您需要将作为该字段的参数传递的值转换为数字值（IE 使用 Convert.ToInt32）
So, if this is true, the code could be changed as
所以，如果这是真的，代码可以更改为
string User_ID = Session["ID"].ToString();
string User_FirstName = FirstNameEdit.Text;
string User_LastName = LastNameEdit.Text;
query.Parameters.AddWithValue("@User_ID", Convert.ToInt32(User_ID));
query.Parameters.AddWithValue("@User_FirstName", User_FirstName);
query.Parameters.AddWithValue("@User_LastName", User_LastName);
query.ExecuteNonQuery();
I have used the AddWithValue following the recommendation in MSDN that says
我按照 MSDN 中的建议使用了 AddWithValue
AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.
AddWithValue 替换了采用 String 和 Object 的 SqlParameterCollection.Add 方法。接受字符串和对象的 Add 重载已被弃用，因为 SqlParameterCollection.Add 重载采用字符串和 SqlDbType 枚举值可能存在歧义，其中通过字符串传递整数可以被解释为参数值或对应的 SqlDbType 值。每当您想通过指定名称和值来添加参数时，请使用 AddWithValue。
However, the advantage in the implicit conversion it's also the disadvantage since the conversion may not be optimal. I can give as reference this very thorough article
但是，隐式转换的优点也是缺点，因为转换可能不是最佳的。我可以参考这篇非常详尽的文章
Another note to keep in mind. Do not swallow exceptions. In your code, the generic message that advise the user of a failure is not enough to let you understand what's going wrong. You should replace your code with
要记住的另一个注意事项。不要吞下异常。在您的代码中，提示用户失败的通用消息不足以让您了解发生了什么问题。您应该将代码替换为
try
{
......
}
catch(Exception ex)
{
// I let you translate... :-)
StatusMessage.Text = "Noget er galt, pr?v lidt senere " +
"Error message=" + ex.Message;
}
回答by Aun JeeRut
string queryStr = "UPDATE SET USER (account, user, password, permission_level)" +
"VALUES ('" + tbxAccount.Text + "', '" + tbxUsername.Text +
"', '" + tbxPassword.Text + "', '" + tbxPermission.Text + "');";
回答by Khazratbek
First of all, as other people said - don't use single quota in the query, you don't need that.
首先，正如其他人所说 - 不要在查询中使用单个配额，你不需要那个。
Secondly: when you are defining Parametersto your command, you may use two variants:
其次：当您为命令定义参数时，您可以使用两种变体：
1) query.Parameters.AddWithValue("@User_FirstName", User_FirstName);
1) query.Parameters.AddWithValue("@User_FirstName", User_FirstName);
or
或者
2) query.Parameters.Add("@User_FirstName", SqlDbType.VarChar).Value = User_FirstName;
2) query.Parameters.Add("@User_FirstName", SqlDbType.VarChar).Value = User_FirstName;
You can't use query.Parameters.Add("@User_FirstName", User_FirstName);
你不能用 query.Parameters.Add("@User_FirstName", User_FirstName);
