Solved

SQL

Posted on 2002-06-29
19
145 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now