Edit and Delete sql data in ASP.NET

Question

I am facing a bit of problem editing and deleting rows from SQL in ASP.NET MVC. The main problem I am facing is the fetch the QueryString value from url in my controller.

I can't attach Request.QueryString["Username"] for some reason. It is giving me Can not apply indexing with [] to an expression of type QueryString error.

Is there something I am doing it wrong or is there any way I can pass the QueryParameter through my controller to my Data access layer, where it will execute the desired query.

I am having the same problem with both Edit and Delete

This is my code looks like so far:

ShowUsers.cshtml (Edit and Delete actions are hooked up here)

@model IEnumerable<SecureMedi.Models.Users>

<section id="users">
    <table id="users-table" class="table table-bordered table-responsive">
        <thead>
            <tr>
                <th class="sort">Username</th>
                <th class="sort">Role</th>
                <th class="sort"><i class="md-icon dp18">mode_edit</i></th>
                <th class="sort"><i class="md-icon dp18">delete</i></th>
            </tr>
        </thead>
        <tbody class="list">
            @foreach (var item in Model) {
            <tr>
                <td class="username">@item.Username</td>
                <td class="role">@item.Role</td>
                <td class="actions">@Html.ActionLink("Edit", "EditUser", "Home", new { Username = @item.Username }, null)</td>
                <td class="actions">@Html.ActionLink("Delete", "DeleteUser", "Home", new { Username = @item.Username }, null)</td>
            </tr>
            }
        </tbody>
    </table>
</section>

EditUser.cshtml (For Editing) - Note: Here I would also like to display the Username value by default from the QueryString (Username=somename) in the form when the page loads. When going to EditUser action from ShowUsers page the url looks something like /Home/EditUser?Username=somename.

@model SecureMedi.Models.Users


<section id="edit-user">
    <h4 class="sub-title space-top space-bottom">Edit user</h4>
    <form id="edit-user-form" asp-controller="Home" asp-action="EditUser" method="post">
        <div class="form-group">
            <label asp-for="Username" class="form-control-label">Username</label>
            <input asp-for="Username" type="text" class="form-control" id="username" name="Username" required disabled value="" />
        </div><!-- / form-group -->
        <div class="form-group">
            <label asp-for="Role">Role</label>
            <select asp-for="Role" class="form-control selector" id="role" name="Role" required>
                <option value="" selected disabled>Select role</option>
                <option value="SecureMediUsers">SecureMediUsers</option>
                <option value="SecureMediModerators">SecureMediModerators</option>
                <option value="SecureMediAdministrators">SecureMediAdministrators</option>
            </select>
        </div><!-- / form-group -->
        <button type="submit" class="btn btn-primary">Save</button>
    </form><!-- / form -->
</section>

HomeController.cs

[HttpGet]
public IActionResult EditUser() {
    var model = new Users();
    return View(model);
}

[HttpPost]
public IActionResult EditUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Edit(u);
    return RedirectToAction("ShowUsers");
}

[HttpPost]
public IActionResult DeleteUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Delete(u);
    return RedirectToAction("ShowUsers");
}

UsersDAL.cs (Data access layer)

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using SecureMedi.Models;

namespace SecureMedi.DAL {
    public class UsersDAL {
        public void Edit(Users u) {
            string connectionstring = "MY_CONNECTION_STRING";
            string sql = String.Format("ALTER ROLE {0} DROP MEMBER {1}", u.Role, u.Username);
            string sql2 = String.Format("ALTER ROLE {0} ADD MEMBER {1}", u.Role, u.Username);
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlCommand cmd2 = new SqlCommand(sql2, conn);

            try {
                conn.Open();
                using(conn) {
                    cmd.Transaction = conn.BeginTransaction();
                    cmd.ExecuteNonQuery();
                    cmd2.Transaction = cmd.Transaction;
                    cmd2.ExecuteNonQuery();
                    cmd2.Transaction.Commit();
                }
            } finally {
                if (conn != null) {
                    conn.Close();
                }
            }

        }

        public void Delete(Users u) {
            string connectionstring = "MY_CONNECTION_STRING";
            string sql = String.Format("DROP USER {0}", u.Username);
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand(sql, conn);

            try {
                conn.Open();
                using(conn) {
                    cmd.ExecuteNonQuery();
                }
            } finally {
                if (conn != null) {
                    conn.Close();
                }
            }
        }
    }
}

Users.cs (Model)

using System.ComponentModel.DataAnnotations;

namespace SecureMedi.Models {
    public class Users {
        [Required]
        [StringLength(100)]
        public string Username {
            get;
            set;
        }

        [Required]
        [StringLength(100)]
        public string Role {
            get;
            set;
        }
    }
}

In summary, the problems I am trying to solve are:

1) Passing the Username query param from EditUser view and Role value from the form to my EditUser controller function.

2) I have hooked up the delete action like

<td class="actions">@Html.ActionLink("Delete", "DeleteUser", "Home", new { Username = @item.Username }, null)</td>

But I'm confused on to how to avoid the [HttpGet] for DeleteUser action. When I click on the anchor tag it is taking me to /Home/DeleteUser?Username=somename, but instead I would just like to execute the DeleteUser action without the view change.

Also, how do I pass Username query param to the controller action.

1) "Passing the Username query param from EditUser view and Role value from the form to my EditUser controller function." I don't understand. Aren't these values already properties of u, the instance of Users which is created from the data posted back in your form? The username passed in the original query parameter (which is gone when you post back) should be the same as the one submitted via the form. However, you disabled the controls, and disabled form controls are not submitted - you should just use a hidden field instead for this. You can display the username with @Model.Username — ADyson
– ADyson, Commented Jan 19, 2018 at 14:50
@EhsanUllahNazir Yeah I missed that. I have updated the model code now. — 0xburned
– 0xburned, Commented Jan 19, 2018 at 14:52
continuation of 1) above - of course your EditUsers GET method would need to accept the username parameter and use that to fetch the correct user record from the db first. Currently it doesn't do this at all, it just creates an empty user object. — ADyson
– ADyson, Commented Jan 19, 2018 at 14:56

MikkaRin · Accepted Answer · 2018-01-19 14:56:23Z

1

1) Change [HttpGet] EditUser to take UserName parameter: and get user by UserName to send this data to model:

[HttpGet]
    public IActionResult EditUser(string UserName) {
        var model = new Users(UserName);//probably, Or something like GetUser(UserName)
        return View(model);
    }

2) You can't call HTTPPost method by using actionLink. It will always use HttpGet. So there is two approaches that you can use - 1. Delete user in HttpGet and than redirect to previous page 2. Use JavaScript to call HttpPost, instead of send HttpGet by action link clicking.

edited Jan 19, 2018 at 14:56

answered Jan 19, 2018 at 14:54

MikkaRin

3,08422 silver badges35 bronze badges

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

3 Comments

0xburned Over a year ago

This line won't work var model = new Users(UserName); since the default Users model contains 2 args giving me the error Users does not contain a constructor that takes 1 arguments. How should I change mu model in this case?

Horkrine Over a year ago

It should be something like var model = new User() { Username = userName }; where the value userName is your string parameter to the method

0xburned Over a year ago

@Mikkarin @Horkrine That solves the problem with [HttpGet] call in both Edit and Delete page (I have created a delete confirmation page where I am making [HttpGet] on delete.) However, [HttpPost] is not working for both Edit and Delete since the action is not fetching Username values from string param in [HttpPost] calls.

Syed Muhammad Munis Ali · Accepted Answer · 2018-01-20 09:03:10Z

You Just Want to Delete the recor form database on just click on the delete link without confirmation user should be navigated to the same page .? these changes will do this trick for you ! tried

Update your Home Controller Like : i just remove the [httppost] from delete actionResult and change the parameter name and also write the controller name also in your return redirecttoaction

IMP NOTE : Update The Paramete Users To Username where ever u use it like in DAL Also Suggestion is to use the same one everywhere either it would be null

HomeController.cs

[HttpGet]
public IActionResult EditUser() {
    var model = new Users();
    return View(model);
}

[HttpPost]
public IActionResult EditUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Edit(u);
    return RedirectToAction("ShowUsers", "ControllerName");
}

public IActionResult DeleteUser(Username u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Delete(u);
    return RedirectToAction("ShowUsers", "ControllerName");
}

Collectives™ on Stack Overflow

Edit and Delete sql data in ASP.NET

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related