Solved

Error when Appending SQL to ADOQuery

Posted on 2004-03-24
4
633 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
  • 2
4 Comments
 
LVL 22

Expert Comment

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

Accepted Solution

by:
esoftbg earned 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now