| View previous topic :: View next topic |
| Author |
Message |
Hal Davison
Guest
|
 Posted: Sat Aug 16, 2003 9:19 am Post subject: Do it exist? |
 |
|
When my customer creates an delivery order they supply the order number.
How does one check with SQL if the entered order already exists?
In other languages we would:
find order where order.order_num = "1234".
if available Order then order_exists = true.
....
How we do this in SQL?
--
Hal Davison
Davison Consulting
|
|
| Back to top |
|
 |
Hal Davison
Guest
|
| Posted: Sat Aug 16, 2003 1:05 pm Post subject: Re: Do it exist? |
|
|
"Viatcheslav V. Vassiliev" <support (AT) oledbdirect (DOT) com> wrote
| Quote: | Select Count(order_id) as OrderCount from orders where order_id = 1234
Open this query - it will return one row with name "OrderCount". Look what
is in this field. If OrderCount > 0 - order exists.
----------------------------------------------------------------
Regards,
Viatcheslav V. Vassiliev
http://www.oledbdirect.com
The fastest way to access MS SQL Server, MS Jet (MS Access)
and Interbase (through OLEDB) databases.
|
Thank You Viatcheslav !!
--
Hal Davison
Davison Consulting
|
|
| Back to top |
|
|
Hal Davison
Guest
|
| Posted: Sat Aug 16, 2003 2:44 pm Post subject: Re: Do it exist? ... ODD.. |
|
|
"Viatcheslav V. Vassiliev" <support (AT) oledbdirect (DOT) com> wrote
| Quote: | Select Count(order_id) as OrderCount from orders where order_id = 1234
Open this query - it will return one row with name "OrderCount". Look what
is in this field. If OrderCount > 0 - order exists.
|
When I do the Query the resultant record count is ALWAYS 4643916 regardless
of the order_id number used.
What don't I understand?
--
Hal Davison
Davison Consulting
|
|
| Back to top |
|
|
Aage Johansen
Guest
|
| Posted: Sat Aug 16, 2003 2:48 pm Post subject: Re: Do it exist? ... ODD.. |
|
|
On Sat, 16 Aug 2003 10:44:43 -0400, Hal Davison <hal (AT) faams (DOT) net> wrote:
| Quote: |
"Viatcheslav V. Vassiliev" <support (AT) oledbdirect (DOT) com> wrote in message
news:3f3e020b$1 (AT) newsgroups (DOT) borland.com...
Select Count(order_id) as OrderCount from orders where order_id = 1234
Open this query - it will return one row with name "OrderCount". Look
what
is in this field. If OrderCount > 0 - order exists.
When I do the Query the resultant record count is ALWAYS 4643916
regardless
of the order_id number used.
What don't I understand?
|
Show us your code.
--
Aage J.
|
|
| Back to top |
|
|
Aage Johansen
Guest
|
| Posted: Sat Aug 16, 2003 8:03 pm Post subject: Re: Do it exist? ... ODD.. |
|
|
On Sat, 16 Aug 2003 15:53:17 -0400, Hal Davison <hal (AT) faams (DOT) net> wrote:
| Quote: | ...
Based on a suggestion from V.V. Vassilliev we built the following:
======================================================
procedure TfrmOrd_oe01.DBEdit1Exit(Sender: TObject);
var OrderCount:integer;
begin
DUHQuery.SQL.Clear;
DUHQuery.SQL.Add('Select Count(Order_num) as OrderCount from
Public.Order_header where Order_num = DBEdit1.Text');
end;
DUHQuery.Prepared := true;
DUHQuery.Open;
DUHQuery.Close;
showmessage('DUH Count: ' + IntToStr(OrderCount));
end;
=====================================================
The field Order_num in the DB is char(11), FYI.
Regardless of Order Number entered, the result of the query is ALWAYS:
4643916
|
Try:
DUHQuery.Open;
showmessage('DUH Count: ' + IntToStr(DUHQuery.Fields[0].AsInteger));
DUHQuery.Close;
[I don't use IBX so this may not be entirely correct]
--
Aage J.
|
|
| Back to top |
|
|
Bob Dawson
Guest
|
| Posted: Sat Aug 16, 2003 8:05 pm Post subject: Re: Do it exist? ... ODD.. |
|
|
You're not actually assigning the query result to your variable. Add a line
as follows:
"Hal Davison" wrote
| Quote: |
procedure TfrmOrd_oe01.DBEdit1Exit(Sender: TObject);
var OrderCount:integer;
begin
DUHQuery.SQL.Clear;
DUHQuery.SQL.Add('Select Count(Order_num) as OrderCount from
Public.Order_header where Order_num = DBEdit1.Text');
end;
DUHQuery.Prepared := true;
DUHQuery.Open;
|
OrderCount := DUHQuery.FieldByName('OrderCount').AsInteger;
| Quote: | DUHQuery.Close;
showmessage('DUH Count: ' + IntToStr(OrderCount));
end;
|
bobD
|
|
| Back to top |
|
|
Kevin Frevert
Guest
|
| Posted: Mon Aug 18, 2003 2:22 pm Post subject: Re: Do it exist? ... ODD.. |
|
|
Hal,
Personally, I would..
<query component at design time>
Select TOP 1 Order_Num
From Public.Order_Header
Where Order_Num = :Order_Num
Configure the Order_Num parameter accordingly.
In Delphi code..
{some datamodule}
public
function OrderNumExists(const Value :String):Boolean;
end;
function TdmSomeDatamodule.OrderNumExists(const Value :String):Boolean;
begin
with qOrderNumExists do
begin {Start of with query do}
try {Start of try...except block}
Close; {just in case}
ParamByName('Order_Num').Value := Value;
Open;
Result := NOT(IsEmpty);
Close;
except
on e: EDatabaseError do
begin
Close;
Result := False;
end;
end; {End of try...except block}
end; {End of with query do}
end;
Now in your main form's code (or child form, another datamodule,
wherever)...
{not sure I would the onexit event but...}
procedure TfrmOrder.DBEdit1Exit(Sender: TObject);
begin
if dmSomeDataModule.OrderNumExists(DBEdit1.Text) then
ShowMessage('Warning, ' + DBEdit1.Text + ' already exists.');
end;
Good luck,
krf
"Hal Davison" <hal (AT) faams (DOT) net> wrote
| Quote: |
procedure TfrmOrd_oe01.DBEdit1Exit(Sender: TObject);
var OrderCount:integer;
begin
DUHQuery.SQL.Clear;
DUHQuery.SQL.Add('Select Count(Order_num) as OrderCount from
Public.Order_header where Order_num = DBEdit1.Text');
end;
DUHQuery.Prepared := true;
DUHQuery.Open;
DUHQuery.Close;
showmessage('DUH Count: ' + IntToStr(OrderCount));
end;
|
|
|
| Back to top |
|
|
Viatcheslav V. Vassiliev
Guest
|
| Posted: Tue Aug 19, 2003 5:31 am Post subject: Re: Do it exist? |
|
|
| Quote: | Did you read my entire post? I guess not.
Sorry, I skipped end of your answer. |
----------------------------------------------------------------
Regards,
Viatcheslav V. Vassiliev
http://www.oledbdirect.com
The fastest way to access MS SQL Server, MS Jet (MS Access)
and Interbase (through OLEDB) databases.
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
