Update failed when using grid.render_sql()

FelixP · April 17, 2012, 9:02am

Hi there,

I have read that to update data from multiple table, you have to edit the serverside code as stated in here docs.dhtmlx.com/doku.php?id=dhtm … operations

however I have no idea what to code or where to place the code…I have tried and still getting no result

here is the snapshot of my error page

I really have no idea what caused this error…I have tested the data processor with single table and it works for CRUD
…here is a piece of my code (btw I’m using coldfusion)

client side code

jQuery(document).ready(function() {
	var myGrid = new dhtmlXGridObject("myGrid");
	
	myGrid.setImagePath("css/imgs/");
	myGrid.setHeader("Product ID, Manufacturer, Product Type, Color, Product Name, Price, Launch Date, Max Volume, Notes");
	myGrid.attachHeader("#rspan,#connector_select_filter,#connector_select_filter,#rspan,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter");
	myGrid.setInitWidths("50,200,200,100,250,200,200,200,500");
	myGrid.setColTypes("ro,coro,coro,ra,ed,ed,ed,ed,txt");
	myGrid.setColSorting("na,connector,connector,connector,connector,connector,connector,connector,connector");
	myGrid.setPagingWTMode(true,true,true,[25,50,100,1000]);
	myGrid.enablePaging(true, 25, 10, "pagingArea", true, "infoArea");
	myGrid.setPagingSkin("toolbar","dhx_skyblue");
	//myGrid.enableSmartRendering(true);
	myGrid.enableMultiselect(true);
	myGrid.init();
	myGrid.loadXML("connector/connector.cfm");
	//myGrid.load("components/loadData.cfc?method=loadGridData", "json");
		
	var dp = new dataProcessor("connector/connector.cfm");
	dp.init(myGrid);					
	
	
	//delete selected row
	jQuery("#btnDelete").click(function(){
		myGrid.deleteSelectedRows();
	})

});

server side code


<cfinclude template="config.cfm">
<cfset grid = createObject("component",request.dhtmlxConnectors["grid"]).init(request.dhtmlxConnectors["datasource"],request.dhtmlxConnectors["db_type"])>

<!---
<cfset grid.enable_log(variables,getCurrentTemplatePath() & "_debug.log")>
--->

<!--- set datasource name --->
<cfset dsn="task2">

<!--- get option lists for combo box --->
<cfquery name="getProductType" datasource="#dsn#">
	SELECT *
    FROM tbl_productType
</cfquery>

<cfquery name="getManufacturer" datasource="#dsn#">
	SELECT *
    FROM tbl_manufacturer
</cfquery>

<!--- set option lists for productType --->
<cfset productType = structNew()>
<cfloop query="getProductType">
	<cfset productType[#productTypeId#]="#productTypeName#">
</cfloop>
<cfset grid.set_options("productTypeId", productType)>

<!--- set option lists for manufacturer --->
<cfset manufacturer = structNew()>
<cfloop query="getManufacturer">
	<cfset manufacturer[#manufacturerId#]="#manufacturerName#">
</cfloop>
<cfset grid.set_options("manufacturerId",  manufacturer)>

<!--- render the table --->
<cfsavecontent variable="query">
	SELECT printerId, productId, manufacturerName, productTypeName, isColor, productName, price, launchDate, maxVolume, notes
	FROM tbl_printer P, tbl_manufacturer M, tbl_ProductType PT
	WHERE P.manufacturerId = M.manufacturerId
	AND P.productTypeId = PT.productTypeId
</cfsavecontent> 

<cffunction name="colorRow">
	<cfargument name="row">
    <cfif ARGUMENTS.row.get_index() mod 2>
    	<cfset ARGUMENTS.row.set_row_color("red")>
    </cfif>
</cffunction>

<cffunction name="myUpdate">
	<cfargument name="action">
    <cfquery name="updateManufacturer">
    	UPDATE tbl_printer SET manufacturerId = ARGUMENTS.action.get_data('manufacturer_Id') WHERE manufacturerId = ARGUMENTS.action.get_id();
    </cfquery>
    <cfset ARGUMENTS.action.success()>
</cffunction>

<cfset grid.dynamic_loading(100)>
<cfset grid.event.attach("beforeRender", colorRow)>
<cfset grid.event.attach("beforeUpdate", myUpdate)>


<cfset grid.render_sql(#query#, "printerId", "productId,manufacturerName,productTypeName,isColor,productName,price,launchDate,maxVolume,notes")>

any kind of help is appreciated…thanks in advance

FelixP · April 17, 2012, 9:08am

just adding some missing information…

that alert box appeared not because I have updated anything…that alert box appeared as soon as the page has finished loaded and of course that update part doesn’t work as well

Stanislav · April 17, 2012, 10:38am

The text in message is part of CF debug output, be sure to remove any debugging instructions from connector related code and be sure that you have not debug output enabled for all pages by default

FelixP · April 17, 2012, 11:05am

yes, I have comment out the debugging part in connector.js and now it says something like “incorrect XML”

Here is what I have done:

it seems that the “radio button” is the thing that causes the error in display, when I change the ra into ed, no problem with display.
The next thing is still the same problem with the update part…I’ll give you my latest code

client side code (the only thing different here from the previous code is I have removed the first column containing ID, and change ra to ed)

jQuery(document).ready(function() {
	var myGrid = new dhtmlXGridObject("myGrid");
	
	myGrid.setImagePath("css/imgs/");
	myGrid.setHeader("Manufacturer, Product Type, Color, Product Name, Price, Launch Date, Max Volume, Notes");
	myGrid.attachHeader("#connector_select_filter,#connector_select_filter,#rspan,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter");
	myGrid.setInitWidths("200,200,100,250,200,200,200,500");
	myGrid.setColTypes("coro,coro,ed,ed,ed,ed,ed,txt");
	myGrid.setColSorting("connector,connector,connector,connector,connector,connector,connector,connector");
	myGrid.setPagingWTMode(true,true,true,[25,50,100,1000]);
	myGrid.enablePaging(true, 25, 10, "pagingArea", true, "infoArea");
	myGrid.setPagingSkin("toolbar","dhx_skyblue");
	//myGrid.enableSmartRendering(true);
	myGrid.enableMultiselect(true);
	myGrid.init();
	myGrid.loadXML("connector/connector.cfm");
	//myGrid.load("components/loadData.cfc?method=loadGridData", "json");
		
	var dp = new dataProcessor("connector/connector.cfm");
	dp.init(myGrid);					
	
	
	//delete selected row
	jQuery("#btnDelete").click(function(){
		myGrid.deleteSelectedRows();
	})

});

server side code (I remove the color row part to improve code readability)

<cfinclude template="config.cfm">
<cfset grid = createObject("component",request.dhtmlxConnectors["grid"]).init(request.dhtmlxConnectors["datasource"],request.dhtmlxConnectors["db_type"])>

<!---
<cfset grid.enable_log(variables,getCurrentTemplatePath() & "_debug.log")>
--->

<!--- set datasource name --->
<cfset dsn="task2">

<!--- get option lists for combo box --->
<cfquery name="getProductType" datasource="#dsn#">
	SELECT *
    FROM tbl_productType
</cfquery>

<cfquery name="getManufacturer" datasource="#dsn#">
	SELECT *
    FROM tbl_manufacturer
</cfquery>

<!--- set option lists for productType --->
<cfset productType = structNew()>
<cfloop query="getProductType">
	<cfset productType[#productTypeId#]="#productTypeName#">
</cfloop>
<cfset grid.set_options("productTypeId", productType)>

<!--- set option lists for manufacturer --->
<cfset manufacturer = structNew()>
<cfloop query="getManufacturer">
	<cfset manufacturer[#manufacturerId#]="#manufacturerName#">
</cfloop>
<cfset grid.set_options("manufacturerId",  manufacturer)>

<cffunction name="myUpdate">
	<cfargument name="action">
    <cfquery name="updateManufacturer">
    	UPDATE tbl_printer SET manufacturerId = ARGUMENTS.action.get_data('manufacturer_Id') WHERE productId = ARGUMENTS.action.get_id();
    </cfquery>
    <cfset ARGUMENTS.action.success()>
</cffunction>

<cfset grid.dynamic_loading(100)>
<cfset grid.event.attach("beforeUpdate", myUpdate)>

<!--- render the table --->
<cfsavecontent variable="query">
	SELECT productId, manufacturerName, productTypeName, isColor, productName, price, launchDate, maxVolume, notes
	FROM tbl_printer P, tbl_manufacturer M, tbl_ProductType PT
	WHERE P.manufacturerId = M.manufacturerId
	AND P.productTypeId = PT.productTypeId
</cfsavecontent> 

<cfif grid.is_select_mode()>
<cfset grid.render_sql(#query#, "productId", "manufacturerName,productTypeName,isColor,productName,price,launchDate,maxVolume,notes")>
<cfelse>
<cfset grid.render_table("tbl_printer")>
</cfif>

Here is the problem:
if I remove the if statement and only execute the render_table (+commenting the event “attach event myUpdate”) then no problem with update…but it’s not what I want because it only operates on single table. What I want to accomplish is to use the render_sql part and update successfully…and the error part is still the same ==> Invalid XML

What am I missing here? Thanks in advance

Stanislav · April 18, 2012, 9:50am

Try next

a) uncomment the enable_log instruction and check result in log file, it will show the text of generated sql queries a

b) try to load the data script directly in browser, it will show valid xml if it works correctly, or detailed info about error, if some problem occurs during data generation.

FelixP · April 19, 2012, 9:46am

here is the response for your suggestion

log information

====================================
Log started, 19/04/2012 04:30:40PM
====================================
DataProcessor object initialized: 
	[IDS] = 1
	[1_C4] = 1000
	[1_C7] = 4000000
	[1_C3] = E7Y 5Y8
	[FIELDNAMES] = 1_GR_ID,1_C0,1_C1,1_C2,1_C3,1_C4,1_C5,1_C6,1_C7,1_C8,1_!NATIVEEDITOR_STATUS,IDS
	[1_C6] = 0
	[1_C0] = 2
	[1_!NATIVEEDITOR_STATUS] = updated
	[1_C8] = Notes1
	[1_C1] = 3
	[1_C2] = 1
	[1_GR_ID] = 1
	[1_C5] = 2012-04-15 00:00:00.0

Row data "1": 
	[!NATIVEEDITOR_STATUS] = updated
	[productName] = E7Y 5Y8
	[ProductAge] = 0
	[price] = 1000
	[maxVolume] = 4000000
	[isColor] = 1
	[productTypeId] = 3
	[launchDate] = 2012-04-15 00:00:00.0
	[manufacturerId] = 2
	[productId] = 1
	[notes] = Notes1

there is no problem when I load the script directly, it shows valid XML format when displaying data…the problem is when I try to update it, then error shows : “invalid XML format”
How do I display the XML format when updating?

I have made a bit changes to the program. Here is the summary:
I display the data in grid from a single table + another manually computed column (which result is (today’s date - table’s date) in months. Here is my query:

<cfsavecontent variable="query">
	SELECT *, DATEDIFF(month, launchDate, GETDATE()) as ProductAge
        FROM tbl_printer
</cfsavecontent>

and this is how I do the render_sql

<cfset grid.render_sql(#query#, "productId", "manufacturerId,productTypeId,isColor,productName,price,launchDate,ProductAge,maxVolume,notes")>

this is my user-defined update function (let’s say that I just want to update the first two column)

<cffunction name="myUpdate">
	<cfargument name="action">
    <cfquery name="updateManufacturer">
    	UPDATE tbl_printer 
        SET manufacturerId = ARGUMENTS.action.get_data('manufacturerId'),
        productTypeId = ARGUMENTS.action.get_data('productTypeId')
        WHERE productId = ARGUMENTS.action.get_id();
    </cfquery>
    <cfset ARGUMENTS.action.success()>
</cffunction>

<cfset grid.event.attach("beforeUpdate", myUpdate)>

Is the argument passed in ARGUMENTS.action.get_data already correct? or is there something I missed here?
again no problem with displaying data, the problem occurs only when I want to update the data

you can view my function for initiating the dhtmlx grid in task2.js line 75
update.zip (3.36 KB)

Ivan_CF · April 20, 2012, 10:11am

Hi.
According to yuor code I see incorrect onAfterUpdate event.
You have:

UPDATE tbl_printer SET manufacturerId = ARGUMENTS.action.get_data('manufacturerId'), productTypeId = ARGUMENTS.action.get_data('productTypeId') WHERE productId = ARGUMENTS.action.get_id();

but should be

UPDATE tbl_printer SET manufacturerId = #ARGUMENTS.action.get_value('manufacturerId')#, productTypeId = #ARGUMENTS.action.get_value('productTypeId')# WHERE productId = #ARGUMENTS.action.get_id()#

I think you can see the differences: #variable# and usage of not “get_data” but “get_value” function. I’ve created the sample on the base of this data - works fine.
Try to change update event.

Regards

FelixP · April 20, 2012, 11:14am

hey thanks a lot Ivan and Stanislav,

yeah It seems that the program-flow is correct already. It’s just that I’m not used to this coldfusion yet. And I manage to solve this with your help and firebug as well. Trully firebug can help your debugging process real fast. Again thanks a lot