ASP.NET MVC delete button

Question

When I click the delete button, I get this error.

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Reserva_Quarto". The conflict occurred in database "GestãoHotel", table "dbo.Reserva", column 'ID_Quarto'.

Does anyone know the reason?

Controller:

// GET: Quartos/Delete/5
public ActionResult Delete(int? id)
{
      if (id == null)
      {
          return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
      }

      Quarto quarto = db.Quarto.Find(id);

      if (quarto == null)
      {
           return HttpNotFound();
      }

      return View(quarto);
}

// POST: Quartos/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
     Quarto quarto = db.Quarto.Find(id);
     db.Quarto.Remove(quarto);
     db.SaveChanges();
     return RedirectToAction("Index");
}

View:

<div class="table-responsive panel">
 <table class="table">
  <tbody>
     <tr>
        <td class="text-success"><i class="fa fa-list-ol"></i> Nº Quarto</td>
        <td>@Model.ID_Quarto</td>
      </tr>
      <tr>
        <td class="text-success"><i class="fa fa-bed"></i> Tipo de Quarto</td>
        <td>@Model.TipoQuarto</td>
      </tr>
    </tbody>
   </table>
</div>

<table class="table">
 <tbody>
  <tr>
     @using (Html.BeginForm())
     {
     @Html.AntiForgeryToken()
     <td class="text-center">
      <button type="submit" class="btn btn-danger btn-circle btn-xl" data-toggle="tooltip" title="Eliminar"> <i class="glyphicon glyphicon-ok"></i></button>
 <button type="button" onclick="location.href='@Url.Action("Index", "Quartos")'" class="btn btn-primary btn-circle btn-xl" data-toggle="tooltip" title="Voltar"><i class="glyphicon glyphicon-arrow-left"></i></button>
     </td>
      }
    </tr>
   </tbody>
 </table>

Connection string:

<connectionStrings>
    <add name="Hotel" 
         connectionString="Data Source=DESKTOP-BC284NS\SQLEXPRESS;initial catalog=GestãoHotel;integrated security=True;" 
         providerName="System.Data.EntityClient" />
    <add name="HotelEntities" 
         connectionString="metadata=res://*/Models.BaseDados.GestãoHotel.csdl|res://*/Models.BaseDados.GestãoHotel.ssdl|res://*/Models.BaseDados.GestãoHotel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=DESKTOP-BC284NS\SQLEXPRESS;initial catalog=GestãoHotel;integrated security=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

when you get the error, hover over the id and check if the id is valid — Neville Nazerane
– Neville Nazerane, Commented Aug 30, 2018 at 18:49
check your migration class(es) for the fk FK_Reserva_Quarto. you may have to change the OnDelete from cascade — Neville Nazerane
– Neville Nazerane, Commented Aug 30, 2018 at 18:51
since it is a db issue, do mention that database you are using and what EF provider — Neville Nazerane
– Neville Nazerane, Commented Aug 30, 2018 at 18:52
@NevilleNazerane Thank you for trying to help me. I have already edited the post and put my connection to the database — Navas
– Navas, Commented Aug 30, 2018 at 18:54
@Navas - Like Neville said, you simply just have a foreign key constraint preventing you from doing a cascade delete. — Chris
– Chris, Commented Aug 30, 2018 at 19:10

TanvirArjel · Accepted Answer · 2018-08-30 19:27:35Z

1

You have to delete Reservas first before deleting the Quarto as follows:

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
     List<Reserva> Reservas = db.Reserva.Where(r => r.ID_Quarto == id).ToList();
     db.Reserva.RemoveRange(Reservas);

     Quarto quarto = db.Quarto.Find(id);
     db.Quarto.Remove(quarto);
     db.SaveChanges();
     return RedirectToAction("Index");
}

Moreover for permanent solution, add the following configuration in DbContext then run a migration and update the database accordingly:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);

     modelBuilder.Entity<Reserva>().HasRequired(j => j.Quarto)
                                   .WithMany(c => c.Reservas)
                                   .HasForeignKey(j => j.ID_Quarto).WillCascadeOnDelete(true);
}

Hope your problem will be solved!

edited Aug 30, 2018 at 19:27

answered Aug 30, 2018 at 19:15

TanvirArjel

32.4k14 gold badges88 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neville Nazerane Over a year ago

is this the simplest way to set a cascade from the fluent API? even if the fk has been set with data annotations?

TanvirArjel Over a year ago

Yes! this is! Even if you don't set the foreign attribute with data annotation then no problem. It will generate everything correctly.

Tomato32 · Accepted Answer · 2018-08-30 19:17:23Z

0

I think you have options for this. - Option 1: Use ON DELETE CASCADE - Option 2: Deleting In Correct Order. It means that you should delete the rows from Reserva first, then the rows from Quarto.

You can follow this sample. Hope to help, my friend :))

//Option 1:

ALTER TABLE <child_table> WITH CHECK 
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE

OPTION 2:

// POST: Quartos/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            //Delete the rows from Reserva first
            var reservaObj = db.Reserva.Where(t => t.ID_Quarto == id).ToList();
            db.Reserva.RemoveRange(reservaObj);

            Quarto quarto = db.Quarto.Find(id);
            db.Quarto.Remove(quarto);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

answered Aug 30, 2018 at 19:17

Tomato32

2,2531 gold badge12 silver badges11 bronze badges

2 Comments

Neville Nazerane Over a year ago

as long as he is using ef with migrations, it would be better to update the migration instead of running a raw query. This query by itself can be run inside a migration. That would help when migrating the database

Tomato32 Over a year ago

@NevilleNazerane: Yeah! Thank you for your comment :d

Collectives™ on Stack Overflow

ASP.NET MVC delete button

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related