• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

SQL

I have a grid that is populated with records that have been retrieved via a SQL query.

I need to be able to select only the records that I want to use either using a checkbox or the Multi-select on the grid.

I then need to find a way of copying the records that have been selected to a table.

Any ideas or examples would be great.
Many Thanks
0
SCOTT78
Asked:
SCOTT78
  • 9
  • 5
  • 3
  • +2
1 Solution
 
kretzschmarCommented:
just pasted from my comment of the q grid &sql below

set multiselct and rowselct to true in the dbgrid-options property



   QSource: TQuery;
   TDest: TTable;
   DSource: TDBGrid;

procedure TUForm.UBClick(Sender: TObject);
var i,j : Integer;
begin
if DSource.SelectedRows.Count > 0 then
begin
  for j := 0 to DSource.SelectedRows.Count - 1 do
  begin
    QSource.GotoBookmark(TBookMark(DSource.SelectedRows.Items[J]));
    TDest.Append;
    For i := 0 to QSource.FieldCount - 1 do
      TDest.Fields[i] := QSource.Fields[I];
    TDest.Post;
  end;
end;
end;


meikl ;-)

0
 
SCOTT78Technical AdvisorAuthor Commented:
Thanks Meikl but should I create any fields in the table or will the query copy set the fields in the table???
0
 
kretzschmarCommented:
the table and the targetfields must be exist
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
VENKATCommented:
kretzschmar is right.
0
 
trex_fireCommented:
So add to you query:

Select 0 as Choice, ....

This way you can manage the value inside the query without posting it to the database. You use Field Choice to determine if you use it or not.  By clicking the check box in the grid, value will change so you dont need anything else.

T-Rex
0
 
kretzschmarCommented:
ts ts ts ...
0
 
SCOTT78Technical AdvisorAuthor Commented:
I have used the code below in my app and it works great using a Select statement in the query.

        begin
          tblSagePost.Open;
          if grdScan.SelectedRows.Count > 0 then
          begin
            for j := 0 to grdScan.SelectedRows.Count - 1 do
            begin
              qrySage.GotoBookmark(TBookMark(grdScan.SelectedRows.Items[J]));
              tblSagePost.Insert;

              For i := 0 to qrySage.FieldCount - 1 do
                tblSagePost.Fields[i] := qrySage.Fields[I];
                tblSagePost.Post;
            end;
          end;
        end;

Can anyone tell me how to use an Update command in the same code.
I have tried it but I get an error saying the dataset isnt open, yet I have called the ExecSQL command.
Otherwise can anyone suggest how to update a record and only that record that has been selected in a Grid using the Multi-select and Row-select properties.
I think it is something to do with 'SelectedRows'??

Thanks
0
 
kretzschmarCommented:
you just have the record,
depending on that, if you have an updateable query (cachedUpdates and UpdateSQL-opject as updateobject linked to the query) you could do something like this

       begin
         tblSagePost.Open;
         if grdScan.SelectedRows.Count > 0 then
         begin
           for j := 0 to grdScan.SelectedRows.Count - 1 do
           begin
             qrySage.GotoBookmark(TBookMark(grdScan.SelectedRows.Items[J]));
             //change here
             qrySage.edit;
             qrySage.fieldByName('TheFieldName').AsString := 'WhatEver';
             qrySage.Post;
             //end change here, could alse be moved down the forloop
             tblSagePost.Insert;

             For i := 0 to qrySage.FieldCount - 1 do
               tblSagePost.Fields[i] := qrySage.Fields[I];
               tblSagePost.Post;
           end;
         end;
       
if you do not have an updateable query, then you could do this like

.

       begin
         tblSagePost.Open;
         if grdScan.SelectedRows.Count > 0 then
         begin
           for j := 0 to grdScan.SelectedRows.Count - 1 do
           begin
             qrySage.GotoBookmark(TBookMark(grdScan.SelectedRows.Items[J]));
             //change here, qryUpdateSage is a second query-object
             //and holds as SQL: Update TableName set FieldName = :Value
             //tablename and fieldname must be replaced by
             //your realnames
             qryUpdateSage.ParamByName('Value').AsString := 'WhatEver';
             qryUpdateSage.ExecSQL;    
             //end change here, could alse be moved down the forloop

             tblSagePost.Insert;

             For i := 0 to qrySage.FieldCount - 1 do
               tblSagePost.Fields[i] := qrySage.Fields[I];
               tblSagePost.Post;
           end;
         end;
       end;


by the second method the update is not reflected in the grid

hope this helps

btw. reject trex_fire proposed answer

meikl ;-)
0
 
kretzschmarCommented:
oops just forgotten something in the second method,
as we want not update all records ;-)
should be

]));
            //change here, qryUpdateSage is a second query-object
            //and holds as SQL: Update TableName set FieldName = :Value where IDFieldName = :ID
            //tablename, fieldname and IDfieldname must be replaced by
            //your realnames
            qryUpdateSage.ParamByName('Value').AsString := 'WhatEver';
            qryUpdateSage.ParamByName('Id').AsInteger := qrySage.FieldByName('IDFieldName).AsInteger;;
            qryUpdateSage.ExecSQL;    
            //end change here, could alse be moved down the forloop

           
meikl ;-)
0
 
JodalvyCommented:
meikl,

You're a genius!!!
0
 
JodalvyCommented:
SCOTT78,

Can I have your phone number for intimate conversation.
0
 
kretzschmarCommented:
? thanks, Jodalvy
0
 
JodalvyCommented:
Meikl,

SCOTT78 is a personal friend and I am grateful that you have provided him with so much information.
0
 
kretzschmarCommented:
;-) no problem, i'm happy about such words
0
 
SCOTT78Technical AdvisorAuthor Commented:
Meikl,

Can you explain one thing to me.
I can select multiple records in my Grid OK.
The records that have the dot(.) next to them are copied to my table OK but the record that has the arrow and dot (.>) next to it isnt copied. Such as, if I only choose one record it isnt copied, yet my system knows I have selected more than 0 rows in the Grid.
A bit weird???

Thanks for the other help youve given me! ! !
0
 
kretzschmarCommented:
? all with a dot should be copied,
where is there a dot. if there is only a >-sign,
then the record is not currently selected/bookmarked
(just press the space key to select/bookmark the record)

btw. which delphi version?
if needed, i can send you a working sample

meikl ;-)
0
 
SCOTT78Technical AdvisorAuthor Commented:
All records with a dot are copied.

The record that has the dot and is shared by the arrow isnt copied.
I have pressed space and if nothing is selected I flag a messagebox, so the record is selected but nothing is copied.
Its strange!
0
 
kretzschmarCommented:
i will do a test, never had this problem
0
 
SCOTT78Technical AdvisorAuthor Commented:
Thanks very much. You've been very helpful.
Thanks again!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now