执行存储过程时 ExecuteNonQuery() 返回 -1 [英] ExecuteNonQuery() returns -1 when execute the stored procedure
问题描述
我正在尝试在 Visual Studio 中执行存储过程.下面给出.
I'm trying to execute stored procedure in Visual Studio. Its given below.
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());
但它返回-1.我通过输入从调试中捕获的相同值来尝试这个存储过程.它成功了.可能的错误是什么?非常感谢!
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!
推荐答案
不要使用= null
,使用is null
IF @existPerson is null
当你比较任何东西 = null
时,结果总是假的(除非你有 set ansi_nulls off
,你不应该这样做,因为这样的选项已被弃用)
When you compare anything = null
the result is always false (unless you have set ansi_nulls off
, which you shouldn't, as such an option is deprecated)
更好的是,您可以使用
IF NOT EXISTS (SELECT p_ID FROM Student WHERE s_NIC = @nic)
另外,你应该使用 SCOPE_IDENTITY()
而不是 SET @currentID = (SELECT MAX( p_ID) FROM Person);
Also, you should use SCOPE_IDENTITY()
instead of 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;
然后
MessageBox.Show(retValue.Value);
这篇关于执行存储过程时 ExecuteNonQuery() 返回 -1的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!