Solved

SQL

Posted on 2002-06-29
19
151 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
creating manifest for my dll that called from activex 6 113
TVirtualStringTree  search using TEdit 7 121
Working with hours 3 57
Delphi: sending SMS on android platform 1 19
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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

776 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