I want to delete a row in my database with the @Html.ActionLink Delete but its not working. What am I doing wrong?

Question

It can write out the cv.name, it also connects to the database. But I'm doing something wrong with a delete action.

cshtml:

<body>   
@{
    foreach( var cv in Model.GetCVs( username)) 
    {
        <h3 style="color: #193367; "><b> Name: @cv.name</b></h3>     // this works
        <form action="" method="post">
            <input type="submit" name="name" value="@cv.name" />     // this works
        </form>
        @Html.ActionLink("Delete","Delete",new{ id = @cv.id})        // this does not work
    }
}
</body>

The markup above is in my cshtml file and the most of it is working all right.

cshtml.cs:

[BindProperty]
public int id { get; set; }

public const string vv = "Data Source=Tables.sqlite3";

public static SqliteConnection Connection = new SqliteConnection(vv);

static AboutModel()
{
    Connection.Open();
}

public struct CV
{
    public string name;
    public int id;
}

public ActionResult Delete(int? id)
{
    using (var cmd = Connection.CreateCommand()) 
    {
        cmd.CommandText = "Delete from MyTable1 where id = @id;";
        return RedirectToPage("About");                 
    }                   
}

This is the code in my cshtml.cs and the Delete method is not working. I don't know how to properly use this delete action. I don't want that it asks back that are you sure want to delete it? I just want that if the user click on the delete button, it should delete that row in a table.

I think you have a problem with @id command parameter. Did you debug Delete action? — Eugene D, May 30 '20 at 14:00

pinkfloydx33 · Answer 1 · 2020-05-30T14:44:17.763

You have two problems. First, you need to add a parameter to your command to correspond to the @id in the command text. Second, and more importantly, you need to actually execute the command. Right now you are just creating a new DbCommand, assigning the CommandText property and then discarding it.

public ActionResult Delete(int? id)
{
    // handle bad request (id is null) somehow
    if(!ModelState.IsValid) 
        return View();

    using (var cmd = Connection.CreateCommand()) 
    { 
        cmd.CommandText = "Delete from MyTable1 where id = @id";

        // set command type Text (or StoredProcedure) 
        // Text is the default so not strictly necessary 
        cmd.CommandType = CommandType.Text; 

        // create new parameter
        var para = cmd.CreateParameter();
        para.Value = id.Value;
        para.ParameterName = "@id";
        para.DbType = DbType.Int32;

        // add parameter to command
        cmd.Parameters.Add(para);

        // need to make sure connection is open
        if (Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken) 
            Connection.Open();

        // execute command
        cmd.ExecuteNonQuery();
    } 

    return RedirectToPage("About");
}

Side note: you really shouldn't hold your connection object in a static field or property. You should create, open, close/dispose as close as possible to where/when the connection is actually needed. Since this is a web application, you are going to get yourself into trouble if you do it this way.

public ActionResult Delete(int? id)
{
    // handle bad request (id is null) somehow
    if(!ModelState.IsValid) 
        return View();

    // create connection when we need it
    using (var conn = new SqliteConnection(vv)) 
    using (var cmd = conn.CreateCommand()) 
    { 
        cmd.CommandText = "Delete from MyTable1 where id = @id";

        // set command type Text (or StoredProcedure) 
        // Text is the default so not strictly necessary 
        cmd.CommandType = CommandType.Text; 

        // create new parameter
        var para = cmd.CreateParameter();
        para.Value = id.Value;
        para.ParameterName = "@id";
        para.DbType = DbType.Int32;

        // add parameter to command
        cmd.Parameters.Add(para);

        // open the connection 
        conn.Open();

        // execute command
        cmd.ExecuteNonQuery();
    } 

    return RedirectToPage("About");
}

I want to delete a row in my database with the @Html.ActionLink Delete but its not working. What am I doing wrong?

1 Answers1