As the title says, i want to know if a stored procedure can use the query to get values from a datagrid cell value on the selected row.
Stored procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `entradas_sai`(
IN ID_VEICULO VARCHAR(45), OUT retcode INT)
BEGIN
DECLARE _rollback BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
START TRANSACTION;
UPDATE entradas SET SAI=1 WHERE id_veiculo='"+ Grid1.SelectedRows.Cells[i].Value +"' AND SAI=0;
//More 2 queries here but those are fine.
IF '_rollback' THEN
SET retcode = 0;
ROLLBACK;
ELSE
SET retcode = 1;
COMMIT;
END IF;
END
EDIT:
New Procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `entradas_sai`(
IN ID_VEICULO VARCHAR(45), OUT retcode INT)
BEGIN
DECLARE _rollback BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _rollback = 1;
START TRANSACTION;
SELECT * FROM entradas WHERE id_veiculo= "@id_veiculo";
INSERT INTO entradas(datasai, horasai) VALUES(date(now()) ,time(now()));
UPDATE entradas SET SAI=1 WHERE id_veiculo="@idveiculo" AND SAI=0;
IF '_rollback' THEN
SET retcode = 0;
ROLLBACK;
ELSE
SET retcode = 1;
COMMIT;
END IF;
END
c# snippet:
private void cmdSaida_Click(object sender, EventArgs e)
{
using (var cn = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
{
cn.Open();
if (Grid1.SelectedRows.Count > 0)
{
MySqlCommand cmd = new MySqlCommand("entradas_sai", cn);
cmd.CommandText = "entradas_sai";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@retcode", MySqlDbType.Int32);
cmd.Parameters["@retcode"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@id_veiculo", Grid1.SelectedCells[1].Value);
cmd.ExecuteNonQuery();
var res = cmd.Parameters["@retcode"].Value;
}
cn.Close();
}
Grid1.Refresh();
}
What I have tried:
Tried this query on my stored procedure.
SqlQuery:
SELECT * FROM `entradas` WHERE id_veiculo= '"+ Grid1.SelectedRows.Cells[i].Value +"';