ExecuteNonQuery() returns 0 if insert successful in both table!
Posted by: Cheong Yi
Date: October 04, 2018 06:05AM

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

Options: ReplyQuote


Subject
Written By
Posted
ExecuteNonQuery() returns 0 if insert successful in both table!
October 04, 2018 06:05AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.