sqlrazorwebmatrix

Delete entry from table and database in webmatrix/razor


I have populated a table with info from my database, but I need another column where I can delete an entry out of the table and out of the database.

I've started to create a delete statement, but I don't know what I need to delete just a single entry.

Here's my code so far:

@{
var db = Database.Open("StayInFlorida");

var sPropertyId = Request.QueryString["PropertyID"];
var roominfo = "SELECT * FROM RoomInfo WHERE PropertyID=@0";
var qroominfo = db.Query(roominfo, sPropertyId);
var droominfo = "DELETE ??? FROM RoomInfo"

if (IsPost){
    var deletesingleroom = db.Query(droominfo, sPropertyId);
}
}

<!DOCTYPE html>

<html lang="en">
<head>
    <meta charset="utf-8" />
    <title></title>
    <link href="~/css/bootstrap.css" rel="stylesheet">
</head>
<body>
    
    <div id="tab2" class="tab-pane">
<h4>Room Information</h4>
<table class="table table-bordered">
<thead>
<tr>
  <th>Room Title</th>
  <th>Room Type</th>
  <th>Room Description</th>
  <th>Delete Room</th>           
</tr>
</thead>
<tbody>
@foreach (var row in qroominfo)
{
<tr>
<td>@row.RoomTitle</td>
<td>@row.RoomType</td>
<td>@row.RoomDescription</td>
<td>
<form method="post">
<button type="submit" class="btn btn-success">Delete</button
</form>
</td>
</tr>
}
</tbody>
</table>
</div>

</body>
</html>

Solution

  • I think you have a problem with the delete statement in SQL in general, not particulary in Razor context.

    The syntax for a delete statement is

    DELETE FROM RoomInfo WHERE <some condition>
    

    If you simply send this query :

    DELETE FROM RoomInfo
    

    This will delete all your rows.
    So for deleting only one row from your POST result, it is kind of the same as the select :

    DELETE FROM RoomInfo WHERE PropertyID=@0
    

    Edit :

    Now that you have a unique identifier that identifie the room you want to delete, you can update your code :

    First, link the Id of your room to your delete button :

    <form method="post">
        <input type="text" name="id-room" value="@row.RoomID">
        <button type="submit" class="btn btn-success">Delete</button
    </form>
    

    And when you submit the delete form, retrieve the room Id to delete :

    var sRoomId = Request.QueryString["id-room"];
    var droominfo = "DELETE FROM RoomInfo WHERE RoomId = " + sRoomId
    

    Note that this code is terrible and will have a lot of security holes.
    You are not supposed to access the database in a razor file for example. You are working like if you were in old fashioned PHP.
    Try to inform yourself about ASP.NET MVC.