Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 968
don Avatar asked Oct 17 '12 10:10

don


People also ask

What will an ExecuteNonQuery return after execution?

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data. 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.

Why does ExecuteNonQuery return?

ExecuteNonQuery() returns number of rows affected(ex: 2 rows updated), so return type of ExecuteNonQuery is Integer. ExecuteScalar() is used to retrieve a single value from database, so return type of ExecuteScalar is Object. ExecuteNonQuery() method: This returns how many rows are affected by the query.

What is use of ExecuteNonQuery () method?

ExecuteNonQuery: Use this operation to execute any arbitrary SQL statements in SQL Server if you do not want any result set to be returned. You can use this operation to create database objects or change data in a database by executing UPDATE, INSERT, or DELETE statements.

What is returned from the ExecuteScalar () method?

ExecuteScalar Method. Executes the query, and returns the first column of the first row in the result set returned by the query. Extra columns or rows are ignored.


1 Answers

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

like image 157
Ahitosh Avatar answered Oct 16 '22 22:10

Ahitosh