Solved

SQL

Posted on 2002-06-29
19
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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