SQL record found

[roo0047]

I'm using LedgerLink (LL) to access QuickBooks (QB) records. Basically I'm using it to add invoices (produced in my application) to QB. The first step is to query QB to see if the record already exists, using the following code:

function TQbInvForm.FindInvoice(RefNumber: string): boolean;
begin
  LLInvData.Close;
  LLInvData.QueryText.Clear;
  LLInvData.QueryText.Add('SELECT * FROM Invoice WHERE RefNumber = '+ #39 + RefNumber + #39);
  LLInvData.Open;
  Result:= LLInvData.RecordCount = 1;
  LLInvData.Close;
end; //FindInvoice

This works just fine, but I've read that RecordCount is not the preferred method of doing something like this. Can someone provide me a more elegant way to do the same thing in SQL queries, or is this it?

Thanks in advance!

Roo
Delphi Rules!

 

[Glenn9999]

in SQL you use COUNT().

SELECT COUNT(*) FROM TABLE1;

 

[roo0047]

Thanks Glenn - It did not work with QuickBooks LedgerLink but I will definately put it to use for MySQL. If anyone else is interested, here's the implementation (un-tested)

procedure TForm1.ShowAllFields;
begin
  Query1.Close;
  Query1.SQL.Clear;
  Query1.SQL.Add('SELECT count(*) as cnt FROM Customer');
  Query1.Open;
  Statusbar1.Panels[0].Text:= '[' + Query1.FindField('cnt').AsString + '] records found.';
end;

Roo
Delphi Rules!

 

[ndaru]

True.

If you use TQuery.RecordCount, it will always return -1. RecordCount method only works with a TTable instance connected with file-based database like Paradox. (That's what I've found in Delphi 3,5,7. I haven't tested it on Delphi 2007).

http://www.freetechbooks.com

 

[roo0047]

It is worth noting that as stated in my original post, RecordCount WORKS with LedgerLink, "... a component and class library for Borland Delphi that provides two way data integration with Intuit\'s QuickBooks via their QBXML interface."

http://sourceforge.net/projects/ledgerlink/

Roo
Delphi Rules!

 

[ThunderForest]

What am I missing here? I've read about this before, that recordCount can be inaccurate, but I always get an accurate recordCount from SQL Server, Paradox, or whatever using TQuery, TwwQuery, TAdoQuery, et al. Do you mean only LedgerLink returns -1, or for once in my life am I just lucky?

Getting answers before I'm asked.
Providing answers if I can.

 

[roo0047]

Thunder - I'm in agreement with you. It works for all I've tried also, including LedgerLink (not except).
Having read the same thing, I was asking for a preferred method. I'm still setting up for MySql, I'll let you know what works with that.

Roo
Delphi Rules!

 

[fatlardo]

The way I see and understand it, a method that works works for a reason!! If a better method is created then the old one may become obsolete in the future (for instance in Delphi 2008) but while you are still using the set up you are now, its fine.

I don't know how right I am. I am not a professional programmer (I'm the kind of guy who just gets a kick out of being able to say 'I can do that!') so I don't know the implications of what I've just said. But as the saying goes, if it's stupid and works, its not stupid!!

Hope it helps and please let me know if I'm just rambling on about something I know nothing about!!!!

Gary

 

[roo0047]

Just that I know of several stupid things that work! )