C# 使用 SqlCommand.Parameters 更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1934343/
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 Table using SqlCommand.Parameters
提问by skylerl
I'm trying to update an MSSQL table using SqlCommand, I think it's a syntax error with my T-SQL, but here is what I have so far:
我正在尝试使用 SqlCommand 更新 MSSQL 表，我认为这是我的 T-SQL 的语法错误，但这是我目前所拥有的：
SqlCommand sqlCmd = new SqlCommand("UPDATE yak_tickets SET email = @emailParam, subject = @subjectParam, text = @textParam, statusid = @statusIDParam, ticketClass = @ticketClassParam WHERE id = @ticketIDParam", sqlConn);
The parameters are working as they should, however, the table never gets updated when I run the code. Any help would be appreciated =)
参数正常工作，但是，当我运行代码时，表永远不会更新。任何帮助将不胜感激 =)
Here is the rest of the code:
下面是代码的其余部分：
#region Parameters
/* Parameters */
sqlCmd.Parameters.Add("@ticketIDParam", SqlDbType.BigInt);
sqlCmd.Parameters["@ticketIDParam"].Value = ticketID;
sqlCmd.Parameters.Add("@emailParam", SqlDbType.NVarChar);
sqlCmd.Parameters["@emailParam"].Value = ticketToBeSubmitted.getEmail();
sqlCmd.Parameters.Add("@subjectParam", SqlDbType.NVarChar);
sqlCmd.Parameters["@subjectParam"].Value = ticketToBeSubmitted.getSubject();
sqlCmd.Parameters.Add("@textParam", SqlDbType.Text);
sqlCmd.Parameters["@textParam"].Value = ticketToBeSubmitted.getTicketContent();
sqlCmd.Parameters.Add("@statusIDParam", SqlDbType.NVarChar);
sqlCmd.Parameters["@statusIDParam"].Value = ticketToBeSubmitted.getStatus();
sqlCmd.Parameters.Add("@ticketClassParam", SqlDbType.NVarChar);
sqlCmd.Parameters["@ticketClassParam"].Value = ticketToBeSubmitted.getTicketClass();
#endregion
#region Try/Catch/Finally
/* Try/Catch/Finally */
try
{
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
sqlErrorLabel.Text = sqlEx.ToString();
sqlErrorLabel.ForeColor = System.Drawing.Color.Red;
}
finally
{
sqlConn.Close();
}
And the method's signature:
以及方法的签名：
public static void updateTicketInDatabase(Ticket ticketToBeSubmitted, Label sqlErrorLabel, int ticketID)
回答by womp
UPDATE FROM is invalid syntax (edit: OP corrected this). The problem might also be the "text" column. textis a keyword in SQL Server, since it's a datatype. Try putting brackets around it.
UPDATE FROM 是无效语法（编辑：OP 更正了这一点）。问题也可能是“ text”列。 text是 SQL Server 中的关键字，因为它是一种数据类型。尝试在它周围放置括号。
UPDATE yak_tickets
SET email = @emailParam,
subject = @subjectParam,
[text] = @textParam,
statusid = @statusIDParam,
ticketClass = @ticketClassParam
WHERE id = @ticketIDParam
回答by GrayWizardx
Couple of questions:
几个问题：
- Is this inside of a transaction thats getting rolledback?
- Have you verified that you @ticketIDParam matches a set of rows on the table? Especially if its not just a integer key
- Are you updating rows that have no side effects (i.e. your updating to the same values)?
- Can you provide the paramaters.Add statements for this query
- Is there a trigger or other setting on the table (I assume not, as you did not mention anything).
- You said you know the params are working correctly, can you say how you verified this? (profiler, visual inspection, etc).
- 这是在回滚的事务中吗？
- 您是否验证过 @ticketIDParam 与表中的一组行匹配？特别是如果它不仅仅是一个整数键
- 您是否正在更新没有副作用的行（即更新为相同的值）？
- 您能否为此查询提供 paramaters.Add 语句
- 桌子上是否有触发器或其他设置（我认为没有，因为您没有提到任何内容）。
- 你说你知道参数工作正常，你能说你是如何验证的吗？（轮廓仪、目视检查等）。
回答by skylerl
Had to use if(!Page.IsPostBack)
必须使用 if(!Page.IsPostBack)
回答by Ant
Sounds like your hosting provider limits your debug options, forcing you to do it the old fashioned way. What if immediately after the update, you put something like:
听起来您的托管服务提供商限制了您的调试选项，迫使您以老式的方式进行。如果在更新后立即输入以下内容会怎样：
;SELECT @@ROWCOUNT
then instead of ExecuteNonQuery, do ExecuteScalar, and see if SQL even thinks its updated anything.
然后代替 ExecuteNonQuery，执行 ExecuteScalar，看看 SQL 是否认为它更新了任何东西。
