Some SImple SQL/Delphi Problem

[delphinewbie]

CUSTOMER // TABLE NAME
CustID CustName

A001 Andy
A002 John
A003 Smith
A004 Lee


The data list above is store in the MSSQL database.. In my delphi program, i want to
extract/get the data for CustName where the custID is equal to A003.. So what SQL statement
should i write ?

I know it's something like the following:


function TdmSVSSession.IsIngotNo2Exists(IngotNo:String; LotNo: String): Boolean;
begin
With qry do
begin
Close;
SQL.Clear;
SQL.Add('SELECT CustName FROM CUSTOMER');
SQL.Add('WHERE CustID= A003');
Open;
result := not IsEmpty;
end;
end;


But my problem is that SQL statment is only return the value either TRUE or FALSE.. All i want
is the data from the database .. Like in this case, the return value that i want is "Smith"
instead of "TRUE" ... can any one there help me with that problem ..

I think it's easy for ya guys.. but for me it's hard .. cos i'm just the newbie for programming.
All the help is appreciated in advance .. thanks..

 

[Svanhooft]

hi,

The result is in :

Name := qry.fieldbyname('Custname').value;

After executing an query the result is like a table with the name of the TQuery object as the table name.


Steph [Bigglasses]

 

[petertickler]

I would suggest you pass the CustID in a parameter. For example,

function TdmSVSSession.IsIngotNo2Exists(IngotNo:String; LotNo: String): Boolean;
begin
With qry do
begin
Close;
SQL.Clear;
SQL.Add('SELECT CustName FROM CUSTOMER');
SQL.Add('WHERE CustID = :CustID');
Parameters.parambyname('CustID').value := 'A003';
Open;
result := not IsEmpty;
end;
end;

It seems to me that your SQL will always return False currently.

To do it the way you are trying, I think you need something like:

SQL.Add('WHERE CustID = ''A003'');

That way, you are passing the string.

Peter Tickler

 

[delphinewbie]

OK thanks a lot ..
But what if i want to get the total value for certain field ?? what should i do then ..

Ok let's take an example here:

Product //Table name

ProductID Price
P001 $5.00
P002 $7.00
P003 $9.00

Let's say in my program i want to get the Total of the price for all 3 product .. what should i do then ? In this case i can't use the following method i guess :

Name := qry.fieldbyname('TotalPrice').value;

Cos i don't have Total Price field name in my table ... what to do then ? Again as i mentioned above, all i want is to get the TotalPrice value, but not the return value such as TRUE or False.. pls.. i hope u guys can help.. i'm in crying need now .. thx ..

 

[Svanhooft]

hi

query is:
select sum(Price) Total from Product

result is
Totalvalue := qry.FieldByname('Total').value;

Steph [Bigglasses]