Updating a db table from a dataset

Classic Servoy
1

Good morning from Gran Canaria.

After getting a dataSet can I update a foundset from it. I mean create the new records and update the existing ones?

If you are asking why from a dataset to a foundset, it´s easy that dataset is retrieved from a file with the it2be data plugin.

Thanks.

2

Does the dataset hold any PK information ?
If so than it’s fairly simple.
Use foundset.loadRecords(PKvalue), when returning 0 records you need to create a new one. When it does return a record you can update it.

Hope this helps.

3

Thanks Robert, I can know if a record exist or not, but is there any way to update the foundset rather that setting the values field by field?

Thanks

4

I don’t think there is a way to update all the fields in the record with a single command.
If the dataset has fieldnames you could loop thought it like so:

for ( var i = 1 ; i <= ds.getMaxRowIndex() ; i++ ) {
    foundset.loadRecord(ds.getValue(i,1)); // assuming the first column is the PK
    if ( foundset.getSelectedIndex() ) {
        // starting at column 2, no need to update the PK
        for ( var j = 2 ; i <= ds.getMaxColumnIndex() ; j++ ) {
            foundset[ds.getColumnName(j)] = ds.getValue(i, j);
        }
    }
}

Or when the dataset is always using the same fields but doesn’t hold the column names you could define an array on your code and use that.

Hope this helps.