can't commit or rollback, MySQL out of sync error on .net

Posted by sergiogx on Stack Overflow See other posts from Stack Overflow or by sergiogx
Published on 2010-05-19T23:47:40Z Indexed on 2010/05/19 23:50 UTC
Read the original article Hit count: 408

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about .NET