Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Copy records between DBGRID (desperate!)
Thu, Jul 3 2008 6:43 PMPermanent Link

Peter van mierlo
hi,

I had a earlier post regarding this problem and thought it was solved, but not, and for me it's critical because
for our festival we are going next thursday live..so i'm a little bit desperate.

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. What happends ?
- When i select items which are not added to functionItem, all items are append (correct)
- When i select items which are alread added to functionItem, the apps shutsdown

Oke...Roy already answered the earlier thread, but i can't find a working solution for
it and can't figure out at what point and how i can find out if a item already exists.
and if it exists, then skip the item and go to the next item


procedure TFormUitgifteUpgrade.button_selectieAddClick(Sender: TObject);
var
i: Integer;
begin
if SMDBGrid_items.SelectedRows.Count = 0 then begin
    Application.MessageBox('Er zijn zijn géén items geselecteerd om toe te voegen.'+#10+''+#10+'Selecteer in het overzicht eerst de items
waarvan'+#10+'u deze toe kunt voegen.', 'Selecteer gegevens', 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]));
        dmTables.qry_itemBYupgrade.Append;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_nawID').value:=dmTables.qry_uitgifte.fieldbyname('naw_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_functieID').Value:=dmTables.qry_uitgifte.FieldByName('naw_functieID').Value;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_itemID').Value:=dmTables.qry_item_up.FieldByName('item_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_itemAantal').Value:=dmTables.qry_item_up.FieldByName('item_aantal').Value;
      try
         dmTables.qry_itemBYupgrade.post;
      except
         dmTables.qry_itemBYupgrade.cancel;
      end;
   end;
 end;
 SMDBGrid_items.SelectedRows.clear;
 dmTables.qry_itemBYupgrade.refresh;
end;
end;
Fri, Jul 4 2008 3:53 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Peter


I'm not sure I understand exactly what you're doing, or what's going on.

You quote three tables but I can't figure out how they relate to your code. Also you say

- When i select items which are alread added to functionItem, the apps shutsdown

I assume you've tried tracing in the debugger? If so what happens, what error message do you get, on what line?

Roy Lambert [Team Elevate]
Fri, Jul 4 2008 5:31 AMPermanent Link

Peter van Mierlo
Hi,

That's may mistake about the 3 tables...oke...the whole store

- We have ''persons'' in a separete table. Each person has a ''function''...lets say barkeeper
- We have a table ''functions'' which contains functions like above (field fun_id and fun_name)
- We have a table ''items'' which contains items like t-shirt, wristband yellow, wristband red (field item_id, item_name)
- We have a tables ''function items'' where the function and items are connected, so for each function
  you can add one or more items. So..a function ''barkeeper'' has the items 'wristband and t-shirt'

This works all fine. But now most difficult problem. It's also possible to do a ''personal upgrade'' for a person. Those upgrades a stored in
tabel ''personal upgrade'' and contains nothing else then new or replaced items from the table ''items''. Let's say Roy is a barkeeper and
has wristband yellow which gives access to the festival field. Roy is a good guy and i would like to give him access to the guest/press area.
There for he needs wristband red.

We use the screen which is attached to this thread. When showing this screen the first thing which is needed is to make visible which items Roy
has based on his function. There for we copy the records from ''function items'' to ''personal upgrade'' whith the followin code :

procedure TdmTables.UpgradeItemsToevoegen;
var
 i : integer;
begin
 if dmTables.qry_itemBYuitgifte_up.recordCount > 0 then begin
    with dmTables.ds_itemBYuitgifte_up.DataSet do begin
      for i := 0 to dmTables.qry_itemBYuitgifte_up.RecordCount-1 do begin
        dmTables.qry_itemBYupgrade.Append;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_nawID').value:=dmTables.qry_uitgifte.fieldbyname('naw_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_functieID').Value:=dmTables.qry_uitgifte.FieldByName('naw_functieID').Value;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_itemID').Value:=dmTables.qry_itemBYuitgifte_up.FieldByName('functieitem_itemID').Value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_itemAantal').Value:=dmTables.qry_itemBYuitgifte_up.FieldByName('showItemAantal').Value;
      try
         dmTables.qry_itemBYupgrade.post;
      except
         dmTables.qry_itemBYupgrade.Cancel;
      end;
      dmTables.qry_itemBYuitgifte_up.next;
    end;
   end;
 end;
end;


When Roy never has had a upgrade, there are NO records in the table ''personal upgrade'', so the first time the records are
copied...no problem and the upgrade screen appears. When Roy has already had a upgrade...TERMINATE application.

Oke...Roy has never had a upgrade so records are copied from ''function items'' to ''personal_upgrade'' Now the upgrade screen
appears and i can add new items to the upgrade section....this also works TILL i add a item which already exists in the ''personal upgrade''
(right dbgrid in the form) and the applications will be terminated. Adding new items from the left to the right dbgrid will be done with the
following code, because you can use multi-select.

procedure TFormUitgifteUpgrade.button_selectieAddClick(Sender: TObject);
var
i: Integer;
begin
if SMDBGrid_items.SelectedRows.Count = 0 then begin
    Application.MessageBox('Er zijn zijn géén items geselecteerd om toe te voegen.'+#10+''+#10+'Selecteer in het overzicht eerst de items waarvan'+#10+'u deze toe kunt voegen.', 'Selecteer gegevens',
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]));
        dmTables.qry_itemBYupgrade.Append;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_nawID').value:=dmTables.qry_uitgifte.fieldbyname('naw_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_functieID').Value:=dmTables.qry_uitgifte.FieldByName('naw_functieID').Value;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_itemID').Value:=dmTables.qry_item_up.FieldByName('item_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_itemAantal').Value:=dmTables.qry_item_up.FieldByName('item_aantal').Value;
      try
         dmTables.qry_itemBYupgrade.post;
      except
         dmTables.qry_itemBYupgrade.cancel;
      end;
   end;
 end;
 SMDBGrid_items.SelectedRows.clear;
 dmTables.qry_itemBYupgrade.refresh;
end;
end;

I have to check if a items is already added to the ''personal upgrade'' when using the code above
I use a unique index to prevent adding records which already exist and here i think it's going wrong.
I can't figure out HOW and at WHAT point to do this check

Please help me with a little coding to create a solution, because i'm very desparate on how
to solve this problem before thuesday.







Table 1 : (contains items like t-shirt, writstband etc
- item_id
- item_name
- item_number

Table 2
- naw_itemID

DBGrid 1 is connected to table 1 :
DBGrid 2 is connected to table 2



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

Peter


I'm not sure I understand exactly what you're doing, or what's going on.

You quote three tables but I can't figure out how they relate to your code. Also you say

- When i select items which are alread added to functionItem, the apps shutsdown

I assume you've tried tracing in the debugger? If so what happens, what error message do you get, on what line?

Roy Lambert [Team Elevate]
Fri, Jul 4 2008 5:32 AMPermanent Link

Peter van Mierlo
the attachment

Peter van mierlo <p.mierlo@planet.nl> wrote:

hi,

I had a earlier post regarding this problem and thought it was solved, but not, and for me it's critical because
for our festival we are going next thursday live..so i'm a little bit desperate.

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. What happends ?
- When i select items which are not added to functionItem, all items are append (correct)
- When i select items which are alread added to functionItem, the apps shutsdown

Oke...Roy already answered the earlier thread, but i can't find a working solution for
it and can't figure out at what point and how i can find out if a item already exists.
and if it exists, then skip the item and go to the next item


procedure TFormUitgifteUpgrade.button_selectieAddClick(Sender: TObject);
var
i: Integer;
begin
if SMDBGrid_items.SelectedRows.Count = 0 then begin
    Application.MessageBox('Er zijn zijn géén items geselecteerd om toe te voegen.'+#10+''+#10+'Selecteer in het overzicht eerst de items
waarvan'+#10+'u deze toe kunt voegen.', 'Selecteer gegevens', 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]));
        dmTables.qry_itemBYupgrade.Append;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_nawID').value:=dmTables.qry_uitgifte.fieldbyname('naw_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_functieID').Value:=dmTables.qry_uitgifte.FieldByName('naw_functieID').Value;
        dmTables.qry_itemBYupgrade.FieldByName('nawItem_itemID').Value:=dmTables.qry_item_up.FieldByName('item_id').value;
        dmTables.qry_itemBYupgrade.FieldByName('nawitem_itemAantal').Value:=dmTables.qry_item_up.FieldByName('item_aantal').Value;
      try
         dmTables.qry_itemBYupgrade.post;
      except
         dmTables.qry_itemBYupgrade.cancel;
      end;
   end;
 end;
 SMDBGrid_items.SelectedRows.clear;
 dmTables.qry_itemBYupgrade.refresh;
end;
end;



Attachments: upgrade.png
Fri, Jul 4 2008 7:57 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Peter


Looking at the code you're posting I can see no reason for the app to explode in your face as it is doing. Its almost certainly a linked control or event that's causing the problem.

Apart from SMDBGrid what components are you using. If I have the same ones I can offer to look at your code for you and see if I can spot the problem. I'll add your email address to my whitelist.

But to return to my earlier question. Have you tried stepping through in the debugger. If so which line does the problem occur on and what is the error message.

Its easy enough to test if a record exists in a table:

1. drop another TDBISAMTable component onto your datamodule
2. point it at the table you want to check in
3. in the app open it
4. when you want to test use .Locate and the fields that make the record unique

As to where to put a test I don't know, because I don't yet know what's causing the problem.

Roy Lambert
Image