can't commit or rollback, MySQL out of sync error on .net
- by sergiogx
Im having trouble with a stored procedure, I can't commit after I execute it.
Its showing this error "[System.Data.Odbc.OdbcException] = {"ERROR [HY000] [MySQL][ODBC 5.1 Driver]Commands out of sync; you can't run this command now"}"
The SP by itself works fine.
does anyone have idea of what might be happening?
.net code:
[WebMethod()]
[SoapHeader("sesion")]
public Boolean aceptarTasaCero(int idMunicipio, double valor)
{
Boolean resultado = false;
OdbcConnection cxn = new OdbcConnection();
cxn.ConnectionString = ConfigurationManager.ConnectionStrings["mysqlConnection"].ConnectionString;
cxn.Open();
OdbcCommand cmd = new OdbcCommand("call aceptarTasaCero(?,?)", cxn);
cmd.Parameters.Add("idMunicipio", OdbcType.Int).Value = idMunicipio;
cmd.Parameters.Add("valor", OdbcType.Double).Value = valor;
cmd.Transaction = cxn.BeginTransaction();
try
{
OdbcDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
resultado = Convert.ToBoolean(dr[0]);
}
cmd.Transaction.Commit();
}
catch (Exception ex)
{
ExBi.log(ex, sesion.idUsuario);
cmd.Transaction.Rollback();
}
finally
{
cxn.Close();
}
return resultado;
}
and this is the code for the stored procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS `aceptartasacero` $$
CREATE DEFINER=`database`@`%` PROCEDURE `aceptartasacero`(pidMun INTEGER, pvalor double)
BEGIN
declare vExito BOOLEAN;
INSERT INTO tasacero(anio,valor,idmunicipios)
VALUES(YEAR(curdate()),pValor,pidMun);
set vExito = true;
select vExito;
END $$
DELIMITER ;
thanks.
