• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

ado SQL load info from 2 differnt tables into same memo

table1:= customerinfo   field=EstimateID

table2:= Estimates field=EstimateID

I need to load info from both tables above into the same memo, But only if the EstimateID from both tables are the same.
Now i am getting a sql syntax error but I do not think I am going about it write.
procedure TForm1.ToolButton6Click(Sender: TObject);
begin
  datamodule1.AQEstimates.SQL.Clear;
  datamodule1.AQEstimates.SQL.Text:='Select EstimateID From Estimates where EstimateID= 1 and Select EstimateID From customerinfo where EstimateID= 1';
  datamodule1.AQEstimates.Open;
 
    while not datamodule1.AQEstimates.Eof do
     begin
     memo1.Text:=(datamodule1.AQEstimates.FieldByName('EstimateID').AsVariant);
     memo1.Text:=(datamodule1.AQEstimates.FieldByName('Description').AsString);
     memo1.Text:=(datamodule1.AQcustomerinfo.fieldbyname('EstimateID').AsString);
     memo1.Text:=(datamodule1.AQcustomerinfo.fieldbyname('Full Name').AsString);
     datamodule1.AQEstimates.Next;
     datamodule1.AQcustomerinfo.Next;
     end;
 
end;

Open in new window

0
Grant Fullen
Asked:
Grant Fullen
  • 15
  • 11
  • 5
1 Solution
 
rfwoolfCommented:
1) You should also post this question in the SQL Zone
2) You are probably looking for an SQL JOIN statement., I haven't tested this but I'll give it a try

SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = customerinfo.EstimateID;
0
 
bokistCommented:
Try this way:

with datamodule1.AQEstimates do
   begin
   Sql.Clear;
   Sql.Add('select e.estimateId, e.description, c.full name');
   Sql.Add('  from estimates e, customerinfo c');
   Sql.Add(' where e.estimateId = c.edtimateId')';
   Sql.Add('     and e.estimateId = 1');
   Open;
   while not EOF do
       begin
       memo1.Text:= FieldByName('EstimateID').AsVariant;
       memo1.Text:= FieldByName('Description').AsString;
       memo1.Text:= fieldbyname('EstimateID').AsString;
       memo1.Text:= fieldbyname('Full Name').AsString;
       Next;
   end;
end;

0
 
rfwoolfCommented:
bokist> some problems with your solution...
namely calling first on the dataset,
and appending lines to the memo instead of replacing all the memo text each time...

Here are the changes
with datamodule1.AQEstimates do
   begin
   Sql.Clear;
   Sql.Add('select e.estimateId, e.description, c.full name');
   Sql.Add('  from estimates e, customerinfo c');
   Sql.Add(' where e.estimateId = c.edtimateId')';
   Sql.Add('     and e.estimateId = 1');
   Open;
   First;
   while not EOF do
       begin
       memo1.Lines.Append(FieldByName('EstimateID').AsVariant);
       memo1.Lines.Append(FieldByName('Description').AsString);
       memo1.Lines.Append(fieldbyname('EstimateID').AsString);
       memo1.Lines.Append(fieldbyname('Full Name').AsString);
       Next;
   end;
end
0
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.

 
Grant FullenAuthor Commented:
bokist that compiles but Unspecified error.
0
 
Grant FullenAuthor Commented:
can you experts help me understand the sql
select e.estimateId, e.description, c.full name');
   Sql.Add('  from estimates e, customerinfo c');
   Sql.Add(' where e.estimateId = c.edtimateId')';
   Sql.Add('     and e.estimateId = 1');
I never seen e.description or c.fullname how can you use e. or c. in a sql statment ?
0
 
Grant FullenAuthor Commented:
rfwolf same error.......
0
 
rfwoolfCommented:
I'm not sure about bokist's SQL statement. Test it in design time in the SQL property of your query and open up the query from the object inspector. Also then check if the result set you are getting is the one you expect.
0
 
rfwoolfCommented:
"can you experts help me understand the sql"
Well like I say, I'm not positive about bokist's SQL - he never uses the word join and yet it's a join statement using the WHERE clause...
But I can explain the use of e and c. These are your tables. When you mention your tables in the FROM clause in the SQL, you can proceed the table name with an abbreviation, here's an example:

SELECT A.Surname FROM Customers A;
0
 
Grant FullenAuthor Commented:
I put the sql text in the query of the object and set to active to true and same error unspecified error . It must be in the sql text.
0
 
Grant FullenAuthor Commented:
this is what i have in there.
select
 e.estimateId, e.description, c.full name
 from estimates e, customerinfo c
where e.estimateId = c.edtimateId and e.estimateId = 1
0
 
Grant FullenAuthor Commented:
SELECT A.Surname FROM Customers A;
so surname is a field ?
so after that I can now use A in any of the sql and it will equal=Surname.
0
 
rfwoolfCommented:
NO... A will be the table "Customers"
0
 
rfwoolfCommented:
Did you try my SQL?
SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = customerinfo.EstimateID;
0
 
Grant FullenAuthor Commented:
wolf i put that sql in the object inspector spl text and
Syntax error in from clause........
0
 
rfwoolfCommented:
Whoops I see one error in my code in the ON clause.. here is the correction
SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = Estimates.EstimateID;
0
 
rfwoolfCommented:
and indeed it does seem possible to do a join using the WHERE clause and not using the word 'join'
0
 
bokistCommented:
rwoolf !

I'm not beginer in SQL, and indeed I use INNER and OUTER JOIN depend on task.
Thank you for suggestion, perhaps better think about field name "full name" and not about my sql statement.  
0
 
rfwoolfCommented:
Go ahead an take offense bokist, I think I was very diplomatic in expressing that I thought there was a possible error. But I see I've hurt your delicate ego. And there's a reason why I put "full name" in quotes.
0
 
Grant FullenAuthor Commented:
crap it still gives syntax error in from clause.
0
 
rfwoolfCommented:
What database are you using?
0
 
Grant FullenAuthor Commented:
bokist i like your example code to. but still an error.
Unspecified error. ANy ideals for me to fix this.
Thanks

0
 
bokistCommented:
rfwoolf!

when you said :
  I'm not positive about bokist's SQL - he never uses the word join...
then you mean
  I think I was very diplomatic in expressing...
Perhaps I misunderstand your words.
0
 
bokistCommented:
with datamodule1.AQEstimates do
   begin
   Sql.Clear;
   Sql.Add('select e.estimateId, e.description, c."full name"');
   Sql.Add('  from estimates e, customerinfo c');
   Sql.Add(' where e.estimateId = c.edtimateId')';
   Sql.Add('     and e.estimateId = 1');
   Open;
// you can ignore First
    while not EOF do
       begin
       memo1.lines.add(fieldByName('EstimateID').asstring); // or asinteger
       memo1.lines.add(fieldByName('Description').asstring);
       memo1.lines.add(fieldbyname('EstimateID').asstring);
       memo1.lines.add(fieldbyname('Full Name').asstring);
       Next;
   end;
end;

0
 
Grant FullenAuthor Commented:
see attached.
error.jpg
0
 
rfwoolfCommented:
What. database. are. you. using?
and this is a good reason why you shouldn't have spaces in your field names. Some databases will accept quotes around field names with spaces (e.g. Firebird), but yours may not. You may have to try using square brackets [full name] or single-quotes 'full name' or if your database is very stupid you may be forced to change your field name.
0
 
Grant FullenAuthor Commented:
it is an access 2003 database.
0
 
Grant FullenAuthor Commented:
does any expers know how to join the 2 tables so i can querry this ?
0
 
Grant FullenAuthor Commented:
Can anyone help to solve this problem . I think i need to join the tables .
0
 
Grant FullenAuthor Commented:
begin
 with datamodule1.AQEstimates do
   begin
   Sql.Clear;
   Sql.Add('select e.estimateId, e.description, c.[fullname]');
   Sql.Add('  from estimates e, customerinfo c');
   Sql.Add(' where e.estimateId = c.edtimateId');
   Sql.Add('     and e.estimateId = 1');
   Open;
// you can ignore First
    while not EOF do
       begin
       memo1.lines.add(fieldByName('EstimateID').asstring); // or asinteger
       memo1.lines.add(fieldByName('Description').asstring);
       memo1.lines.add(fieldbyname('EstimateID').asstring);
       memo1.lines.add(fieldbyname('Full Name').asstring);
       Next;
   end;
end;
This throughs error Parameter c.estimateID has no defalt value........
0
 
bokistCommented:
Unfortunatelly I haven't experience with Access.
As far as I know, Access is using LocalSQL-Syntax (flat file).
Perhaps this way will work ?

begin
 with datamodule1.AQEstimates do
   begin
   Sql.Clear;
   Sql.Add('select estimates.estimateId as eID, estimates.description as eDESC,  customerinfo.[full name] as cNAME');
   Sql.Add('  from estimates, customerinfo');
   Sql.Add(' where estimates.estimateId = customerinfo.edtimateId');
   Sql.Add('     and estimates.estimateId = 1');
   Open;
    while not EOF do
       begin
       memo1.lines.add(fieldByName('eID').asstring); // or asinteger
       memo1.lines.add(fieldByName('eDESC').asstring);
       memo1.lines.add(fieldbyname('eID').asstring);  // or asinteger
       memo1.lines.add(fieldbyname('cNAME').asstring);
       Next;
   end;
end;
I cant give you more detailed explanation, because of my weak english(my disadvantage).
Sorry, and good luck.

0
 
Grant FullenAuthor Commented:
Thanks for working with me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 15
  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now