?
Solved

ado SQL load info from 2 differnt tables into same memo

Posted on 2009-04-26
31
Medium Priority
?
257 Views
Last Modified: 2012-05-06
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
Comment
Question by:Grant Fullen
  • 15
  • 11
  • 5
31 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237068
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
 
LVL 6

Expert Comment

by:bokist
ID: 24237098
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237109
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Grant Fullen
ID: 24237113
bokist that compiles but Unspecified error.
0
 

Author Comment

by:Grant Fullen
ID: 24237123
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
 

Author Comment

by:Grant Fullen
ID: 24237129
rfwolf same error.......
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237136
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237143
"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
 

Author Comment

by:Grant Fullen
ID: 24237154
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
 

Author Comment

by:Grant Fullen
ID: 24237155
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
 

Author Comment

by:Grant Fullen
ID: 24237166
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237168
NO... A will be the table "Customers"
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237170
Did you try my SQL?
SELECT customerinfo."Full Name", Estimates."Description" FROM customerinfo JOIN Estimates ON customerinfo.EstimateID = customerinfo.EstimateID;
0
 

Author Comment

by:Grant Fullen
ID: 24237182
wolf i put that sql in the object inspector spl text and
Syntax error in from clause........
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237245
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237247
and indeed it does seem possible to do a join using the WHERE clause and not using the word 'join'
0
 
LVL 6

Expert Comment

by:bokist
ID: 24237296
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237303
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
 

Author Comment

by:Grant Fullen
ID: 24237314
crap it still gives syntax error in from clause.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237316
What database are you using?
0
 

Author Comment

by:Grant Fullen
ID: 24237330
bokist i like your example code to. but still an error.
Unspecified error. ANy ideals for me to fix this.
Thanks

0
 
LVL 6

Expert Comment

by:bokist
ID: 24237357
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
 
LVL 6

Expert Comment

by:bokist
ID: 24237455
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
 

Author Comment

by:Grant Fullen
ID: 24237532
see attached.
error.jpg
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24237546
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
 

Author Comment

by:Grant Fullen
ID: 24238132
it is an access 2003 database.
0
 

Author Comment

by:Grant Fullen
ID: 24238287
does any expers know how to join the 2 tables so i can querry this ?
0
 

Author Comment

by:Grant Fullen
ID: 24238686
Can anyone help to solve this problem . I think i need to join the tables .
0
 

Author Comment

by:Grant Fullen
ID: 24238697
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
 
LVL 6

Accepted Solution

by:
bokist earned 1000 total points
ID: 24238859
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
 

Author Closing Comment

by:Grant Fullen
ID: 31574719
Thanks for working with me.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

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…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

850 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