I am trying to do a rollback of my querys only that gives me an error in ExecuteScalar

Question

I am trying to do a rollback of my querys only that gives me an error in ExecuteScalar

Navigation

#1 by (4 votes)

2

The code works perfectly without btw rollback.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString);
con.Open();
SqlTransaction tran = con.BeginTransaction();

try
{
    //Adicionar Titular query----------------------------------------------------------------------------------------------------------------------------------

    SqlCommand cmd2 = new SqlCommand("INSERT INTO Titular(nifTitular,nifEntidade,nome,emailTitular,contacto)" +
    "VALUES(@nifTitular,@nifEntidade,@nome,@emailTitular,@contacto)" + "SELECT SCOPE_IDENTITY()", con);
    cmd2.Parameters.AddWithValue("@nifTitular", Request.Form["nif"]);
    cmd2.Parameters.AddWithValue("@nifEntidade", Request.Form["nife"]);
    cmd2.Parameters.AddWithValue("@nome", Request.Form["nome"]);
    cmd2.Parameters.AddWithValue("@emailtitular", Request.Form["email"]);
    cmd2.Parameters.AddWithValue("@contacto", Request.Form["contacto"]);

    //iNewRowIdentity passa a ter o ultimo idTitular inserido
    int iNewRowIdentityTit = Convert.ToInt32(cmd2.ExecuteScalar());
    tran.Commit();
    ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Registo feito com sucesso')", true);

}
catch (SqlException exception)

{
    tran.Rollback();
    Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>");

}
finally
{
    con.Close();
}

Give me this error.

ExecuteScalar requires the command to have a transaction when the binding assigned to the command is in a pending local transaction. The Transaction property of the command was not initialized.

c#

asked by anonymous 20.08.2018 / 13:49

1 answer

Javascript / Jquery - Calculation of days between two dates "DD / MM / YYYY" [duplicate] Select dates that are not in the table

score 4 · Accepted Answer

The error says that the command (instance of SqlCommand ) needs an associated transaction.

See that one of the constructors gets three parameters : the SQL string, the connection and the transaction.

What you need is to change the instantiation of the command to use the transaction.

SqlCommand cmd2 = new SqlCommand("Insert (...)", con, tran);