What makes a dataset read-only?
lazarus / 16-Databases

----------------------------------------------------------------------
AKCarlow · 2014-07-27 14:30

I have two separate dbGrids on a form, each populated by its own
SQLQuery, each showing data from different tables in the same local
MySQL 5 database. On one of them I can edit a cell by clicking on it;
when I ApplyUpdates the changes are written to the database.

On the other clicking a cell does highlight it, but I cannot edit it.
One of the columns is for a TinyInt (boolean) field and is correctly
showing as checkboxes. I have an OnClick handler for this grid - if I
click anywhere in the grid, I get the pirate 1 message, and if I click
in the Estimate (TinyInt) column I get pirate 1 and 2, but not 3, and
the program crashes with a "dataset is read-only" error. As far as I
can see, the settings for things like Active and Enabled on the
various components are the same in both cases. What am I doing wrong? 

Code: [Select]procedure TfShareReg.dbgUsageCellClick(Column: TColumn);
begin
ShowMessage('pirate 1');
If Column.FieldName = 'Estimate' then
  begin
  ShowMessage('pirate 2');
  qUsage.Active:=true;
  qUsage.Edit;
  ShowMessage('pirate 3');
  if Column.Field.OldValue = 0 then
     begin //do estimate
     showmessage('make an estimate');
     Column.Field.NewValue := 1;
     end {do estimate}
  else
      begin {undo estimate}
      showmessage('put it back');
      Column.Field.Value:=0;
      end; {undo estimate}
  qUsage.ApplyUpdates;
  end;
end;

----------------------------------------------------------------------
BigChimp · 2014-07-27 14:32

Do the insertsql, deletesql and updatedsql properties contain the
relevant SQL? If not (e.g. when you have a more complicated query than
a simple select), you'll have to fill those properties yourself...

----------------------------------------------------------------------
AKCarlow · 2014-07-27 14:52

In both cases InsertSQL etc are empty, but ParseSQL is true. Both
cases have a parameterised WHERE clause - the parameter is set by a
DataChange event handler in another data set eg
Code: [Select]  qUsage.Close;
  qUsage.Params.ParamByName('paramShare').AsString:=dbtxtShareNo.Capti
on;
  qUsage.Open;

Both grids properly show the expected values for the selected row in
the other dataset.

----------------------------------------------------------------------
LacaK · 2014-07-28 05:25

What is in qUsage.SQL.Text ?

----------------------------------------------------------------------
AKCarlow · 2014-07-28 22:30

select
ShareNo,ReadingDate,Connection,Reading,Previous,Note,Estimate,Actual,
Reading-Previous as M3Used from Connections join MeterReadings on
ConnectionNo = Connection where ShareNo = :paramShare order by
Connection,ReadingDate desc;

----------------------------------------------------------------------
LacaK · 2014-07-29 05:09

Then it is expected behavior, that dataset is ReadOnly.
If SQL is multi-table select (better say something else than single
table select), then you must supply own InsertSQL, UpdateSQL,
DeleteSQL to let sqlDB know which tables to update.

----------------------------------------------------------------------
AKCarlow · 2014-07-29 10:16

Ahh, thank you. I can do that. Time to move on so and find the next
hurdle!