C# 执行存储过程时 ExecuteNonQuery() 返回 -1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12932471/
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
ExecuteNonQuery() returns -1 when execute the stored procedure
提问by don
I'm trying to execute stored procedure in Visual Studio. Its given below.
我正在尝试在 Visual Studio 中执行存储过程。下面给出。
CREATE PROCEDURE [dbo].[addStudent]
@stuName varchar(50),
@address varchar(100),
@tel varchar(15),
@etel varchar(15),
@nic varchar (10),
@dob date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @currentID INT
DECLARE @existPerson INT
SET @existPerson = (SELECT p_ID FROM Student WHERE s_NIC = @nic);
IF @existPerson = null
BEGIN
INSERT INTO Person (p_Name, p_RegDate, p_Address, p_Tel, p_EmergeNo, p_Valid, p_Userlevel)
VALUES (@stuName, GETDATE(), @address, @tel, @etel, 0, 'Student' );
SET @currentID = (SELECT MAX( p_ID) FROM Person);
INSERT INTO Student (p_ID, s_Barcode, s_DOB, s_NIC) VALUES (@currentID , NULL, @dob, @nic);
return 0;
END
ELSE
return -1;
END
Im doing so by using this code below.
我通过使用下面的代码来这样做。
SqlConnection con = new SqlConnection();
Connect conn = new Connect();
con = conn.getConnected();
con.Open();
cmd = new SqlCommand("addStudent", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@stuName", SqlDbType.VarChar).Value = nameTxt.Text.ToString();
cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = addressTxt.Text.ToString();
cmd.Parameters.Add("@tel", SqlDbType.VarChar).Value = telTxt.Text.ToString();
cmd.Parameters.Add("@etel", SqlDbType.VarChar).Value = emerTxt.Text.ToString();
cmd.Parameters.Add("@nic", SqlDbType.VarChar).Value = nicTxt.Text.ToString();
cmd.Parameters.Add("@dob", SqlDbType.DateTime).Value = dobTime.Value.ToString("MM-dd-yyyy");
int n = cmd.ExecuteNonQuery();
MessageBox.Show(n.ToString());
But it returns me -1. I tried this stored procedure by entering the same values I captured from debugging. It was successful. What can be the possible error? Thanks a lot!
但它返回我-1。我通过输入从调试中捕获的相同值来尝试这个存储过程。它成功了。可能的错误是什么？非常感谢！
采纳答案by podiluska
Don't use = null, use is null
不要用= null，用is null
IF @existPerson is null
When you compare anything = nullthe result is always false (unless you have set ansi_nulls off, which you shouldn't, as such an option is deprecated)
当你比较任何东西时= null，结果总是错误的（除非你有set ansi_nulls off，你不应该这样做，因为这样的选项已被弃用）
Even better, you can use
更好的是，您可以使用
IF NOT EXISTS (SELECT p_ID FROM Student WHERE s_NIC = @nic)
Also, you should use SCOPE_IDENTITY()instead of SET @currentID = (SELECT MAX( p_ID) FROM Person);
另外，您应该使用SCOPE_IDENTITY()代替SET @currentID = (SELECT MAX( p_ID) FROM Person);
SET @currentID = SCOPE_IDENTITY()
Finally you also need to add a parameter to collect the return value
最后还需要添加一个参数来收集返回值
SqlParameter retValue = cmd.Parameters.Add("return", SqlDbType.Int);
retValue.Direction = ParameterDirection.ReturnValue;
then
然后
MessageBox.Show(retValue.Value);
回答by Damien_The_Unbeliever
Let's have a look at the documentationfor ExecuteNonQuery:
让我们来看看文档为ExecuteNonQuery：
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the return value is -1.
对于 UPDATE、INSERT 和 DELETE 语句，返回值是受命令影响的行数。...对于所有其他类型的语句，返回值为 -1。
You're calling a stored procedure, which, in and of itself, is none of the 3 listed statements where a row count is returned.
您正在调用一个存储过程，它本身并不是返回行数的 3 个列出的语句中的任何一个。
If you want to determine the value that was passed to a returnstatement within the stored procedure, you need to add another parameter to the command, and set its Directionproperty to ReturnValue(the name you give to this parameter will be ignored)
如果要确定return存储过程中传递给语句的值，则需要向命令添加另一个参数，并将其Direction属性设置为ReturnValue（您为此参数指定的名称将被忽略）
回答by Ahitosh
To resolve this problem just remove "SET NOCOUNT ON" or Change it to "SET NOCOUNT OFF". and everything works fine!
要解决此问题，只需删除“SET NOCOUNT ON”或将其更改为“SET NOCOUNT OFF”。一切正常！
