DataProcessor won't update data

Suite and Optimus Suite 5
#1

Hi,

I don’t get why my DataProcessor won’t update data. It is a pretty simple thing I’m trying to do and I have followed the examples as far as I can tell.

I have the following .NET DataConnector (grid_db_handler.ashx):

[code]
public override IdhtmlxConnector CreateConnector(HttpContext context)
{
string ds = context.Request.QueryString[“ds”];
string ljid = context.Request.QueryString[“ljid”];

connector = new dhtmlxGridConnector(
“SELECT Date, AppendixId, AccountType, AccountNumber, Description, Revenue, Expenses, Currency, VATCode FROM LedgerJournalTransactions WHERE (Dataset = '”+ ds +“’ AND LedgerJournalId = '”+ ljid +“') ORDER BY Id;”,
“Id”,
dhtmlxDatabaseAdapterType.SqlServer2005,
ConfigurationManager.ConnectionStrings[“DB”].ConnectionString);

connector.Request.TransactionMode = TransactionMode.PerRecord;

connector.Request.CustomSQLs.Add(
CustomSQLType.Update,
“UPDATE LedgerJournalTransactions SET Date = CONVERT(datetime, {date}), AppendixId = {appendix_id}, AccountType = {account_type}, AccountNumber = {account_number}, Description = {description}, Revenue = {revenue}, Expenses = {expenses}, Currency = {currency}, VATCode = {vat_code};”);

return connector;
}[/code]

And I have a Grid + DataProcessor as follows:

 mygrid = new dhtmlXGridObject("gridbox");

mygrid.setImagePath("./dhtmlxgrid/codebase/imgs/");

mygrid.setHeader("Date, Appendix Id, Account Type, Account Number, Description, Revenue, Expenses, Currency, VAT Code", null, "");
mygrid.setColumnIds("date, appendix_id, account_type, account_number, description, revenue, expenses, currency, vat_code");
mygrid.setColTypes("ed, ed, ed, ed, ed, edn, edn, ed, ed");
           
mygrid.enableSmartRendering(true);

mygrid.init();

mygrid.load("grid_db_handler.ashx?ds=TKZ&ljid=F8EBF621-ECF3-44FD-A28B-578A2CC8E4FA");          

var dp = new dataProcessor("grid_db_handler.ashx?ds=TKZ&ljid=F8EBF621-ECF3-44FD-A28B-578A2CC8E4FA");

dp.enableDataNames(true);

dp.enableDebug(true);

dp.init(mygrid);

The database table currently only has 1 row which is also loaded into the grid initially, but when I try to update any of the values in the grid, all the cell values turn red/bold which I believe means that there is an error/validation issue and that an update has not been performed.

I don’t know how to debug these components so I can’t really figure out what is wrong - Any help would be much appreciated both in regard to what could be wrong and also in regard to how to debug this - figuring out what is going wrong and why.

#2

Hi,
it’s hard to tell from code. Try enabling connector logging - it will show an update query that you get and this should give a clue to what goes wrong
Here is a related article,
docs.dhtmlx.com/connector__net__errors.html
and a code sample (a modified ashx.cs taken from connectors sample folder):
s3.amazonaws.com/uploads.hipcha … r.ashx.zip

Alternatively, you could simply put a breakpoint inside a handler and execute the update request step by step. You can also add dhtmlxConnectors project into your solution (‘sources’ folder in the package) so you’ll be able to debug them directly

Btw, in this code you put values from http request right into sql query, it creates an entry point for sql injection attacks:

[code]string ds = context.Request.QueryString[“ds”];
string ljid = context.Request.QueryString[“ljid”];

connector = new dhtmlxGridConnector(
“SELECT Date, AppendixId, AccountType, AccountNumber, Description, Revenue, Expenses, Currency, VATCode FROM LedgerJournalTransactions WHERE (Dataset = '”+ ds +"’ AND LedgerJournalId = ‘"+ ljid +"’) ORDER BY Id;"[/code]

Connector package has a helper for escaping such values in MSSQL -

dhtmlxConnectors.Tools.EscapeQueryValue()

A following code would be safer:

[code]string ds = context.Request.QueryString[“ds”];
string ljid = context.Request.QueryString[“ljid”];

connector = new dhtmlxGridConnector(
“SELECT Date, AppendixId, AccountType, AccountNumber, Description, Revenue, Expenses, Currency, VATCode FROM LedgerJournalTransactions WHERE (Dataset = '”+ dhtmlxConnectors.Tools.EscapeQueryValue(ds) +"’ AND LedgerJournalId = ‘"+ dhtmlxConnectors.Tools.EscapeQueryValue(ljid) +"’) ORDER BY Id;"[/code]

#3

OK i got the debugging part working now (both client- and server-side) and I have added the escape code part preventing injection attacks.
The data processor seems to be doing what it is supposed to, but the connector doesn’t seem to recognize that values were updated - at least it doesn’t update anything.
The updated row stays bold, though not red.

I have attached the server-side log with a line break between the initial selection for populating the grid and after the client-side update.

Also there seems to be a problem when adding the connector.js file to the page - browser console says: too much recursion
Files are added as followd:[code]

[/code]

The grid is loaded and the data processor initialized like this:[code]mygrid.load(“grid_db_handler.ashx?ds=dat&ljid=F8EBF621-ECF3-44FD-A28B-578A2CC8E4FA”);

var dp = new dataProcessor(“grid_db_handler.ashx?ds=dat&ljid=F8EBF621-ECF3-44FD-A28B-578A2CC8E4FA”);

dp.defineAction(“error”, function (response)
{
log("[dataprocessor.error] "+ response.firstChild.nodeValue);
return true;
});

dp.enableDataNames(true);
dp.enableDebug(true);
dp.enableUTFencoding(true);
dp.setTransactionMode(“POST”, true);

dp.init(mygrid);[/code]

The grid conector code looks like this:[code] dhtmlxGridConnector connector = null;

        if (!String.IsNullOrEmpty(context.Request.QueryString["ds"]))
        {
            ds = context.Request.QueryString["ds"];
        }

        if (!String.IsNullOrEmpty(context.Request.QueryString["ljid"]))
        {
            ljid = context.Request.QueryString["ljid"];
        }

        string sql_select = "SELECT Id, Date, AppendixId, AccountType, AccountNumber, Description, Revenue, Expenses, Currency, VATCode FROM LedgerJournalTransactions WHERE (Dataset = '"+ Tools.EscapeQueryValue(ds) +"' AND LedgerJournalId = '"+ Tools.EscapeQueryValue(ljid) +"') ORDER BY Id;";


        connector = new dhtmlxGridConnector(
            sql_select, 
            "Id",
            dhtmlxDatabaseAdapterType.SqlServer2005,
            ConfigurationManager.ConnectionStrings["DB"].ConnectionString);                

            connector.Request.TransactionMode = TransactionMode.PerRecord;

            connector.Begin += new EventHandler(connector_Begin);
            connector.End += new EventHandler<dhtmlxConnectors.RenderEventArgs>(connector_End);
       
            return connector;[/code]

dhtmlx_connector_log.zip (1.08 KB)

#4

Nobody is able to see what is causing these issues?

#5

Hello,
can you please attach a demo so I could reproduce the issue?

Regarding the error logging, my previous reply was not complete, sorry.
the connector library has built-in log writer which uses System.Diagnostics.Trace. You can add a trace listener which will output all messages into log file:

Add system.diagnostics section into web config:

<system.diagnostics> <trace autoflush="true" indentsize="3" > <listeners> <add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="D:\connector.log" traceOutputOptions="DateTime"></add> </listeners> </trace> </system.diagnostics>

Or you can do it from code on application start:

System.Diagnostics.Trace.AutoFlush = true; dhtmlxConnectors.Log.Listeners.Add(new System.Diagnostics.TextWriterTraceListener("D:\\connector.log", "myListener")); dhtmlxConnectors.Log.Enabled = true;

#6

In your email you write that I should provide the full tracer log OR a complete demo - and since creating a complete demo with database etc. would take some time, you get the full log along with some additional items.
So, please take a look at it before telling me to create a demo, because I’m sure the answer is in there - the browser console more or less says what the problem is, just not what is causing it.

The log shows that the values do arrive in the connector, but for some reason they are not used properly.

data_processor_debug.png1048×260 21.3 KB


connector.zip (1.78 KB)

#7

It looks like the client-side uses different column names than expected by the server, connector couldn’t map request parameters to column values.
When you call grid.setColumnIds, try using the same column names that are returned from your sql query:
JS

 mygrid.setColumnIds("AppendixId,AccountType,AccountNumber,Description,Revenue,Expenses,Currency,VATCode");

Or

JS

mygrid.setColumnIds("appendix_id,account_type,account_number,...");

SQL SELECT Id, Date as date, AppendixId as appendix_id, AccountType as account_type, .., FROM
Does it do any difference?

#8

I believe it does make a difference, but now the problem seems to be that the connector is unaware of the data types of the columns in the data table.

See this part of the log (the update request):16:12:25 DataRequest: Processing client request. QueryString: ds=dat&ljid=F8EBF621-ECF3-44FD-A28B-578A2CC8E4FA&gr_id=4&Id=4&Date=2016-06-23+00%3a00%3a00&AppendixId=3&AccountType=Account+Type+2&AccountNumber=7891011&Description=some+message+stuff+here...&Revenue=0&Expenses=200%2c00&Currency=DKK&VatCode=DKMOMS&!nativeeditor_status=updated&dhxr1467814345944=1, Form: 16:12:25 DataRequest: Resolving type of request. 16:12:25 DataRequest: Parsing data actions. 16:12:25 DataRequest: Now parsing data action for record with id=. 16:12:25 DataRequest: Extracting new column values for rowID= 16:12:25 dhtmlxGridConnector: Encoding query field: ds 16:12:25 dhtmlxGridConnector: Encoding query field: ljid 16:12:25 dhtmlxGridConnector: Encoding query field: Id 16:12:25 dhtmlxGridConnector: Encoding query field: Date 16:12:25 dhtmlxGridConnector: Encoding query field: AppendixId 16:12:25 dhtmlxGridConnector: Encoding query field: AccountType 16:12:25 dhtmlxGridConnector: Encoding query field: AccountNumber 16:12:25 dhtmlxGridConnector: Encoding query field: Description 16:12:25 dhtmlxGridConnector: Encoding query field: Revenue 16:12:25 dhtmlxGridConnector: Encoding query field: Expenses 16:12:25 dhtmlxGridConnector: Encoding query field: Currency 16:12:25 dhtmlxGridConnector: Encoding query field: VatCode 16:12:25 dhtmlxGridConnector: Encoding query field: dhxr1467814345944 16:12:25 DataRequest: Extracted values are: [Date = 2016-06-23 00:00:00,AppendixId = 3,AccountType = Account Type 2,AccountNumber = 7891011,Description = some message stuff here...,Revenue = 0,Expenses = 200,00,Currency = DKK] 16:12:25 DataRequest: Extracting UserData for rowID= 16:12:25 DataRequest: Extracted UserData is: [] 16:12:25 DataRequest: Parsed new action: Action: type=updated,sid: 4,tid: 4, Details: 16:12:25 DataRequest: Calling BeforeDataActionProcessing event(Action = [Action: type=updated,sid: 4,tid: 4, Details: ] 16:12:25 DataRequest: Processing data action (Action = [Action: type=updated,sid: 4,tid: 4, Details: ] 16:12:25 DataRequest: Entering PerRecord transaction mode 16:12:26 DataRequest: Calling BeforeUpdate event(Action = [Action: type=updated,sid: 4,tid: 4, Details: ] 16:12:26 DataRequest: Executing update action. 16:12:26 MSSQLAdapter: Entering: ExecuteUpdateQuery 16:12:26 MSSQLAdapter: Creating UpdateQuery from TableName: LedgerJournalTransactions , Fields-Values: Date = 2016-06-23 00:00:00, AppendixId = 3, AccountType = Account Type 2, AccountNumber = 7891011, Description = some message stuff here..., Revenue = 0, Expenses = 200,00, Currency = DKK, PrimaryKeyField: Id, PrimaryKeyValue: 4 16:12:26 MSSQLAdapter: Update query: UPDATE LedgerJournalTransactions SET Date = '2016-06-23 00:00:00', AppendixId = '3', AccountType = 'Account Type 2', AccountNumber = '7891011', Description = 'some message stuff here...', Revenue = '0', Expenses = '200,00', Currency = 'DKK' WHERE Id = '4' 16:12:26 MSSQLAdapter: Exception cought: Error converting data type varchar to numeric. 16:12:26 DataRequest: Calling DataActionProcessed event. Action=[Action: type=error,sid: 4,tid: 4, Details: Update operation failed with the following error: Error converting data type varchar to numeric.] 16:12:26 dhtmlxGridConnector: Rendering response 16:12:26 DataRequest: Enterering Render method. 16:12:26 dhtmlxGridConnector: Rendering data actions: [Action: type=error,sid: 4,tid: 4, Details: Update operation failed with the following error: Error converting data type varchar to numeric.] 16:12:26 dhtmlxGridConnector: Calling End event
Now the values appear to be correctly lifted off the request, but it seems that the SQL fails because the numeric values are sent as varchar values, like column = ‘value’ instead of just column = value, by the connector - The columns Revenue and Expenses are both of data type decimal but appear to be sent as if they were a varchar type (see the attached image of the data table design).
Both of these columns are also (in the grid) of type “edn” so I would expect the decimal separator to be the generic “.” and not “,” as shown in the log.
Data comes from the connector, so I seems that already when loading the data, these columns are treated as varchar and not numeric values… but why?

How to correct this - isn’t the connector supposed to be able to build insert, update and delete statements on it’s own based on the select statement and the data types of the columns in the result?
Or do I have the manually set custom statements for these actions?

Also, I still don’t know why adding the connector.js file to the page gives me the “too much recursion” error in the broser console. If I remove the comment on the middle line, the error occurs.[code]

[/code]

Is it because this code is already added through one of the other 2 files?

#9

Hello,
dhtmlxConnector does not have info on column types and treats values as string, expecting them to be implicitly converted to the appropriate types by the dbms.
As for formatting columns in grid - did you set a comma as a decimal separator in UI using grid.setNumberFormat? docs.dhtmlx.com/grid__formatting.html

If so, looks like dhtmlxGrid sends decimals to the backend in the same format they are entered in the editor, disregarding specified number format so if you specify comma as decimal separator and write a decimal in editor - dhtmlxGrid won’t use a format you provided in order to parse the entered value back to number.
It is a bug of dhtmlxGrid, please open a separate ticket in support system for it.

As a temporary workaround, you can preprocess these values on a backend, probably simple replacing commas with dots will do:

[code]public override IdhtmlxConnector CreateConnector(HttpContext context)
{

var connector = new dhtmlxGridConnector( …);
…
connector.BeforeInsert += new EventHandler(connector_BeforeProcessing);
connector.BeforeUpdate += new EventHandler(connector_BeforeProcessing);
…
}

void connector_BeforeProcessing(object sender, DataActionProcessingEventArgs e)
{
var revenueValue = e.DataAction.Data[(TableField)“Revenue”];
if (!string.IsNullOrEmpty(revenueValue))
{
e.DataAction.Data[(TableField)“Revenue”] = revenueValue.Replace(’,’, ‘.’);
}

var expensedValue = e.DataAction.Data[(TableField)"Expenses "];
if (!string.IsNullOrEmpty(expensedValue))
{
    e.DataAction.Data[(TableField)"Expenses"] = expensedValue.Replace(',', '.');
}

}[/code]

#10

Hmm yes something odd is going on indeed. I removed any number formatting in the grid and the values are still formatted with comma as the decimal separator.
So, whether it is the grid that does this on it’s own or it is the connector that delivers the data already separated with comma, I don’t know. I have used the grid before without having this issue, but in those scenarios I was the one delivering the data, not the connector.

My local culture does use comma as decimal separator, but the data in the database should not be affected by this, at least that would be a first if that was the case.

If I use my own code to select the data from the database and and generate a JSON-response for the client, the data looks as expected in regard to decimal separators (note that the decimal separator in NOT a comma) disregard the slightly different attribute names as these rows are objects of my own class:[ { "id":2, "date":"2017-11-23 00:00:00", "appendix_id":"1", "account_type":"Account Type 2", "account_number":"123456", "description":"text here...mooh!", "revenue":500.5, "expenses":100.5, "currency":"DKK", "vat_code":"DKMOMS" }, { "id":4, "date":"2016-06-23 00:00:00", "appendix_id":"2", "account_type":"Account Type 3", "account_number":"7891011", "description":"some message stuff here...", "revenue":100.25, "expenses":500.75, "currency":"DKK", "vat_code":"SEMOMS" } ]
So, I’m unsure as to where the issues is located because I cannot see what it is that the connector is sending out to the client - It would be interesting to see if the comma comes from the data or if it is the grid that adds it when the data is loaded.

I’m thinking that if the connector is treating everything as string and the collation of the database dictates that the visual decimal separator should be a comma (meaning that SQL Server Management Studio shows a comma but of cause the real value is a dot), then maybe the connector is delivering the data already containing this comma as decimal separator. If the value is a string then a comma as decimal separator wouldn’t create an error because the value isn’t a float or decimal but in fact a string in which a comma is allowed of cause.

Is there a way to examine the data that comes from the connector either previous to it being sent to the client or when the client receives it before loading it into the grid?
I would much like to see what is actually being sent to the client.

Also, using setNumberFormat - shouldn’t that only affect the visual presentation of a float or decimal value in the grid (both columns are of type “edn”)?