CODEWITHSUNDEEP
c#sqlsql-servervisual-studio-2010stored-procedures
don
don

Reputation: 597

ExecuteNonQuery() returns -1 when execute the stored procedure

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

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!

Upvotes: 13

Views: 39618

Answers (3)

Ahitosh
Ahitosh

Reputation: 99

To resolve this problem just remove "SET NOCOUNT ON" or Change it to "SET NOCOUNT OFF". and everything works fine!

Upvotes: 10

podiluska
podiluska

Reputation: 51494

Don't use = null, use is null

IF @existPerson is null

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)

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

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

Upvotes: 17

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Let's have a look at the documentation for 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.

You're calling a stored procedure, which, in and of itself, is none of the 3 listed statements where a row count is returned.

If you want to determine the value that was passed to a return statement within the stored procedure, you need to add another parameter to the command, and set its Direction property to ReturnValue (the name you give to this parameter will be ignored)

Upvotes: 13

Related Questions