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!
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.
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.
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.
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.
To resolve this problem just remove "SET NOCOUNT ON" or Change it to "SET NOCOUNT OFF". and everything works fine!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With