Hi,

within my apps there are 3 tables, called :
- function (functionID,functionName)
- item (itemID, itemName)
- functionItem (fi_id, fi_itemID, fi_functionID)

Based on 3 dbgrids including checkboxes i select on the itemgrid/table the items which i would like
to append to the functiongrid/table

There are 2 buttons :
- one for append the selected items to tabel functionItems
- one for delete the selected items from tabel functionItems

I use the code below, which works fine. The tabel FunctionItems has a unique index
to prevent double record/key. There's a onPostError for canceling the operation when
there's a double key. The tabel data for functionItems looks something like this:

fi_itemID     fi_functionID
1                    10
1                    11
2                    20
2                    21
2                    22
2                    23

What happends ?
- When i select items which are not added to functionItem, all items are append (correct)
- When i select items which are already added to functionItems, they are NOT added (correct)
- When i select 1 item which is already added and 1 item which is NOT added, then the missing
  items are not added to functionItems, i think because of the cancel operations on the OnPostError event
  Skipping the OnPostError causes a error from the database engine instead of my own message,


The code i use for adding record is below. I i can't figure out if i can somewhere check if a items
is already part of FunctionItems before appending them or how i can build this functionality.
Basicly what i need is a solution where i can select items in a left grid and append to the right grid
and all items which not exists in the right grid have to be added.


procedure TFormFunctieItem.button_selectieAddClick(Sender: TObject);
var
 i: Integer;
begin
 if SMDBGrid_items.SelectedRows.Count = 0 then begin
    Application.MessageBox('You must selected items', 'Selection', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
 end;
 if SMDBGrid_items.SelectedRows.Count > 0 then begin
    with SMDBGrid_items.DataSource.DataSet do begin
       for i := 0 to SMDBGrid_items.SelectedRows.Count-1 do begin
         GotoBookmark(Pointer(SMDBGrid_items.SelectedRows.Items[i]));
            dmItem.qry_itemBYfunctie.Append;
            dmItem.qry_itemBYfunctie.FieldByName('functieitem_functieID').value:=dmOverig.qry_functie.FieldByName('functie_id').value;
            dmItem.qry_itemBYfunctie.FieldByName('functieitem_itemID').value:=dmItem.qry_item_up.FieldByName('item_id').value;
       end;
     end;
     SMDBGrid_items.UnSelectAllClick(sender);
  end;
end;

Peter

As you're finding out its dangerous to rely on the implicit post. If nothing else the last item you append may never get posted.

Alter your procedure as below and get rid of the onpost event


>procedure TFormFunctieItem.button_selectieAddClick(Sender: TObject);
>var
> i: Integer;
>begin
> if SMDBGrid_items.SelectedRows.Count = 0 then begin
> Application.MessageBox('You must selected items', 'Selection', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
> end;
> if SMDBGrid_items.SelectedRows.Count > 0 then begin
> with SMDBGrid_items.DataSource.DataSet do begin
> for i := 0 to SMDBGrid_items.SelectedRows.Count-1 do begin
> GotoBookmark(Pointer(SMDBGrid_items.SelectedRows.Items[i]));
> dmItem.qry_itemBYfunctie.Append;
> dmItem.qry_itemBYfunctie.FieldByName('functieitem_functieID').value:=dmOverig.qry_functie.FieldByName('functie_id').value;
> dmItem.qry_itemBYfunctie.FieldByName('functieitem_itemID').value:=dmItem.qry_item_up.FieldByName('item_id').value;
try
dmItem.qry_itemBYfunctie.post;
except
dmItem.qry_itemBYfunctie.cancel;
end;
> end;
> end;
> SMDBGrid_items.UnSelectAllClick(sender);
> end;
>end;

You might even be better testing for an items existence in dmItem.qry_itemBYfunctie before you try and append it. Depending on how you're doing your selection you might even be able to prevent an item being selected if it already exists in dmItem.qry_itemBYfunctie.

Roy Lambert

Hi Roy,

Thanks...everyday i'm still learning here...great and thanks
for the solutions, it did the trick i needed...

Peter

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Peter

As you're finding out its dangerous to rely on the implicit post. If nothing else the last item you append may never get posted.

Alter your procedure as below and get rid of the onpost event


>procedure TFormFunctieItem.button_selectieAddClick(Sender: TObject);
>var
> i: Integer;
>begin
> if SMDBGrid_items.SelectedRows.Count = 0 then begin
> Application.MessageBox('You must selected items', 'Selection', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
> end;
> if SMDBGrid_items.SelectedRows.Count > 0 then begin
> with SMDBGrid_items.DataSource.DataSet do begin
> for i := 0 to SMDBGrid_items.SelectedRows.Count-1 do begin
> GotoBookmark(Pointer(SMDBGrid_items.SelectedRows.Items[i]));
> dmItem.qry_itemBYfunctie.Append;
> dmItem.qry_itemBYfunctie.FieldByName('functieitem_functieID').value:=dmOverig.qry_functie.FieldByName('functie_id').value;
> dmItem.qry_itemBYfunctie.FieldByName('functieitem_itemID').value:=dmItem.qry_item_up.FieldByName('item_id').value;
try
dmItem.qry_itemBYfunctie.post;
except
dmItem.qry_itemBYfunctie.cancel;
end;
> end;
> end;
> SMDBGrid_items.UnSelectAllClick(sender);
> end;
>end;

You might even be better testing for an items existence in dmItem.qry_itemBYfunctie before you try and append it. Depending on how you're doing your
selection you might even be able to prevent an item being selected if it already exists in dmItem.qry_itemBYfunctie.

Roy Lambert