Problems updating grid using form.

Suite and Optimus Suite 5
#1

Hi all,

This Apologies is sort of a long one, so please be a little patient

Background:

I have a grid that is populated with data from a db what I’ve been trying to do is use a form to allow updating, addition and deletion to the grid which would also update the db I’ve followed the tutorial regarding do this but when ever I try and trigger one of the CRUD functions I get the following error

Here is the page code im using:

 function load_TransportGrid() {

                transportGrid = new dhtmlXGridObject('transportGridContainer');
                // create the transport grid
                transportGrid .setImagePath("codebase/imgs/");
                transportGrid  .setHeader("Transport code,Transport name,Preference level,Options");
                transportGrid .attachHeader("#text_filter,#text_filter,#numeric_filter");
                transportGrid .setColumnIds("Transport_Type_ID,Transport_Name,Preference_Level,Options");
                transportGrid .setInitWidths("80,110,110,60");
                transportGrid .setColAlign("left,right,right,right,right");
                transportGrid .setSkin("modern");
                transportGrid .setColSorting("str,str,str,int");
                transportGrid .setColTypes("ro,ro,ed,ro");
                transportGrid.enableCellIds(true);
                transportGrid .init();
                transportGrid .load("resources/lib/cons/transportPreferences_gridConfig.php",function () {
                    {
                        for (var i = 0; i <  transportGrid.getRowsNum(); i ++ ) {
                            var cellObj = transportGrid.cellByIndex(i,3);
                            var cellValue = "<input type='image' id='delete' src='imgs/delete.jpg' alt='Zoom on location' WIDTH='20' HEIGHT='20' onClick = 'deleteTransportType()'> ";       
                            cellObj.setCValue(cellValue);
                        }
                    }
                });
                // define settings for the transport grid and initialise it.

                transportFormStructure =  [
                    {type:"settings",position:"label-top"},
                    {type: "fieldset", name:"addTransportSel", label:"Add transport preference ", list:[
                            {type: "select", label:  "Name:", options:[
                                    {text: "Car", value: "Car"},
                                    {text: "Train", value: "Train"},
                                    {text: "Bus", value: "Bus"},
                                    {text: "Coach", value: "Coach"}
                                ]},
                            {type:"newcolumn"},
                            {type:"select",offsetLeft: 2,name:"addTransportPrefSel",label:"Preferenece Level" ,offsetLeft:30,options:[
                                    {text: "1",value: "1"},
                                    {text: "2",value:"2"},
                                    {text:"3",value:"3"}
                                ]},
                            {type:"newcolumn"},
                            {type: "button",  name:"add_transport_btn", value:"Add",offsetTop:10}
                        ]}
                ]
                // create a new list containing form elements to define its structure

                transportForm = new dhtmlXForm("transportFormContainer",transportFormStructure);
                transportForm.attachEvent("onButtonClick",function(id){
                 if (id =='add_transport_btn') {
                     addTransportType();
                 }
                  else if (id =='update_transport_btn') {
                     
                 }
                 
                }) 
                transportForm.bind(transportGrid);
                
                transportDataprocessor = new dataProcessor ("resources/lib/cons/transportPreferences_gridConfig.php");
                transportDataprocessor.init(transportGrid);
                transportDataprocessor.enableDataNames(true);
                transportDataprocessor.setTransactionMode("Get");
            }

              function deleteTransportType() {
                var selectedItem = transportGrid.getSelectedRowId();
                transportGrid.deleteRow(selectedItem);
            }
            
            function addTransportType() {
                var areaCode = [b]transportForm.getItemValue("addAreaSel");[/b]
                var areaName = [b]transportForm.getItemText("addAreaSel");[/b]
                var prefLevel = transportForm.getItemValue("addAreaPrefSel");
                var inputString = areaName + ","+ prefLevel;
                transportGrid.addRow(areaCode, inputString,0);
            }

and this is the connector code:

[code]$grid_conn = new GridConnector($conn, “MySQL”);
// intilize the connector
$id = $_SESSION [‘ID’];
// get the users id for the query.
$grid_conn ->render_sql(“SELECT transport_preferences.Transport_Type_ID,
transport_preferences.Preference_Level,
ltransport_type.Description
FROM transport_preferences,ltransport_type
WHERE transport_preferences.Transport_Type_ID =
ltransport_type.Transport_Type_ID
AND transport_preferences.User_ID = $id”,"",“Transport_Type_ID,Transport_Name,Preference_Level”);

// render the table

?>

[/code]

Im pretty sure the bits in bold are wrong because they are returning null, but im not sure how to get a selection boxes text and values, could this be the issue?
If not can anybody give me some idea what the problem is?
Any help very much appreciated,Ive been at this for 2 days now and Im on a deadline :frowning: .

Thanks in advance.

#2

a) you are using render_sql - but second parameter of that command (record’s id) is empty - so connector will not be able to recognize records during saving

b) you are using
transportDataprocessor.enableDataNames(true);
transportDataprocessor.setTransactionMode(“Get”);

both modes are fine if you are using custom data saving script, but no-go for connector.

c) render_sql has 2 joined tables, in such case you need to use define how data must be saved, because there is no enough info for connector.
docs.dhtmlx.com/doku.php?id=dhtm … ex_updates

#3

Hi again,

Thanks for the prompt response Stanislav, I have tried your suggestions, removed the two offending methods, added id fields and included add delete and update functions into my connector files,

The connector file now looks as follows:

[code]rid_conn = new GridConnector($conn, “MySQL”);
// intilize the connector
$id = $_SESSION [‘ID’];
// get the users id for the query.

// attach a query to the connector to allow it to handle insert operations
$grid_conn->sql->attach(“Insert”,“INSERT INTO transport_preferences ({transport_preferences.Transport_Type_ID},$id,{transport_preferences.Preference_Level})
VALUES ({transport_preferences.Transport_Type_ID},$id,{transport_preferences.Preference_Level})”);

// attach a query to allow the connector to handle update operations
$grid_conn->sql->attach(“Update”,“UPDATE transport_preferences
SET Preference_Level = {transport_preferences.Preference_Level}
WHERE Transport_Type_ID = {transport_preferences.Transport_Type_ID}
AND User_ID = $id”);

//attach a query to allow the connector to handle delete operations
$grid_conn->sql->attach(“Delete”,“DELETE * FROM transport_preferences
WHERE User_ID = $id
AND Transport_Type_ID = {transport_preferences.Transport_Type_ID}”);

// create a query to generate display information and instruct the connector to render it
$grid_conn ->render_sql(“SELECT ltransport_type.Transport_Type_ID,
ltransport_type.Description,
transport_preferences.Preference_Level
FROM transport_preferences,ltransport_type
WHERE transport_preferences.Transport_Type_ID =
ltransport_type.Transport_Type_ID
AND transport_preferences.User_ID =$id”,“Transport_Type_ID”,“Transport_Type_ID,Transport_Name,Preference_Level”);

?>
[/code]

but im now getting the following error:

<?xml version='1.0' ?>

Interestingly I have used the same approch on another grid I am using and it shows as having updated but no changes are made to the db.
I know th actual queries are correct cos I’ve tested them independently but when I use them as part of the connector I get errors.

As a slight aside I do actually have custom processor scripts for the grids but im not sure how to trigger the correct (add,delete,update) operations on button press.

Anyone got any suggestions,

Thanks.

#4

try to enable logging - it will have detailed info about sql error ( and exact text of executed sql )

$grid_conn = new GridConnector($conn, "MySQL"); $grid_conn->enable_log("some.txt"); ...

As a slight aside I do actually have custom processor scripts for the grids
There are two solution

a) you can assign client code to one of dataprocessor’s events, and call custom ajax
or
b) you can call your php methods from connector

function doSave($action){ $id = $action->get_id(); $val1 = $action->get_value("Transport_Name"); ... custom_save_method($id, $val); $action->success(); } $grid->conn->event->attach("beforeProcessing", "doSave");

#5

Aha Ive located the problem thanks for the logging suggestion Stanislav the log shows the following:

[b]dataProcessor object initialized
1_gr_id => 1
1_c0 => 1
1_c1 => Car
1_c2 => 1
1_c3 =>
1_!nativeeditor_status => deleted
ids => 1

Undefined offset: 3 at C:\wamp\www\codebase\connector\grid_connector.php line 268[/b]

and if Im right it means that the parameter for the forth row is empty which would be correct I think, because my forth col only contains clickable images not actual data.
So does anyone happen to know how to define which coloumns should be sent for a db operation?
Ive tried setting data columns at guess but that does’nt work because it only defines what cols should trigger an update not what cols should be sent.
Sorry to keep asking dumb questions but does anyone have any ideas?

Thanks in advance.

#6

Sorry meant column do’h :blush:

#7

Try to use the attached php file instead of the original one, it will correctly ignore extra fields which comes from server side.
grid_connector.zip (2.41 KB)

#8

Hi again all,

Thanks so much for going to the effort of writting that code Stanislav, I am no longer getting the un defined index error but im still getting the same problem (ie the db is’nt updating)

my error log looks as follows:

Seems really odd, if Im reading this right its now passing the correct data,ignoring the empty column, running the correct query with the correct data finishing and then returning an error and not bothering to update, can any one help? Im really pulling my hair out on this now :frowning: .

#9

Please check your code, if you have something like

//attach a query to allow the connector to handle delete operations $grid_conn->sql->attach("Delete","DELETE * FROM area_preferences WHERE User_ID = 2 AND Area_Code = {Area_Code}");

you need to change it as

$grid_conn->sql->attach("Delete","DELETE * FROM area_preferences WHERE User_ID = 2 AND Area_Code = '{Area_Code}'");

Connector doesn’t surround strings with quotes when inserting in custom queries, which seems break one in your case, where Area_Code string placed in sql without quotes.

#10

Success :smiley: , thanks so much Stanislav all that for the sake of some speech marks !
I should have noticed.

Thanks again.