I’m trying to make a rollback of my Querys only to give me a mistake in Executescalar

Question

The code works perfectly without the 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 mistake.

Executescalar requires the command to have a transaction when the link assigned to the command is in a local pending transaction. The Transaction property of the command has not been initialized.

Answer 1

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

Note that one of the constructors receives three parameters: the SQL string, connection and transaction.

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

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