ExecuteNonQuery() returns 0 if insert successful in both table!

[Cheong Yi]

I think nothing's wrong with the connection because when I open it, it does not throw any error.

So I guess the error is when I'm executing a command through the store procedure.

From google ExecuteNonQuery() will return the row than affected during insert, update.

But my scenario will always get 0 if it is successful or not successful. Please correct me anything I'm wrong or misunderstand .

Please also suggest and the best way to achieve the result insert into two table. Thanks you


using (MySqlCommand cmd = new MySqlCommand("sp_insertlogin", sqlConn))
{
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@infristname", objsr.fristName);
cmd.Parameters.AddWithValue("@insurname", objsr.surName);
cmd.Parameters.AddWithValue("@inemailAddress", objsr.email);
cmd.Parameters.AddWithValue("@inphoneNumber", objsr.phone);
cmd.Parameters.AddWithValue("@inprofileImg", objsr.profileImg);
cmd.Parameters.AddWithValue("@inuserPass", objsr.password);

cmd.Parameters.Add(new MySqlParameter("id", MySqlDbType.Int32));
cmd.Parameters["id"].Direction = ParameterDirection.Output;

cmd.Parameters.Add(new MySqlParameter("userExits", MySqlDbType.Int32));
cmd.Parameters["userExits"].Direction = ParameterDirection.Output;

result = cmd.ExecuteNonQuery();

var outval = cmd.Parameters["@id"].Value;
var userExits = cmd.Parameters["@userExits"].Value;

if ((int)userExits == 2)
return 2;


return result;
}

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insertlogin`(
IN infristName VARCHAR(300),
IN insurname VARCHAR(300),
IN inemailAddress VARCHAR(500),
IN inphoneNumber INT(11),
IN inprofileImg VARCHAR(300),
IN inuserPass VARCHAR(2000),
OUT id INT(11) ,
OUT userExits INT(11)
)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION rollback;
DECLARE EXIT HANDLER FOR NOT FOUND rollback;
DECLARE EXIT HANDLER FOR SQLWARNING rollback;


START TRANSACTION;

IF EXISTS (SELECT emailAddress FROM user_access.user_account where emailAddress = inemailAddress) THEN
SET userExits = 2;
SET id = 0;
ELSE
SET userExits = 0;
INSERT INTO user_access.user_account
(
fristName ,
surname ,
emailAddress ,
phoneNumber ,
profileImg
)
VALUES
(
infristname ,
insurname ,
inemailAddress ,
inphoneNumber ,
inprofileImg
);
SET id = LAST_INSERT_ID();


INSERT INTO user_access.user_profile
(
userAccount ,
password
)
VALUES
(
id ,
inuserPass
);

COMMIT;


END IF;

END