Skip to Content

Overview :In one of our requirements, we needed to allow the user to edit the grid online and save the data.  Well I  didn’t get the exact solution to this but a work around using the JavaScript. The detials below will explain step by step process using some really simple and reusable JavaScript code. Hope You will enjoy this.

In the illustration below, I have considered Employee table of the Northwind database.

Generate the Grid

    Query Template: Select the Employees Table from the table list and some of the columns from the column list. Make sure to select the Id (which is primary key ) as this will be used as identifier for the record in update. Refer to the Image Below.


      Test the Query Template and save that in some location of your choice. 

      Display Template :Create one Grid type Display template and on the Data Maping tab, select the Query that you just created and save the template. Please refer to the Image below.


        Web Page :Make one HTML page and put the the Applet using above Display template and Query template, which should look something like this.</li></ul>            

          1. You need to put ‘ID’ attribute in the applet tag for the above applet, which can be same as applet name attribute, refer to the code sample below:

        <APPLET NAME=”SampleGrid” id=”SampleGrid” CODEBASE=”/Illuminator/Classes” CODE=”iGrid” ARCHIVE=”” WIDTH=”640″ HEIGHT=”230″ MAYSCRIPT>

          1. This will be used to identify which grid needs to be updated, this is illustrated below.
          1. Put two Empty JavaScript Methods with the following signature:

        function updateSelectedRecords(){/* Method Body/ }</li><li>function setReadOnlyProperties(){/ Method Body/ }<br />Content of each method is described in the section below.</li></ul></li></ul><p>With this done, we have completed the first part, that is getting the grid.</p><p>Now the next step is to make the grid editable, well not exactly the grid, but something which will allow us to edit the contents of the grid.</p><p>Follow few simple things to achive this: </p><ul><li>Query Template: First We need a query template which will update the Employee details. In the Query Template Editor , create one command query which will update the Employee details based on EmployeeId, which should look something like <ul><li>UPDATE Employees SET Employees.LastName='[Param.1]’ , Employees.FirstName='[Param.2]’ , Employees.Title='[Param.3]’ ,Employees.Address='[Param.4]’,<br />Employees.City='[Param.5]’ where Employees.EmployeeID='[Param.6]'</li></ul></li><li>Display Template : We need Command Type display template to update the Emplaoyee Details:</li><li>Put one Command Applet in the webpage with above created Query template and display template, give the name as ‘UpdateEmployeeCommand'</li><li>In your page, Please keep one empty form with name +GenerateTable , +the code should be something like +
        <ul><li><form name=”GenerateTable”><br /><!This is the empty form where the generated table will reside><br /></form>+</li></ul></li><li>Create one JavaScript file with extension .js and put the following code in that <ul><li>//–

        Code Starts—

        </li><li>var Params;  // This is used as two dim array to hold the values updated fields of each row.<br />var noOfColumns=0; // This is to hold the no. of columns in each row<br />var rowCount=0;   // this is to hold the no. of rows for the grid<br />var readOnlyProperties;// this is used to hold the readonly properties of the table.<br /><br />/
         * This function will generate the table.
           The parameter passed to this function is the Grid Id for which the
           table has to be generated.
        /<br /><br />function generateGridTable(gridName){<br />    <br />var TableName=gridName‘_Table’;<br />var generateHTML=”<table name=”TableName” border=1>\n”;<br />var generateHeader=””;<br />var generateRow=””;<br /><br />var gridObject=document.getElementById(gridName).getGridObject();<br />noOfColumns=gridObject.getColumnCount();<br />rowCount=gridObject.getRowCount();<br />readOnlyProperties=new Array(noOfColumns);<br />setReadOnlyProperties();<br />//–Generate Headers–<br />        generateHeader=”<tr><th>Select</th>\n”<br />        for(var i=1;i<=noOfColumns;i)<br />            generateHeader=”<th>”(gridObject.getColumnName(i)).toUpperCase()“</th>\n”;<br />        <br />        generateHeader=”</tr>”;<br />//—--Header generation Complete<br /><br />//–Generate Rows<br />    for(var i=1;i<=rowCount;i){<br />        //First Add the checkBox to select the record to update<br />        generateRow=”<tr><td><input type=’checkbox’ id=’select”i“‘ onClick=enableRow(“i“,”noOfColumns“)></td>\n”;<br />                for(var j=1;j<=noOfColumns;j+){<br />                    generateRow=”<td><input disabled=’true’ type=’text’ id='”i“.”j“‘ value='”gridObject.getCellValue(i,j)“‘ “isReadOnly(gridObject.getColumnName(j))“/></td>\n”;<br />                }<br />        generateRow=”</tr>\n”;<br />    }<br />    var AddSaveButton=”<tr><td colspan=”noOfColumns“><input type=’Button’ value=’update records’ onClick=’updateSelectedRecords(“rowCount“)’ /></td></tr>\n”<br />//—-Row Genration Complete<br />//alert(generateHTMLgenerateHeadergenerateRowAddSaveButton“</table>”);<br />document.GenerateTable.innerHTML=generateHTMLgenerateHeadergenerateRowAddSaveButton+”</table>”;<br />}<br /><br /><br />/*
          * This function is called when the selection checkBox is checked or unchecked.
        /<br />function enableRow(index){<br />    for(var i=1;i<=noOfColumns;i+)<br />        document.getElementById(index‘.’i).disabled=!document.getElementById(‘select’index).checked;<br />}<br /><br />/
         * This Function will generate a Two dim Array
         * which holds the values for each of the updated text box.
         * The generated Array will hold the values from 1.1–1.noOfColumns,      <br />                                                 rowCount.1rowCount.noOfColumns<br /> /<br /><br />function generateParams(){<br />Params= new Array(rowCount1);// 0,0 will hold null values as our id’s of table are mapped from 1.1<br />// Create a two Dim Array to hold the updated Values<br />    for(var i=0;i<=rowCount;i){<br />        Params[i]= new Array(noOfColumns);<br />    }<br /><br />    // Add values to each place in two dim array<br />    for(var i=0;i<=rowCount;i){// for 1, for each row<br />        for(var j=0;j<=noOfColumns;j){// for 2, for each col<br />            if(i>0 && j>0){// if 1, avoid 0.0<br />                    Params[i][j]=document.getElementById(i‘.’+j).value;<br />                }// end if 1<br />            }//end for 2<br />        }// end for 1<br />    }<br /><br />    /*<br />        This function is find if the property is readonly or not<br />    */<br />    function isReadOnly(propertyName){<br />        for(var i=0;i<readOnlyProperties.length;i++)<br />                if(readOnlyProperties[i]==propertyName)<br />                    return ‘readOnly’;<br />        return ”;<br />    }<br />//

        Code ends here

          1. Keep the  JavaScript file  somewhere on  the  Inpetpub folder, preferably same place where your web page  is.
          2. In your webpage, import the JavaScript file which is created in the above step, follow the following code to do so

        Place a Button on your webpage, on click of that call function generateGridTable(‘SampleGrid*’); where <em><strong>SampleGrid </strong></em>is the ID of the applet which needs to be updated.*

          1. Save the HTML file and Test,it should look some thing like.


          1. Click on the Edit Grid Button which should Generate a table exactly like our Grid and one checkbox for each row. as shown in the image below


          So we have got the Table which is exactly like the Grid, By Default all the rows are disabled , which could be enabled to update by checking the respective checkbox.Notice one button called +Update Records +just below the table.

          The next thing is to update the record in the database. For this we need to put the body in the one of the Empty methods +updateSelectedRecords() which I mentioned earlier.+

          function updateSelectedRecords(){<br />generateParams();// call this method to get the param array<br />var queryObject=document.UpdateEmployee.getQueryObject();// This is query Object for the Update Command

          +   for(var i=1;i<=rowCount;i+){<br />        if(document.getElementById(‘select’i).checked==true){</p><p>// Params[i][n] is the n’th textbox value in the i’th row


          If we want to make some of the properties that should not change, like in our case, employeeId add the below mentioned code to the +setReadOnlyProperties() function.</p><p>function setReadOnlyProperties(){
              readOnlyProperties[0]=’EmployeeID’;// Here the value should exactly match the col name in grid
          }</p><p> </p><p>Now to update some record, select the record and change the values you wish to, Notice that in our example, EmployeeID is read only field. Lets change the value in first row,lets say Miss Nancy is promoted to Sales Manager from Sales Representative,change the value and click Update records, See the grid, it has the updated value, </p><p>Refer to Following Images  </p><p>!|alt=|src=! </p><p> !|alt=|src=!</p><p>
          + Thats it, so now we are able to update the Grid using some really cool JavaScript Stuff. 

          To report this post you need to login first.

          Be the first to leave a comment

          You must be Logged on to comment or reply to a post.

          Leave a Reply