Wednesday 10 February 2016

MVC - how to update just one cell in the table after double click

Watch this example on YouTube:




1. Create the following table
CREATE TABLE [dbo].[Customers](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerFirstName] [varchar](50) NOT NULL,
    [CustomerLastName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Customers] ON

GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerFirstName], [CustomerLastName]) VALUES (1, N'Bob3', N'Dylan1')
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerFirstName], [CustomerLastName]) VALUES (2, N'Oleh 1', N'Skrypka')
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerFirstName], [CustomerLastName]) VALUES (3, N'Frank', N'Sinatra')
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerFirstName], [CustomerLastName]) VALUES (4, N'John ', N'Lennon')
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerFirstName], [CustomerLastName]) VALUES (5, N'Mick ', N'Jagger')
GO
SET IDENTITY_INSERT [dbo].[Customers] OFF
GO

2. First Stored Procedure

 Create PROCEDURE [dbo].[GetAllCustomers]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Select * From Customers
END
3.  Second Stored Procedure
CREATE PROCEDURE [dbo].[GetCustomerByCustomerID]
    @CustomerID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Select * From Customers Where CustomerID = @CustomerID
END
4. Third Stored Procedure
CREATE PROCEDURE [dbo].[UpdateCustomers]
    @CustomerID int,
    @CustomerFirstName varchar(50),
    @CustomerLastName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Update Customers Set CustomerFirstName = @CustomerFirstName, CustomerLastName = @CustomerLastName
    Where CustomerID  = @CustomerID
END

5. Index.cshtml

@model IEnumerable<MvcApplication4.Models.Customer>

@{
    ViewBag.Title = "Index";
}

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            $(".CustomerFirstName").dblclick(function (e) {
                e.stopPropagation();
                var CurrentEle = $(this);
                var value = $(this).html();
                var row = $(e.target).closest('tr');
                var CustomerID = row.find($("[id*=key]")).val();
                UpdateVal(CurrentEle, value, CustomerID, "CustomerFirstName");
            });
            $(".CustomerLastName").dblclick(function (e) {
                e.stopPropagation();
                var CurrentEle = $(this);
                var value = $(this).html();
                var row = $(e.target).closest('tr');
                var CustomerID = row.find($("[id*=key]")).val();
                UpdateVal(CurrentEle, value, CustomerID, "CustomerLastName");
            });
        });
        function UpdateVal(CurrentEle, value, CustomerID, ColumnToUpdate){
            if($(".cellValue") !== undefined){
                if($(".cellValue").val() !== undefined){
                    $(".cellValue").parent().html($("#OriginalValue").val().trim());
                    $(".cellValue").remove();
                }
            }
            if(value.match("<") == null){
                $(CurrentEle).html('<div class="cellValue" id="cellWrapper"> ' +
                    '<input class="cellValue" type="text" id="txtValue" value="'+ value + '" />' +
                    '<input class="cellValue" type="hidden" value="' + CustomerID + '" id="keySelected" />' +
                    '<input class="cellValue" type="hidden" value="' + ColumnToUpdate + '" id="ColumnToUpdate" /> ' +
                    '<input class="cellValue" type="hidden" value="' + value + '" id="OriginalValue" /> ' +
                    '<input class="cellValue" type="button" value="save"   onclick="return SaveChanges()" /> ' +
                    '<input class="cellValue" type="button" value="cancel" onclick="return CancelChanges()" /> ' +
                    '</div> ');
            }
            $(".cellValue").focus();
            $(".cellValue").keyup(function(event){
                if(event.keyCode == 13){
                    $(CurrentEle).html($(".cellValue").val().trim());
                }
            });
        }

        function CancelChanges(e){
            if($(".cellValue")!== undefined){
                if($(".cellValue").val() !== undefined){
                    $(".cellValue").parent().html($("#OriginalValue").val().trim());
                    $(".cellValue").remove();
                }
            }
            window.location.reload();
        }

        function SaveChanges(e) {
            var customer = {};
            customer.ColumnToUpdate = $("[id*=ColumnToUpdate]").val();
            customer.FieldValue = $("[id*=txtValue]").val();
            customer.ID = $("[id*=keySelected]").val();
            $.ajax({
                type: "POST",
                url: "Cust/SaveCustomer",
                data: '{customer: ' + JSON.stringify(customer) + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(response){
                    alert("done!!");
                    location.href = location.href;
                    window.location.reload();
                }

            });
        }
    </script>
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th></th>
        <th>
            @Html.DisplayNameFor(model => model.CustomerFirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.CustomerLastName)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.HiddenFor(modelItem => item.CustomerID, new { id = "key"})
        </td>
        <td>
            <div class="CustomerFirstName">@Html.DisplayFor(modelItem => item.CustomerFirstName)</div>
        </td>
        <td>
            <div class="CustomerLastName">@Html.DisplayFor(modelItem => item.CustomerLastName)</div>
        </td>
 
    </tr>
}

</table>
 6. Cust Controller

    public class CustomerToUpdate
    {
        public int ID { get; set; }
        public string FieldValue { get; set; }
        public string ColumnToUpdate { get; set; }
    }
    public class CustController : Controller
    {
        private TESTEntities db = new TESTEntities();


        public void SaveCustomer(CustomerToUpdate customer)
        {
            cust upd = db.GetCustomerByCustomerID(customer.ID).First();
            switch (customer.ColumnToUpdate)
            {
                case "CustomerFirstName":
                    upd.CustomerFirstName = customer.FieldValue;
                    break;
                case "CustomerLastName":
                    upd.CustomerLastName = customer.FieldValue;
                    break;
                default:
                    break;
            }
            db.UpdateCustomers(upd.CustomerID, upd.CustomerFirstName, upd.CustomerLastName);
        }


        public ActionResult Index()
        {
            return View(db.Customers.ToList());
        }


 7. Route.config

public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Cust", action = "Index", id = UrlParameter.Optional }
            );
        }

 8. add the following to layout file
        @Scripts.Render("~/Scripts/jquery-1.8.2.js")
        @Scripts.Render("~/Scripts/jquery.unobtrusive-ajax.js")
    </head>

 

1 comment:

  1. Error ON CONTROLLER:
    There is no argument given that corresponds to the required formal parameter 'SOMETHING' of 'DBEntities.UpdatesDoc(int?, string, string, string, string, string, string, string, string)


    I validated the Entities and did step by step but this error shows up.

    Also I do not have this:
    @Scripts.Render("~/Scripts/jquery-1.8.2.js") @Scripts.Render("~/Scripts/jquery.unobtrusive-ajax.js")

    So I put this:
    @Scripts.Render("~/Scripts/jquery-1.10.2.js")
    @Scripts.Render("~/Scripts/jquery.validate.unobtrusive.js")

    ReplyDelete