I am having a problem saving an update to a dataset that uses a query with params as the row source.  I used a query with params so I can "filter" the dataset to a range of dates.

Form1 has dataset "ds_date_range" which gives me the range of dates in the incidents table.  It has this query as the row source:

SELECT MAX(incident_date) AS max_date, MIN(incident_date) AS min_date FROM incidents;


Form1 also has the dataset ds1 which supplies the data to Ga grid. The row source for ds1 is this query:

SELECT * FROM incidents WHERE incident_date BETWEEN DATE{BegDate=''} AND DATE{EndDate=''}

This is the rest of the pertinent code from Form1:

procedure TForm1.LoadDateRange(boolFilter: boolean);
var
  strBegDate : string;
  strEndDate : string;
begin
  //remove filter
  if boolFilter = false then
     begin
        strBegDate := IntToStr(YearOf(StrToDate(ds_date_range.columns['min_date'].AsString)))+'-'+
                       IntToStr(MonthOf(StrToDate(ds_date_range.columns['min_date'].AsString)))+'-'+
                       IntToStr(DayOf(StrToDate(ds_date_range.columns['min_date'].AsString)));

        strEndDate := IntToStr(YearOf(StrToDate(ds_date_range.columns['max_date'].AsString)))+'-'+
                       IntToStr(MonthOf(StrToDate(ds_date_range.columns['max_date'].AsString)))+'-'+
                       IntToStr(DayOf(StrToDate(ds_date_range.columns['max_date'].AsString)));
     end;
  //apply filter
  if boolFilter = true then
     begin
        if (strToDate(Form1.Edit_BegDate.Text) = strToDate(Form1.Edit_EndDate.Text)) then
           begin
              showmessage('Dates Cannot Be The Same');
              exit;
           end
        else
           begin
              strBegDate := IntToStr(YearOf(StrToDate(Form1.Edit_BegDate.Text)))+'-'+
                             IntToStr(MonthOf(StrToDate(Form1.Edit_BegDate.Text)))+'-'+
                             IntToStr(DayOf(StrToDate(Form1.Edit_BegDate.Text)));

              strEndDate := IntToStr(YearOf(StrToDate(Form1.Edit_EndDate.Text)))+'-'+
                             IntToStr(MonthOf(StrToDate(Form1.Edit_EndDate.Text)))+'-'+
                             IntToStr(DayOf(StrToDate(Form1.Edit_EndDate.Text)));
           end;
     end;

  //load params
  ds1.Params.Clear;
  ds1.Params.Add('BegDate='''+strBegDate+'''');
  ds1.Params.Add('EndDate='''+strEndDate+'''');
  Database.Load(ds1);
end;

procedure TForm1.Form1Create(Sender: TObject);
begin
  Database.Load(ds_date_range);
end;

procedure TForm1.ds_date_rangeAfterLoad(Sender: TObject);
begin
  LoadDateRange(false);
end;

procedure TForm1.btnEditClick(Sender: TObject);
begin
  Form1.ds1.Update;
  Form2.ShowModal;
end;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

All of this works great.  I have button on Form1 that launches Form2.  I have the components on Form2 tied to the same record set, ds1, that Grid1 on Form1 uses.  Here is the pertinent code from Form2 (BTW, ds3 is just used to load a combobox on From2):

procedure TForm2.Form2Create(Sender: TObject);
var
  i : integer;
begin
  for i := 0 to Controlcount -1 do
     begin
        if (controls[i] is TEdit) or (controls[i] is TMemo)  or
           (controls[i] is TImage) or (controls[i] is TComboBox) then
           begin
              controls[i].Dataset := Form1.ds1;
           end;
     end;
  Database.Load(ds3);
end;

procedure TForm2.btnSaveClick(Sender: TObject);
begin
  Database.StartTransaction;
     try
        Form1.ds1.Save;
        Database.Commit;
        Form2.Close;
     except
        Database.Rollback;
        raise;
     end;
end;


@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

I have tried everything I can think of to get the update to work. I know it has to be that the params are missing because if I can rewrite the query for ds1 with static date values for the params like this,

SELECT * FROM incidents WHERE incident_date BETWEEN DATE{BegDate='2014-10-01'} AND DATE{EndDate='2014-10-31'}

, and it works fine.

How can I make this work?  The error message I get is attached.  

Thanks,
TD

I know it is a long post but I would greatly appreciate it if you guys would take a look and see if you can help me out with this issue.

As always, thanks for your help,
TD

I've not played with params, but the DATE format needs the keyword and
the date with quotes. The error dialog suggests that there is something
wrong with the quotes, so I'd be looking at that. Should you query have
the quotes around the params, and the param be the plain string for
example?

--

Matthew Jones

Matthew,

> The error dialog suggests that there is something
> wrong with the quotes, so I'd be looking at that.

The param is just empty. That seems to be the problem. (...instead found
DATE '')

Uli

Uli Becker wrote:

Matthew,

> The error dialog suggests that there is something
> wrong with the quotes, so I'd be looking at that.

The param is just empty. That seems to be the problem. (...instead found
DATE '')

Uli

Yes, that is the problem I think BUT how would one pass these params to the dataset again now that the dataset exists?

"Matthew Jones" wrote:

I've not played with params, but the DATE format needs the keyword and
the date with quotes. The error dialog suggests that there is something
wrong with the quotes, so I'd be looking at that. Should you query have
the quotes around the params, and the param be the plain string for
example?

--

Matthew Jones

If you mean DATE'{BegDate=''}' then I tried that and it failed also.

Thanks for trying to help,
TD

> Yes, that is the problem I think BUT how would one pass these params to the dataset again now that the dataset exists?

Your post is a bit confusing - actually I don't understand what exactly
the problem is.

The error you attached, occurs when loading the dataset. I can't see
what that has to do with updating the database.

If you load a dataset after updating it, you normally load it again and
then have to add the params.

Uli

Uli Becker wrote:

> Yes, that is the problem I think BUT how would one pass these params to the dataset again now that the dataset exists?

Your post is a bit confusing - actually I don't understand what exactly
the problem is.

The error you attached, occurs when loading the dataset. I can't see
what that has to do with updating the database.

If you load a dataset after updating it, you normally load it again and
then have to add the params.

Uli

Sorry about  the confusion.  Dataset ds1 was loaded when Form1 is created.  On Form2 all of the TEdit's are connected to ds1 on Form1.  I am not trying to reload ds1, I am trying to save an update to it.  The error message indicates to me that in order for the update to succeed you would have to somehow supply the params again to ds1.  If this is the case I have no idea how to do that.  This is what I need help with.

TD

>   I am not trying to reload ds1, I am trying to save an update to it.  The error message indicates to me that in order for the update to succeed you would have to somehow supply the params again to ds1.  If this is the case I have no idea how to do that.  This is what I need help with.

On Form2: do you have databound edit controls for the dates?

Uli

Uli Becker wrote:

>   I am not trying to reload ds1, I am trying to save an update to it.  The error message indicates to me that in order for the update to succeed you would have to somehow supply the params again to ds1.  If this is the case I have no idea how to do that.  This is what I need help with.

On Form2: do you have databound edit controls for the dates?

Uli

The two date params get their values from two Edit controls on Form1.  The user enters the range of dates they wish to search and ds1 returns only that range of dates.

There is an Edit control on Form2 for the single date field that is in the incident table in the database.  There are additional Edit controls on Form2 for the other fields in the incident table.  

At design time none of the Edit controls are bound to ds1 on Form2, BUT, they are bound to ds1 on the Form2Create event per the code below.  

procedure TForm2.Form2Create(Sender: TObject);
var
  i : integer;
begin
  for i := 0 to Controlcount -1 do
     begin
        if (controls[i] is TEdit) or (controls[i] is TMemo)  or
           (controls[i] is TImage) or (controls[i] is TComboBox) then
           begin
              controls[i].Dataset := Form1.ds1;
           end;
     end;
end;

Hope this is answers your question Uli, and thanks for helping.
TD