locked
How to make calculation inside an Edit Form: Jquery RRS feed

  • Question

  • User1984354752 posted

    Hi there:

    I have a Jquery Edit Form with   4 text boxes  coming obviously from a grid. I want before submitting the values  to the database to sum the values of textboxes 1,2  and 3  into the third fourth( calculated field)  while the Edit form is still open . I can achieve the calculation when I post the values and refresh the grid but I want to do this calculation while the user is busy populating the Edit Form so he can see the result before posting the changes when he/she closes the Edit Form. 

    I know this require  a function but don't know how to refer the HTML components of the Jquery Edit Form?

    Thanking you in  advance.

    Friday, May 8, 2020 2:41 PM

Answers

  • User-1330468790 posted

    Hi,  9peculiar

     

    Thank you for your reply. For the issues you encounter, you can refer to the following example:

    Foreground code:

    <head runat="server">
    
        <title></title>
    
        <link type="text/css" href="Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
    
        <script src="Scripts/jquery-3.4.1.js"></script>
    
        <script src="Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
    
        <script src="Scripts/jquery.jqGrid.js" type="text/javascript"></script>
    
        <script type="text/javascript"> 
    
            function sum() {
    
                $('input[id*="txt4"]').attr("readOnly", true);
    
                $('input[id*="txt"]').on("change", function () {
    
                    var txt1 = $('input[id*="txt1"]').val();
    
                    var txt2 = $('input[id*="txt2"]').val();
    
                    var txt3 = $('input[id*="txt3"]').val();
    
                    var value = Number(txt1) + Number(txt2) + Number(txt3);
    
                    $('input[id*="txt4"]').val(value);
    
                });
    
            }
    
            $(function () {
    
                $("#dataGrid").jqGrid({
    
                    url: 'Test.aspx/GetDataFromDB',
    
                    datatype: 'json',
    
                    mtype: 'POST',
    
                    serializeGridData: function (postData) {
    
                        return JSON.stringify(postData);
    
                    },
    
                    gridComplete: function () {
    
                        var ids = jQuery("#dataGrid").jqGrid('getDataIDs');
    
                        for (var i = 0; i < ids.length; i++) {
    
                            var cl = ids[i];
    
                            be = "<input style='height:22px;width:20px;' type='button' id='editBtn" + i + "' value='E' onclick=\"jQuery('#dataGrid').editRow('" + cl + "');sum()\"  />";
    
                            se = "<input style='height:22px;width:20px;' type='button' value='S' onclick=\"jQuery('#dataGrid').saveRow('" + cl + "');\"  />";
    
                            ce = "<input style='height:22px;width:20px;' type='button' value='C' onclick=\"jQuery('#dataGrid').restoreRow('" + cl + "');\" />";
    
                            jQuery("#dataGrid").jqGrid('setRowData', ids[i], { act: be + se + ce });
    
     
    
                        }
    
                    },
    
                    ajaxGridOptions: { contentType: "application/json" },
    
                    loadonce: true,
    
                    colNames: ['act', 'txt1', 'txt2', 'txt3', 'SUM'],
    
                    colModel: [
    
                        { name: 'act', index: 'act', width: 80 },
    
                        {
    
                            name: 'txt1', index: 'txt1', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt2', index: 'txt2', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt3', index: 'txt3', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt4', index: 'SUM', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        }
    
                    ],
    
                    pager: '#pagingGrid',
    
                    rowNum: 5,
    
                    rowList: [10, 20, 30],
    
                    viewrecords: true,
    
                    gridview: true,
    
     
    
                    jsonReader: {
    
                        page: function (obj) { return 1; },
    
                        total: function (obj) { return 1; },
    
                        records: function (obj) { return obj.d.length; },
    
                        root: function (obj) { return obj.d; },
    
                        repeatitems: false,
    
                        id: "0"
    
                    },
    
                });
    
            });
    
            $('#dataGrid').jqGrid('navGrid', "#pagingGrid", { edit: true, add: true, del: false });
    
        </script>
    
    </head>
    
    <body style="font-size: 20px">
    
        <form id="form1" runat="server">
    
            <table style="border: solid 2px red; width: 100%; vertical-align: central;">
    
                <tr>
    
                    <td style="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; font-family: 'Times New Roman'; font-weight: bold; font-size: 20pt; color: chocolate;">jQ Grid demo 
    
                    </td>
    
                </tr>
    
                <tr>
    
                    <td style="text-align: center; vertical-align: central; padding: 50px;">
    
                        <table id="dataGrid" style="text-align: center; font-size: 20px"></table>
    
                        <div id="pagingGrid"></div>
    
                    </td>
    
                </tr>
    
            </table>
    
        </form>
    
    </body>

    Code Behind:

    [WebMethod]
    
            public static List<Dictionary<string, object>> GetDataFromDB()
    
            {
    
                DataTable dt = new DataTable();
    
                using (SqlConnection con = new SqlConnection(@"Server=.;Database=TestDB;Trusted_Connection=True;"))
    
                {
    
                    using (SqlCommand cmd = new SqlCommand("SELECT id,txt1,txt2,txt3,txt4 from test", con))
    
                    {
    
                        con.Open();
    
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                        da.Fill(dt);
    
                        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    
                        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    
                        Dictionary<string, object> row;
    
                        foreach (DataRow dr in dt.Rows)
    
                        {
    
                            row = new Dictionary<string, object>();
    
                            foreach (DataColumn col in dt.Columns)
    
                            {
    
                                row.Add(col.ColumnName, dr[col]);
    
                            }
    
                            rows.Add(row);
    
                        }
    
                        return rows;
    
                    }
    
                }
    
            }

    Result:

     

    According to your description, I suggest you keep the value in number instead of currency format as number format is more friendly and it will save time from coding for dealing with currency in calculation.

    Hope this can help you.

     

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 13, 2020 10:23 AM

All replies

  • User-1330468790 posted

    Hi,  9peculiar

      

    Based on your description, I suggest you could do like this, please refer to the following example:

     Code:

     <body>
    
        <form id="sum" runat="server">
    
            <div>
    
                <input id="txt1" type="text" />+
    
                <input id="txt2" type="text" />+
    
                <input id="txt3" type="text" />=
    
                <input id="txt4" type="text" readonly="true"/>
    
            </div>
    
        </form>
    
        <script src="Scripts/jquery-3.4.1.js" type="text/javascript"></script>
    
        <script>
    
            $(document).ready(function () {
    
                $("#txt1,#txt2,#txt3").on("change", function () {
    
                    var txt1 = $("#txt1").val();
    
                    var txt2 = $("#txt2").val();
    
                    var txt3 = $("#txt3").val();
    
                    var value = Number(txt1) + Number(txt2) + Number(txt3);
    
                    $("#txt4").val(value);
    
                });
    
            });
    
        </script>
    
    </body>

    Result:

     

      

    I would like to know if this can help you meet your needs.

    If it is another requirement, can you describe it in detail?

     

    Best regards,

    Sean

    Monday, May 11, 2020 11:20 AM
  • User1984354752 posted

    Hi Sean:

    This is in principle what I'm looking for. However I don't know ho to hook this to the built-in Edit from the jqgrid. I don't know how to refer the html  fields of the Edit forms in order to  reproduce your coding ….surely there should be a way? 

    I also wanto to take advantage that we are dealing with the jqgrid Edit form to ask you a very simple question .  Why the edit form doesn't format the fields as per below ? I mean jqfrid shows me the currency format but  the Edit form doesn't keep the format …... I'm missing something here?

    {
    name: 'FundingAmount', index: 'FundingAmount', editable: true, key: false, search: true, width: 200, resizable: false, formatter: 'currency',
    formatoptions: { prefix: 'R', thousandsSeparator: ',' }
    },

    I'm really grateful for your assistance 

    Best regards.

    Tuesday, May 12, 2020 8:16 PM
  • User-1330468790 posted

    Hi,  9peculiar

     

    Thank you for your reply. For the issues you encounter, you can refer to the following example:

    Foreground code:

    <head runat="server">
    
        <title></title>
    
        <link type="text/css" href="Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
    
        <script src="Scripts/jquery-3.4.1.js"></script>
    
        <script src="Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
    
        <script src="Scripts/jquery.jqGrid.js" type="text/javascript"></script>
    
        <script type="text/javascript"> 
    
            function sum() {
    
                $('input[id*="txt4"]').attr("readOnly", true);
    
                $('input[id*="txt"]').on("change", function () {
    
                    var txt1 = $('input[id*="txt1"]').val();
    
                    var txt2 = $('input[id*="txt2"]').val();
    
                    var txt3 = $('input[id*="txt3"]').val();
    
                    var value = Number(txt1) + Number(txt2) + Number(txt3);
    
                    $('input[id*="txt4"]').val(value);
    
                });
    
            }
    
            $(function () {
    
                $("#dataGrid").jqGrid({
    
                    url: 'Test.aspx/GetDataFromDB',
    
                    datatype: 'json',
    
                    mtype: 'POST',
    
                    serializeGridData: function (postData) {
    
                        return JSON.stringify(postData);
    
                    },
    
                    gridComplete: function () {
    
                        var ids = jQuery("#dataGrid").jqGrid('getDataIDs');
    
                        for (var i = 0; i < ids.length; i++) {
    
                            var cl = ids[i];
    
                            be = "<input style='height:22px;width:20px;' type='button' id='editBtn" + i + "' value='E' onclick=\"jQuery('#dataGrid').editRow('" + cl + "');sum()\"  />";
    
                            se = "<input style='height:22px;width:20px;' type='button' value='S' onclick=\"jQuery('#dataGrid').saveRow('" + cl + "');\"  />";
    
                            ce = "<input style='height:22px;width:20px;' type='button' value='C' onclick=\"jQuery('#dataGrid').restoreRow('" + cl + "');\" />";
    
                            jQuery("#dataGrid").jqGrid('setRowData', ids[i], { act: be + se + ce });
    
     
    
                        }
    
                    },
    
                    ajaxGridOptions: { contentType: "application/json" },
    
                    loadonce: true,
    
                    colNames: ['act', 'txt1', 'txt2', 'txt3', 'SUM'],
    
                    colModel: [
    
                        { name: 'act', index: 'act', width: 80 },
    
                        {
    
                            name: 'txt1', index: 'txt1', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt2', index: 'txt2', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt3', index: 'txt3', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        },
    
                        {
    
                            name: 'txt4', index: 'SUM', width: 140, editable: true, formatter: 'currency',
    
                            formatoptions: { prefix: '$', thousandsSeparator: ',' }
    
                        }
    
                    ],
    
                    pager: '#pagingGrid',
    
                    rowNum: 5,
    
                    rowList: [10, 20, 30],
    
                    viewrecords: true,
    
                    gridview: true,
    
     
    
                    jsonReader: {
    
                        page: function (obj) { return 1; },
    
                        total: function (obj) { return 1; },
    
                        records: function (obj) { return obj.d.length; },
    
                        root: function (obj) { return obj.d; },
    
                        repeatitems: false,
    
                        id: "0"
    
                    },
    
                });
    
            });
    
            $('#dataGrid').jqGrid('navGrid', "#pagingGrid", { edit: true, add: true, del: false });
    
        </script>
    
    </head>
    
    <body style="font-size: 20px">
    
        <form id="form1" runat="server">
    
            <table style="border: solid 2px red; width: 100%; vertical-align: central;">
    
                <tr>
    
                    <td style="padding-left: 20px; padding-top: 20px; padding-bottom: 20px; background-color: skyblue; font-family: 'Times New Roman'; font-weight: bold; font-size: 20pt; color: chocolate;">jQ Grid demo 
    
                    </td>
    
                </tr>
    
                <tr>
    
                    <td style="text-align: center; vertical-align: central; padding: 50px;">
    
                        <table id="dataGrid" style="text-align: center; font-size: 20px"></table>
    
                        <div id="pagingGrid"></div>
    
                    </td>
    
                </tr>
    
            </table>
    
        </form>
    
    </body>

    Code Behind:

    [WebMethod]
    
            public static List<Dictionary<string, object>> GetDataFromDB()
    
            {
    
                DataTable dt = new DataTable();
    
                using (SqlConnection con = new SqlConnection(@"Server=.;Database=TestDB;Trusted_Connection=True;"))
    
                {
    
                    using (SqlCommand cmd = new SqlCommand("SELECT id,txt1,txt2,txt3,txt4 from test", con))
    
                    {
    
                        con.Open();
    
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                        da.Fill(dt);
    
                        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    
                        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    
                        Dictionary<string, object> row;
    
                        foreach (DataRow dr in dt.Rows)
    
                        {
    
                            row = new Dictionary<string, object>();
    
                            foreach (DataColumn col in dt.Columns)
    
                            {
    
                                row.Add(col.ColumnName, dr[col]);
    
                            }
    
                            rows.Add(row);
    
                        }
    
                        return rows;
    
                    }
    
                }
    
            }

    Result:

     

    According to your description, I suggest you keep the value in number instead of currency format as number format is more friendly and it will save time from coding for dealing with currency in calculation.

    Hope this can help you.

     

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 13, 2020 10:23 AM
  • User1984354752 posted

    Hi Sean:

    Apologies for the late response. I had to put coding aside due to personal issues.  It seems that you hooked the coding to form  that you built which isn't exactly what I asked for . I'm  using  the built in Edit form that is built-in with the jqgrid. Take a look at this link => http://www.trirand.com/jqgridwiki/doku.php?id=wiki:form_editing

    So basically my question is how to do what you did in the built in Edit form. I have a vague idea …..I came across this Forum post

    https://forums.asp.net/t/1959843.aspx?JQGrid+Edit+Form+Submit+alert+Message+Ok+button+clicked+Edit+Form+Modal+Close

     SAs you can see,  the code read the value of the fields  to do a validation which in our case is a sum no a validation ...but don't know which event to use to achieve the same effect you did in your example.

    Hope you have now a full understanding of my requirement. 

    What I want is to do what you coded for the fields of the Edit form. I came across with 

    Wednesday, May 27, 2020 7:41 PM