Board index » delphi » Agregate function/MSJET40.DLL/Access Violation (Borland please)

Agregate function/MSJET40.DLL/Access Violation (Borland please)

Hi,

  When I run my application from within the Delphi IDE calling any SQL
agregate functions result in an AccessViolation when the line containing

the function  is read.

The error Message is "Project p.exe raised exception Class
EAccessViolation at address..... in module MSJET40.DLL ....Process
stopped....."

The error occurs in the ADODB.PAS in the  procedure RefreshFromOLEDB
embedded in the function InternalRefdresh. (ADODB.PAS  Line 2351 apprx.)

When running the application from outside the IDE no AV occurs. After
the AV occurs the rest of the function execute correctly and the result
is good. When the Open method is called, no error occurs, the error
occurs only when reading the line containing the agregate function.
Is it a known problem? How to avoid this AV?

function TFrmMain.InStock(PartNo, RevNo: String): Double;
var
  InvQuery: TADOQuery;
begin
  try
    InvQuery := TADOQuery.Create(Application);
    InvQuery.Connection := DMod.ADOConnection;

    with InvQuery do
    begin
      Close;
      with SQL do
      begin
        Clear;
        Add('SELECT SUM(QTY)');    //******* the AV occurs here****
        Add('FROM INVENTORY');
        Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');
        Parameters[0].Value := PartNo;
        Parameters[1].Value := RevNo;
      end;
      Open;

      if RecordSet.RecordCount > 0 then
      begin
        if Fields[0].Value = Null then
          Result := 0
        else
          Result := Fields[0].Value;

      end;
    end;
  finally
    InvQuery.Close;
    InvQuery.Free;

  end;

end;

Thank you very much
Serge Myrand

 

Re:Agregate function/MSJET40.DLL/Access Violation (Borland please)


Quote
Serge Myrand wrote:
> Hi,

>   When I run my application from within the Delphi IDE calling any SQL
> agregate functions result in an AccessViolation when the line containing

> the function  is read.

> The error Message is "Project p.exe raised exception Class
> EAccessViolation at address..... in module MSJET40.DLL ....Process
> stopped....."

> The error occurs in the ADODB.PAS in the  procedure RefreshFromOLEDB
> embedded in the function InternalRefdresh. (ADODB.PAS  Line 2351 apprx.)

> When running the application from outside the IDE no AV occurs. After
> the AV occurs the rest of the function execute correctly and the result
> is good. When the Open method is called, no error occurs, the error
> occurs only when reading the line containing the agregate function.
> Is it a known problem? How to avoid this AV?

> function TFrmMain.InStock(PartNo, RevNo: String): Double;
> var
>   InvQuery: TADOQuery;
> begin
>   try
>     InvQuery := TADOQuery.Create(Application);
>     InvQuery.Connection := DMod.ADOConnection;

>     with InvQuery do
>     begin
>       Close;
>       with SQL do
>       begin
>         Clear;
>         Add('SELECT SUM(QTY)');    //******* the AV occurs here****
>         Add('FROM INVENTORY');
>         Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');
>         Parameters[0].Value := PartNo;
>         Parameters[1].Value := RevNo;
>       end;
>       Open;

Change the query to
Add('SELECT SUM(QTY) FROM INVENTORY');
Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');

this will work.

Re:Agregate function/MSJET40.DLL/Access Violation (Borland please)


make sure that there are spaces (blanks) at the end of every string in
the Stringlist of the SQL
e.g.
        sql.Add('SELECT SUM(QTY) ');    //******* the AV occurs here****
        sql.Add('FROM INVENTORY ' );
        sql.Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO ');

otherwise your version when presented as a string

Quote
>>         Add('SELECT SUM(QTY)');    //******* the AV occurs here****
>>         Add('FROM INVENTORY');
>>         Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');

would read
'select sum(qty)from Inventorywhere part_no = :part_no and Rev_no =
:rev_no';
and you really want
'select sum(qty) from Inventory where part_no - :part_no and Rev_no =
:rev_no ';

note the diffrence
e.g. 'select sum(qty)from ' becomes 'select sum(qty) from'
             ^^^^^^^^^^^^                   ^^^^^^^^ ^^^^
also
'Inventorywhere part_no ' becomes 'Inventory where part_no '
       ^^^^^^^                           ^^^^^^^^

HTH

In article <39A56236.9374A...@solucionar.com.br>, Eduardo Soares
Ogasawara <se...@solucionar.com.br> writes

Quote
>Serge Myrand wrote:

>> Hi,

>>   When I run my application from within the Delphi IDE calling any SQL
>> agregate functions result in an AccessViolation when the line containing

>> the function  is read.

>> The error Message is "Project p.exe raised exception Class
>> EAccessViolation at address..... in module MSJET40.DLL ....Process
>> stopped....."

>> The error occurs in the ADODB.PAS in the  procedure RefreshFromOLEDB
>> embedded in the function InternalRefdresh. (ADODB.PAS  Line 2351 apprx.)

>> When running the application from outside the IDE no AV occurs. After
>> the AV occurs the rest of the function execute correctly and the result
>> is good. When the Open method is called, no error occurs, the error
>> occurs only when reading the line containing the agregate function.
>> Is it a known problem? How to avoid this AV?

>> function TFrmMain.InStock(PartNo, RevNo: String): Double;
>> var
>>   InvQuery: TADOQuery;
>> begin
>>   try
>>     InvQuery := TADOQuery.Create(Application);
>>     InvQuery.Connection := DMod.ADOConnection;

>>     with InvQuery do
>>     begin
>>       Close;
>>       with SQL do
>>       begin
>>         Clear;
>>         Add('SELECT SUM(QTY)');    //******* the AV occurs here****
>>         Add('FROM INVENTORY');
>>         Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');
>>         Parameters[0].Value := PartNo;
>>         Parameters[1].Value := RevNo;
>>       end;
>>       Open;

>Change the query to
>Add('SELECT SUM(QTY) FROM INVENTORY');
>Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');

>this will work.

--
Arnold Johnson

Re:Agregate function/MSJET40.DLL/Access Violation (Borland please)


Eduardo,

  Thank you very much, you are right, it works. I would never tought that to
put the FROM clause on the same line to change anything.
  I will make more tests in the future.

Thanks again
Serge Myrand

Quote
Eduardo Soares Ogasawara wrote:
> Serge Myrand wrote:

> > Hi,

> >   When I run my application from within the Delphi IDE calling any SQL
> > agregate functions result in an AccessViolation when the line containing

> > the function  is read.

> > The error Message is "Project p.exe raised exception Class
> > EAccessViolation at address..... in module MSJET40.DLL ....Process
> > stopped....."

> > The error occurs in the ADODB.PAS in the  procedure RefreshFromOLEDB
> > embedded in the function InternalRefdresh. (ADODB.PAS  Line 2351 apprx.)

> > When running the application from outside the IDE no AV occurs. After
> > the AV occurs the rest of the function execute correctly and the result
> > is good. When the Open method is called, no error occurs, the error
> > occurs only when reading the line containing the agregate function.
> > Is it a known problem? How to avoid this AV?

> > function TFrmMain.InStock(PartNo, RevNo: String): Double;
> > var
> >   InvQuery: TADOQuery;
> > begin
> >   try
> >     InvQuery := TADOQuery.Create(Application);
> >     InvQuery.Connection := DMod.ADOConnection;

> >     with InvQuery do
> >     begin
> >       Close;
> >       with SQL do
> >       begin
> >         Clear;
> >         Add('SELECT SUM(QTY)');    //******* the AV occurs here****
> >         Add('FROM INVENTORY');
> >         Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');
> >         Parameters[0].Value := PartNo;
> >         Parameters[1].Value := RevNo;
> >       end;
> >       Open;

> Change the query to
> Add('SELECT SUM(QTY) FROM INVENTORY');
> Add('WHERE PART_NO = :PART_NO AND REV_NO = :REV_NO');

> this will work.

Other Threads