Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy selected row from one ADOQuery to an other

Posted on 2009-07-11
18
Medium Priority
?
1,809 Views
Last Modified: 2013-11-23
I have 2 adoquery's, (connected to an access database), when I select a record in adoquery1 I want it to be copied to adoquery2 when I click on a button.
I already have this code, but it doesnt work.
procedure TForm1.Button2Click(Sender: TObject);
var teller : Integer;
  Enabled : Boolean;
begin
  ADOQuery2.Append;
  for teller := 1 to ADOQuery1.fieldcount - 1 do
  ADOQuery2.fields[teller].Value := ADOQuery1.fields[teller].Value;
  ADOQuery2.Post;
  Enabled := ADOQuery2.RecordCount > 0;
  RemoveSongButton.Enabled := Enabled;
  RemoveSongfromsonglist.Enabled := Enabled;
  DBGrid2.Enabled := True;
  Button4.Enabled := True;
  Button5.Enabled := True;
  Button6.Enabled := True;
end;

Open in new window

0
Comment
Question by:delphiuser2009
[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
  • 9
18 Comments
 
LVL 6

Expert Comment

by:FactorB
ID: 24829874
procedure TForm1.Button2Click(Sender: TObject);
var
  teller : Integer;
  Enbld : Boolean;
begin
  ADOQuery2.Insert;
  for teller := 1 to ADOQuery1.fieldcount - 1 do begin
  ADOQuery2.Fields[teller].value := ADOQuery1.Fields[teller].value;
  end;
  ADOQuery2.Post;
  if ADOQuery2.RecordCount > 0 then Enbld:=true else Enbld:=false;  
  RemoveSongButton.Enabled := Enbld;
  RemoveSongfromsonglist.Enabled := Enbld;
  DBGrid2.Enabled := True;
  Button4.Enabled := True;
  Button5.Enabled := True;
  Button6.Enabled := True;
end;
0
 

Author Comment

by:delphiuser2009
ID: 24829899
Thanks, I have the first ADOQuery (is linked to a DBGrid with two columns in). But in the second ADOQuery (second DBGrid), it only shows the second column (columns are: Songtitle and Genre). The second ADOQuery should also show the first column, but now it displays only the second column.
Why?
 
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24829927
1. Have you used proper SQL syntax for it. Probably should be:

ADOQuery2.SQL:='SELECT * FROM Table_Name;';

it looks that in your case is

ADOQuery2.SQL:='SELECT Songtitle FROM Table_Name;';

Change Table_Name with the name of your table

2. Also it can be a case if you defined DBGrid to show only specific columns. Double click on DBGrid in design time and clear all the columns defined in Editing dialog.

Regards,
B.
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!

 

Author Comment

by:delphiuser2009
ID: 24829945
Thanks I've changed it to ADOQuery.SQL :='SELECT Songtitle, Genre FROM songs2
Now the Genre column is on its place, but the first column is not visible.
It looks like the code is not taking the column Songtitle for the copy, only the Genre column.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24830020
If is more possible that you have defined only Genre column for DBGrid, try double clicking on DBGrid and deleting all the columns from the list. This way DBGrid will have default settings.
0
 

Author Comment

by:delphiuser2009
ID: 24830092
Nope this doesnt work also.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24830133
Hmm, I don't know what is the problem with DBGrid. I really haven't similar problems. Do you remember changing it somehow in the meantime. If I was you I would delete problematic DBGrid. Put a new on the form, name it like the old one and reconnect it to the same datasource.
0
 
LVL 6

Accepted Solution

by:
FactorB earned 400 total points
ID: 24830249
here is the problem, in my first post change

for teller := 1 to ADOQuery1.fieldcount - 1 do begin

with

for teller := 0 to ADOQuery1.fieldcount - 1 do begin

Everything should be fine now. Sorry I did a mistake, when I used your code as base I haven't noticed that loop starts from 1, Songtitle in this case is column 0.

Regards,
Bojan
0
 

Author Comment

by:delphiuser2009
ID: 24831678
Thanks that did the trick.
I will reward you the points, but I have a small question (not really this topic but still SQL).
I have this code which I use to get all the columns (single row) and put them into 2 richedit fields and a edit field.
1 column is a songtext column and the text inside that field should be put into the richedit field. The other two are the songtitle and id column.
It now shows (when I press a button) not the selected row in the fields I described earlier. But some other row.
This is the code I use for it.
Please can you look at it?
I'll raise the point for you if you can help me with it.
Thanks.

procedure TForm1.Button3Click(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('SELECT * FROM songs');
    Open;
    RichEdit1.Lines.Text := ADOQuery1.FieldByName('SongText').AsString;
    RichEdit2.Lines.Text := ADOQuery1.FieldByName('Songtitle').AsString;
    Edit3.Text := ADOQuery1.FieldByName('Id').AsString;
    RichEdit1.Enabled := True;
    RichEdit2.Enabled := True;
    Button8.Enabled := True;
  end;
end;

Open in new window

0
 
LVL 6

Expert Comment

by:FactorB
ID: 24831708
Off course that you can not get the selected record.
Why: when you close and open ADOQuery1 pointer moves from selected row to first row.
As I can see now original code should be stripped to:

procedure TForm1.Button3Click(Sender: TObject);
begin
    RichEdit1.Lines.Text := ADOQuery1.FieldByName('SongText').AsString;
    RichEdit2.Lines.Text := ADOQuery1.FieldByName('Songtitle').AsString;
    Edit3.Text := ADOQuery1.FieldByName('Id').AsString;
    RichEdit1.Enabled := True;
    RichEdit2.Enabled := True;
    Button8.Enabled := True;
end;
0
 

Author Comment

by:delphiuser2009
ID: 24831767
I get the message: Exception class EDatabaseError with message 'ADOQuery1: Field 'SongText' not found'.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24831789
This is why, when you execute query previously you are not including these two fields (SongText, Songtitle):

ADOQuery1.SQL:='SELECT Songtitle, Genre FROM songs2';

in order to function you should change the query to:

ADOQuery1.SQL:='SELECT Songtitle, Genre, SongText, Songtitle FROM songs2';
0
 

Author Comment

by:delphiuser2009
ID: 24835015
I see in the Query that you use songs table, but like you said in a previous post:
procedure TForm1.Button3Click(Sender: TObject);
begin
    RichEdit1.Lines.Text := ADOQuery1.FieldByName('SongText').AsString;
    RichEdit2.Lines.Text := ADOQuery1.FieldByName('Songtitle').AsString;
    Edit3.Text := ADOQuery1.FieldByName('Id').AsString;
    RichEdit1.Enabled := True;
    RichEdit2.Enabled := True;
    Button8.Enabled := True;
end;
This is a query that is related to table songs, the query for ADOQuery1 looks like this now: ADOQuery1.SQL :='SELECT Id, Songtitle, Genre, Songtext FROM songs';
But I still get the error that field 'Songtext' can't be found.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24835044
Ttry changing column names with numbers

from

 RichEdit1.Lines.Text := ADOQuery1.FieldByName('SongText').AsString;
 RichEdit2.Lines.Text := ADOQuery1.FieldByName('Songtitle').AsString;

to

 RichEdit1.Lines.Text := ADOQuery1.Fields[3].AsString;
 RichEdit2.Lines.Text := ADOQuery1.Fields[1].AsString;

0
 

Author Comment

by:delphiuser2009
ID: 24835220
Now I get the error: 'list index out of bounds (3)'.
0
 

Author Comment

by:delphiuser2009
ID: 24835328
I've got the solution: With button1 I open the database, but forgot to add songtext in the SELECT query.
I will reward you the points and I'll raise it to 100 for you help.
0
 

Author Closing Comment

by:delphiuser2009
ID: 31602386
Thanks you very much for your help. This helped me alot.
0
 
LVL 6

Expert Comment

by:FactorB
ID: 24835594
Glad that it helped, I suspected too on SQL syntax, but since you posted good Query earlier, I got confused.

Have a good day,
B.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

604 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