How to edit Date in grid attached to Web SQL

[Martin]

Hi, I am having problem with editing date in grid. Date is stored in DB as SQLite type, it is shown in select result as yyyy-MM-dd. I have already some data in DB from my other project and I used this function in SQL to store date

"...Date = STRFTIME('%Y-%m-%d', '?')..."

Kendo UI Grid shows date as yyyy-MM-dd so I change the format to:

{ field: "TestDate", title: "Datum", template: "#= kendo.toString(kendo.parseDate(TestDate, 'yyyy-MM-dd'), 'dd.MM.yyyy') #" }

When I try to edit row, the DatePicker value is empty. Doesn't show the value from TestDate column and the DatePicker is set to "now" date. When I change the date and click on Update nothing happend. I can see new value in grid, but the edit window is still open and I get bact the old value after refresh .
Update function:

function update(db, model, callback)
{
  db.transaction(function (tx)
  {
    tx.executeSql("update Costs set Amount = ?, Date = STRFTIME('%Y-%m-%d', '?'), CostTypeId = ?, CurrencyId = ?, CountryId = ?, Description = ?, CZK = NULL where Id = ?",
                  [model.Amount, model.TestDate, model.CostTypeId, model.CurrencyId, model.CountryId, model.Description, model.ID], function (tx, result)
                  {
                    callback([]);
                  });
  });
}

So my question is:
1. How to set DatePicker value to date which I am editing when the edit window popup?
2. Which date format I should use to select, update and insert data to WebSQL and where and how I should do that.

Thank you for your answer :)

[Martin]

nobody knows? I can't continue without this in my project and I don't have much time. Please somebody.

[Martin]

hey guys, i just found out that if I am using kendo.all.min.js version 2012.3.1114, value in DatePicker is set to the input when I am editing record, but if I use version 2013.3.1324, the value is empty. Is that a bug?
http://jsbin.com/azukin/208/edit

[Atanas Korchev]

Hi Martin,

The problem occurs because the web sql result object is readonly and the parse method of the datum field tries to modify it. The solution is to make the toArray function clone the result object so it is no longer read only.

Here is the updated code (couldn't save the jsbin due to time our errors):

var db = openDatabase('kendodb', '1.0', 'Kendo database', 2 * 1024 * 1024);

db.transaction(function (tx) {
  tx.executeSql('DROP TABLE products');
  tx.executeSql('CREATE TABLE products (id INTEGER PRIMARY KEY, name, datum datetime)');
  tx.executeSql('INSERT INTO products (name, datum) VALUES("Coffee", date("now"))');
  tx.executeSql('INSERT INTO products (name, datum) VALUES("Milk", date("2014-02-18"))');

});

function toArray(result) {
  var length = result.rows.length;

  var data = new Array(length);

  for (var i = 0; i < length; i++) {
    data[i] = $.extend({}, result.rows.item(i));
  }

  return data;
}

function read(db, callback) {
   db.transaction(function(tx) {
     tx.executeSql('SELECT id, name, datum from products', [], function(tx, result) {
       callback(toArray(result));
     });
   });
}

function update(db, model, callback) {
  db.transaction(function(tx) {
    tx.executeSql("UPDATE products SET name=?, datum=? WHERE id=?",
                  [model.name, model.datum, model.id], function(tx, result) {
       callback([]);
    });
  });
}

function create(db, model, callback) {
  db.transaction(function(tx) {
    tx.executeSql('INSERT INTO products (name, datum) VALUES(?)',
                  [model.name, model.datum], function(tx, result) {
       callback([{ id: result.insertId }]);
    });
  });
}

function destroy(db, model, callback) {
  db.transaction(function(tx) {
    tx.executeSql('DELETE FROM products WHERE id=?',
                  [model.id], function(tx, result) {
       callback([]);
    });
  });
}

var dataSource = new kendo.data.DataSource({
  transport: {
    read: function(options) {
      //options.success([
//        { id: 1, name: "foo", datum: "2014-02-24" }
//      ]);
      read(db, options.success);
    },
    create: function(options) {
      create(db, options.data, options.success);
    },
    update: function(options) {
      update(db, options.data, options.success);
    },
    destroy: function(options) {
      destroy(db, options.data, options.success);
    }
  },
  schema: {
    model: {
      id: "id",
      fields: {
        id: { editable: false },
        name: { type:"text", validation: { required: true } },
        datum: {
          type: "date",
          parse: function(value)
          {
            return kendo.parseDate(value, 'yyyy-MM-dd');
          }
        }
      }
    }
  }
});


$("#grid").kendoGrid({
  dataSource: dataSource,
  columns: [
    { field: "id" },
    { field: "name" },
    { field: "datum", title: "Datum", format:"{0:yyyy-MM-dd}"},
    { command: "edit"},
    { command: "destroy"}
  ],
  editable: "popup",
  toolbar: ["create", "save"]
});

Regards,
Atanas Korchev
Telerik

[Martin]

thank you soooooooooo much.
It is working in JS Bin with new database perfectly :)
I just have to find out why is not working on my old DB. Update is ok, without any error, but it is like the DB is read only. I have old values back after refresh. I used this DB in Windows Mobile app and now I am making Android version with existing data.