Edit SQL non-spatial data table

[BrianLord1]

I have a table that i was able to publish in a map service and query it to populate a datagrid in my application.  I need to allow users to edit the data from this table using the datagrid, but have been unable to figure out how to do this.

I did use an 'itemFileWriteStore' to populate the grid with the queried records from the table. 

Here is the html for the grid.
[HTML]                <table data-dojo-type="dojox.grid.DataGrid" data-dojo-id="grid"  id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px'" style="margin-top: 30px; height: 168px;">
                    <thead>
                        <tr>
                            <th field="GenSurvID" >General Survey ID</th>
                            <th field="SurveyorID" >Surveyor ID</th>
                            <th field="SpecificDate" >Specific Date</th>
                            <th field="Book" >Book</th>
                            <th field="PageNum" >Page Number</th>
                            <th field="DocLink" formatter="makeLink" >Document Link</th>
                            <th field="SurveyNum" >Survey Number</th>
                            <th field="Section" >Section</th>
                            <th field="Township" >Township</th>
                            <th field="Range" >Range</th>
                            <th field="EstimatedDate" >Estimated Date</th>
                            <th field="CntyRemarks" width="100%">County Remarks</th>
                        </tr>
                    </thead>
                </table>[/HTML]

here is the code that populates the grid...

function showResults(selectedFeatures) {                 var results = selectedFeatures.features;                 if (results.length != 0) {                    //create array of attributes                    var items = dojo.map(results, function (result) {                         return result.attributes;                    });                     //Create data object to be used in store                    var data = {                        identifier: "GenSurvID",  //This field needs to have unique values                        items: items                    };                     //Create data store and bind to grid.                    store = new dojo.data.ItemFileWriteStore({ data: data });                    var grid = dijit.byId('grid');                    grid.setStore(store);                    grid.editable = true;                 } else {                    divNoRecords.style.display = 'block';                }                 if (divToggleDetail.className == 'divToggleDetailExpand') {                    AnimateDetailsView()                }                 HideLoadingMessage2();             }

Any help would be greatly appreciated.

Thanks,
Mark

[DianaBenedict]

A couple of questions come to mind:
1) is your NonSpatial Table an SDE layer, registered with the GDB and/or versioned? 
2) did you include the table in your FeatureMapService so you can edit it?

If the answer is no, then I would think that you will need to write your own webservice to edit this table. The webservice would probably use something like ADO.Net to make the connection objects and call the proper SQL/stored procs to update your database. 

However if the answer is yes then you can leverage the FeatureLayer.applyEdits to make direct edits to your nonSpatial table.  I have not done this with a datagrid but I have been able to edit nonSpatial tables using the ESRI AttributeInspector via a FeatureLayer.  With the datagrid I would assume that you just need to get the final attirubte field:value pairs and build the expected graphics array for the graphics/records that have changed values. Note that for nonspatial tables the grpahic.geometry = null

[BrianLord1]

I did import the table into SDE and have it in my app as a feature layer and am populating the datagrid by querying the layer.  I have used the applyEdits method before with the attribute inspector, but am having some issues using it with the datagrid.  I am not sure how to tie the two together.

What event should I use to tell pass the edits from the datagrid to the function where I call applyEdits?  The dojo datagrid has an event "onApplyEdit", which is suppose to fire when an edit is aplied to the datagrid but I have not been able to get that to work.

Thanks for your help.

[DianaBenedict · Accepted solution]

... What event should I use to tell pass the edits from the datagrid to the function where I call applyEdits?  The dojo datagrid has an event "onApplyEdit", which is suppose to fire when an edit is aplied to the datagrid but I have not been able to get that to work.

I am sorry but I have not worked much with DataGrids other than a readonly process.  I noticed that DataGrid also has an "onApplyCellEdit" event, maybe this is more of what you are looking for.  I would start by doing something like below ... though none of this has been tested and it is only based on what I know about how events work with datagrids and dojo.connect

  var myGrid = dijit.byId("grid"); dojo.connect(myGrid, "onApplyCellEdit", gridCellEditHanler);  function gridCellEditHanler(inValue, inRowIndex, inFieldIndex) { //call code here to handle cell edits alert("cell value change"); }

I used the following link to get additional information some it was helpful in seeing how others are using the datagrid for editing:
http://dojo-toolkit.33424.n3.nabble.com/DataGrid-onApplyCellEdit-issue-td3320440.html

I would just play around with it and hook up some alerts and see what you get then I would plug in the code to actually apply the edits.
Before I hook up the code to applyEdits to the featureLayer/table I would create a quick sample using the AttributeInspector and see how it generates the objects/json to pass to the server for ApplyEdits.  I would also consider having a "Save" button on your Grid/form since making calls to the server evertime the user makes one simple little change on datagrid will create a lot of network traffic.  Of course there are pros and cons but you will need to eval and figure out which makes more sense for your organization.

Good luck

[LukeBehling]

Hello Mark,
Did you ever get this to work in that datagrid? I'm looking at something similar right now.

[BrianLord1]

Hello Mark,
Did you ever get this to work in that datagrid? I'm looking at something similar right now.

Luke,

I did end up getting this to work after importing the table into sde.

Here is the html i use to set up the grid and add the event handler for when a cell gets changed.

[HTML]<table dojotype="dojox.grid.EnhancedGrid" id="grid" data-dojo-props="rowsPerPage:'5', rowSelector:'20px', rowHeight:'16px'"
                    style="height: 168px; width:99%;">
                    <script type="dojo/method" event="onApplyCellEdit" args="value, rowId, idx">
                        adminDoCellEdit(value, rowId, idx);
                    </script>
                </table>[/HTML]

This gets the selected row and column from the datagrid and the value that changed.  I then query the service grabbing the corresponding record and change the value and then run the applyedits.  There is probably a better way, but I am new too javascript and this has worked so far.

function adminDoCellEdit(value, iRowIndex, iStoreIndex) {
                var grid = dijit.byId('grid');
                var selectedItem = grid.getItem(iRowIndex);
                var objectID = grid.store.getValue(selectedItem, 'OBJECTID');

                var pTask = 'surveyRecQueryTask';
                surveyRecQueryTask = new esri.tasks.QueryTask("http://servername:6080/arcgis/rest/services/Internal_Secured/SurveyRecords2/MapServer/6");
                var query = new esri.tasks.Query();
                query.where = "OBJECTID = " + objectID;
                query.outFields = ["*"];
                query.outSpatialReference = map.spatialReference;
                query.returnGeometry = true;

                surveyRecQueryTask.execute(query, function (featureList) {
                    var feature = featureList.features[0];

                    if (feature.attributes[iStoreIndex] != value) {
                        feature.attributes[iStoreIndex] = value;

                        map.getLayer(surveyRecordsLayerID).applyEdits(null, [feature], null, callback, err2back);

                    } else {
                        // alert("You did not make any changes");
                    }
                });
            }

I hope this helps, like I said there might be a better way and if you figure something else out please let me know.  I also have code to add or delete records using the datagrid, let me know if you need that as well.

Thanks,
Mark