Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error when Appending SQL to ADOQuery

Posted on 2004-03-24
4
Medium Priority
?
653 Views
Last Modified: 2010-04-06
I am Using Delphi 6 Enterprise under Windows 2000, and am using ADO to connect to an ACCESS 2000 Database.

I basically use ADOQueries to connect to the Access Database and generally have NO problems, except, when I use code similar to the following I get the following error: Project XXXXX.exe has raised exception class EAccessViolation with message 'Access violation at address 1B041851 in module 'msjet40.dll'........ etc...

-----------------------------------------------------------------------
if qwkTable2.Active then dmMain.qwkTable2.Close;

  qwkTable2.SQL.Clear;
  qwkTable2.SQL.Append('SELECT Sum(mQuoteDetails.TotalCost) AS SumOfTotalCost, Sum(mQuoteDetails.TaxAmount) AS SumOfTaxAmount');
  qwkTable2.SQL.Append('FROM mQuoteDetails');
  qwkTable2.SQL.Append('GROUP BY mQuoteDetails.JobID, mQuoteDetails.QuoteID');
  qwkTable2.SQL.Append('HAVING ((mQuoteDetails.JobID='+IntToStr(iJobID)+') AND (mQuoteDetails.QuoteID='+IntToStr(iQuoteID)+'))');
  qwkTable2.Open;
-----------------------------------------------------------------------

Basically the qwkTable2 is a ADOQuery with NO SQL code, which I use as a WORK TABLE, that is SQL can change depending on the function. The error occurs after the first APPEND statement (I have tried OTHER statement types, even simple SELECT statements). I thought that the ADOQuery may be in use, so I added the first line, but still no luck.

If I hard code the above Query into the ADOQuery component it works OK, but when I use the above method I get errors.

Note: I have done this on many occasions with SQL Server, with no problems.


Any help would be greatly appreciated

Thanks
PE
0
Comment
Question by:pjelias
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 10666553
Use SQL.Add instead of Append
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 500 total points
ID: 10666733
try this:

  with dmMain do
  begin
    qwkTable2.Active := False;
    qwkTable2.SQL.Clear;
    T := ''
       + ' SELECT Sum(mQuoteDetails.TotalCost) AS SumOfTotalCost, Sum(mQuoteDetails.TaxAmount) AS SumOfTaxAmount'
       + ' FROM mQuoteDetails'
       + ' GROUP BY mQuoteDetails.JobID, mQuoteDetails.QuoteID'
       + ' HAVING ((mQuoteDetails.JobID='
       + QuotedStr(IntToStr(iJobID))
       + ') AND (mQuoteDetails.QuoteID='
       + QuotedStr(IntToStr(iQuoteID))
       +'))'
       + '';
    qwkTable2.SQL.Text := T;
    qwkTable2.Open;
  end;
0
 

Author Comment

by:pjelias
ID: 10666830
esoftbg,

thanks for the quick response and solution

Question though, why does your code work and not mine ?, yet my code works when using SQL SERVER (I am fairly new to using ACCESS)


Regards
PE
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 10666911
I think there is 3 reasons:
1. it must to be a space before: FROM, 'GROUP BY and HAVING
2. it must to be quoted the values of IntToStr(iJobID) and IntToStr(iQuoteID)
3. when I place a breakpoint on the line:     qwkTable2.SQL.Text := T;
applikation stops there an I see the content of T

emil
0

Featured Post

Industry Leaders: 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

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

715 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