Solved

SQL

Posted on 2002-06-29
19
154 Views
Last Modified: 2010-04-04
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
Comment
Question by:SCOTT78
  • 9
  • 5
  • 3
  • +2
19 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7118810
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
 

Author Comment

by:SCOTT78
ID: 7119156
Thanks Meikl but should I create any fields in the table or will the query copy set the fields in the table???
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7119251
the table and the targetfields must be exist
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Expert Comment

by:VENKAT
ID: 7121180
kretzschmar is right.
0
 
LVL 1

Expert Comment

by:trex_fire
ID: 7123491
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7123647
ts ts ts ...
0
 

Author Comment

by:SCOTT78
ID: 7124093
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7124129
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
 
LVL 27

Accepted Solution

by:
kretzschmar earned 100 total points
ID: 7124140
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
 

Expert Comment

by:Jodalvy
ID: 7124345
meikl,

You're a genius!!!
0
 

Expert Comment

by:Jodalvy
ID: 7124353
SCOTT78,

Can I have your phone number for intimate conversation.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7124355
? thanks, Jodalvy
0
 

Expert Comment

by:Jodalvy
ID: 7124361
Meikl,

SCOTT78 is a personal friend and I am grateful that you have provided him with so much information.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7124435
;-) no problem, i'm happy about such words
0
 

Author Comment

by:SCOTT78
ID: 7124697
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7124723
? 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
 

Author Comment

by:SCOTT78
ID: 7124746
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7124761
i will do a test, never had this problem
0
 

Author Comment

by:SCOTT78
ID: 7126450
Thanks very much. You've been very helpful.
Thanks again!!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
delphi prevent click fast 2 242
Simple Delphi Question 9 96
Twebbrowser in Fire monkey android scrolling to bottom 1 45
Delphi: sending SMS on android platform 1 38
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question