You are passing in a value of "ID_VEICULO" but not using it anywhere.
If
entradas.id_veiculo
is an integer column then your line
SELECT * FROM 'entradas' WHERE id_veiculo= "@id_veiculo";
will generate an error. I'm not sure of the MySQL error but in SQL Server this would be
Quote:
Conversion failed when converting the varchar value 'asdkfjasldj' to data type int.
Because of the error the exception handler will be fired and _rollback will have been set to 0. Did you debug and check the return code from
var res = cmd.Parameters["@retcode"].Value;
In your stored procedure avoid calling the parameters the same name as the column and actually use them rather than the string "@id_veiculo" ... I think it is unlikely that that string is actually how you are identifying every single vehicle in the table.
Try changing the stored procedure like this
CREATE DEFINER='root'@'localhost' PROCEDURE 'entradas_sai'(
IN IDTOCHECK VARCHAR(45), OUT retcode INT)
BEGIN
DECLARE _rollback BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
START TRANSACTION;
INSERT INTO `entradas`(datasai, horasai) VALUES(date(now()) ,time(now()));
UPDATE entradas SET SAI=1 WHERE id_veiculo=IDTOCHECK AND SAI=0;
IF '_rollback' THEN
SET retcode = 0;
ROLLBACK;
ELSE
SET retcode = 1;
COMMIT;
END IF;
END
When problems happen with stored procedures try running parts of the sql directly in MySQL (or at a push use
SQL Fiddle[
^]). Then you would have spotted the problem in the SELECT.
Also learn to use the Visual Studio debugger -
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[
^]
As a general rule of thumb, if something doesn't do what you expected then check the values that are being returned ... in this case I believe
res==0