Sending database elements to grid

Tags: #<Tag:0x00007efc654dea78> #<Tag:0x00007efc654de7f8>
#1

I have a controller routine that retrieves data from a database. I want to send this data to the grid. The demo uses
var jsonData = new[]
{
new[] {“aa”, “bb”, "“cc”, “dd”}
In the Controller routine that access my database, the data is put into a
var log = new List();
.
.
logger.fldPrimaryKey = (reader.SafeGetInt(0).ToString());
log,Add(logger);

How would I create something like the jsonData New [] but use the reader data instead.

#2

Hi @joannplano2005

To update data back to Handsontable you have the following options

  • instance.loadData() which is the same as instance.updateSettings({data: new_data})
  • instance.setDataAtCell() for arrays and instance.setDataAtRowProp for objects

The first two methods require an array of arrays or objects (or a parsed JSON) and the two left need cell coordinates: row index, column index (or prop), the value. So I guess that it’s up to you which of those options sounds better.

The instance.setDataAtCell() / instance.setDataAtRowProp in a batch() has the same performance and if only a single change happens in the database this one is a batter option.

Do you want to load all the new data from the reader or update it once a record changes?

#3

I am using a jQuery.ajax routine to load in the database data. Here is the routine.
Handsontable.dom.addEvent(load, ‘click’, function () {
jQuery.ajax({
url: ‘/Home/getACLog’, //Controller to Get the
//JsonResult From – Json(jsonData, JsonRequestBehavior.AllowGet);
type: “GET”,
dataType: “json”,
contentType: ‘application/json; charset=utf-8’, // dataType and contentType should be json
async: true,
processData: false,
cache: false,
success: function (data) { // on Success send the Json data
// to the table by using loaddata function""
//alert(data);
//var rdata = JSON.parse(JSON.stringify(data));
//var rrdata = JSON.parse(JSON.stringify(data));
hot.loadData(data);
exampleConsole.innerHTML = ‘Data loaded’;
},
error: function (xhr) {
alert(‘error’);
}
});

Here is the controller file that it is accessing. Debug shows that the routine gets all the database data correctly. The problem is Handsometable doesn’t see the data. In stead I get an error.

  public JsonResult getACLog()
    {
        var log = new List<ACLog>();
        String[] array = new String[10];
        
        OdbcCommand command = new OdbcCommand("SELECT fldPrimaryKey, fldCall, fldDateStr, fldTimeOnStr, fldMode, fldState, fldRstR, fldRstS, fldBand FROM tblContacts;");
        using (OdbcConnection connection = new OdbcConnection("Driver={Driver do Microsoft Access (*.mdb)};dbq=C:\\Users\\User\\MasterLog.mdb;defaultdir=C:\\Users\\User;driverid=25;fil=MS Access;filedsn=C:\\Users\\User\\MasterLog.mdb.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;uid=admin;usercommitsync=Yes"))
        {
            command.Connection = connection;
            connection.Open();

            using (var reader = command.ExecuteReader())
            {

                    while (reader.Read())
                {
                    var logger = new ACLog();

                    logger.box = "false";

                    logger.fldPrimaryKey = (reader.SafeGetInt(0).ToString());
                    logger.fldCall = reader.SafeGetString(1);
                    logger.fldDateStr = reader.SafeGetString(2);
                    logger.fldTimeOnStr = reader.SafeGetString(3);
                    logger.fldBand = reader.SafeGetString(4);
                    logger.fldMode = reader.SafeGetString(5);
                    logger.fldState = reader.SafeGetString(6);
                    logger.fldRstR = reader.SafeGetString(7);
                    logger.fldRstS = reader.SafeGetString(8);
                    


                    log.Add(logger);

                }
            };
        }
        return Json(log, JsonRequestBehavior.AllowGet);
    }
#4

Doing some more investigation, I found that If I remove the checkbox in Column_1, that my getACLog function will load up to a little over 10,000 records. Beyond that it fails with a popup message “Localhost50850 says error”. So getting closer.

#5

Do you maybe get anything more than this? It would be hard to specify anything based on such an error. The only thing that comes to my mind is that maybe there’s an infinite loop somewhere.

#6

When I attempt to load more than 10K rows, it pops this error. Also the Json Save routine stops working.

#7

I found some details on the Internet that may confirm the theory about the infinite loop as people claimed that it might be either too many requests or asking for one without getting any response.

Without access to your application, I do not know if I’ll be able to tell you anything more than that.

#8

Would it help to see my program and database. I could setup a Dropbox folder and send you the link.

#9

Sure. We can organize a code review, but I would need to confirm your current support plan first. Could you please share your license holder or license ID via support@handsontable.com?

#10