delphiaccess-violationtadoquery

Delphi - Why am I getting this Access Violation? Is there a limit to ADOQuery parameteres?

I have this code that is returning an Access Violation ('Access violation at address 74417E44 in module 'sqloledb.dll'. Read of address 786E3552') and I can't identify where is the problem. My only guess is that ADOQuery has a limit for the number of parameters we can pass. The code is as follows:

With qryInsert do
  begin
    Active := False;
    Close;
    Sql.Clear;
    Sql.Add('Insert Into MyTable(ColumnOne, ');
    Sql.Add('             ColumnTwo,           ');
    Sql.Add('             ColumnThree,         ');
    Sql.Add('             ColumnFour,           ');
    Sql.Add('             ColumnFive,          ');
    Sql.Add('             ColumnSix,        ');
    Sql.Add('             ColumnSeven,        ');
    Sql.Add('             ColumnEight,     ');
    Sql.Add('             ColumnNine,       ');
    Sql.Add('             ColumnTen,       ');
    Sql.Add('             ColumnEleven,     ');
    Sql.Add('             ColumnTwelve,   ');
    if qrySelect.FieldByName('ColumnTwelve').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnThirteen,   ');
      Sql.Add('           ColumnFourteen,   ');
      Sql.Add('           ColumnFifteen,   ');
    end;
    Sql.Add('             ColumnSixteen,   ');
    if qrySelect.FieldByName('ColumnSixteen').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnSeventeen,         ');
      Sql.Add('           ColumnEighteen,         ');
      Sql.Add('           ColumnNineteen,         ');
    end;
    if qrySelect.FieldByName('ColumnTwenty').AsSTring = 'Y' then
    begin
      Sql.Add('           ColumnTwenty,  ');
      Sql.Add('           ColumnTwentyOne,        ');
      Sql.Add('           ColumnTwentyTwo,        ');
      Sql.Add('           ColumnTwentyThree,        ');
    end
    else
      Sql.Add('           ColumnTwenty,  ');
    Sql.Add('             ColumnTwentyFour) ');
    Sql.Add('Values(:ColumnOne, :ColumnTwo, :ColumnThree, :ColumnFour, ');
    Sql.Add('       :ColumnFive, ' + dateDB + ', :ColumnSeven,          ');
    Sql.Add('       :ColumnEight, :ColumnNine, :ColumnTen, ');
    Sql.Add('       :ColumnEleven,                                    ');
    Sql.Add('       :ColumnTwelve,                                    ');
    if qrySelect.FieldByName('ColumnTwelve').AsSTring = 'Y' then
      Sql.Add('     :ColumnThirteen, :ColumnFourteen, :ColumnFifteen,              ');
    Sql.Add('       :ColumnSixteen,                                      ');
    if qrySelect.FieldByName('ColumnSixteen').AsSTring = 'Y' then
      Sql.Add('     :ColumnSeventeen, :ColumnEighteen, :ColumnNineteen,                 ');
    if qrySelect.FieldByName('ColumnTwenty').AsSTring = 'S' then
    begin
      Sql.Add('   :ColumnTwenty,                                      ');
      Sql.Add('   :ColumnTwentyOne, :ColumnTwentyTwo, :ColumnTwentyThree,                ');
    end
    else
      Sql.Add('   :ColumnTwenty,                                      ');
    Sql.Add('     :ColumnTwentyFour)                                  ');
    {And then for all the parameteres, pass the value}
    Parameters.ParamByName('ColumnOne').Value := varColumnOne;
    ...
    Parameters.ParamByName('ColumnTwentyFour').Value := varColumnTwentyFour;
    ExecSQL;
  end;

I get the error on this line:

Sql.Add('       :ColumnTwelve,                                    ');

which is the 11th parameter in my insert statement. If I comment this line I get the error in the next parameter. If I put the value directly like this:

Sql.Add('     ' + varColumnTwelve + ',                            ');

It works fine, but I get the error in the next parameter.

So it makes me wonder: does ADOQuery has a limit of how many parameters it can handle? Or if this isn't the real issue, does anyone has a clue of how I can fix this?

Notes:

Solution

  • The AV only (and always) appears when debugging, it does never appear if I execute the application directly through its ".exe".

    ....

    The insert works after all the AVs appeared on the screen. I just want to understand why am I getting this error when everything looks fine.

    The access violation is raised in an external module, implemented in a language other than Delphi. Most likely the external code is behaving correctly and as designed, and the access violation is expected.

    That may sound very odd but the external code clearly handles the exception since control does not pass to your code's exception handlers. As you observe, the program works correctly.

    This is what is known as a first chance exception. The debugger is notified and breaks. But then control returns to the program and in this case the program deals with the exception and continues. And it is perfectly normal, albeit perhaps counter-intuitive, for code to raise a first chance access violation exception, but still be functioning correctly. As evidence for that claim, see this from an article written by a member of the VS development team:

    Why the VS Debugger does not stop on first chance Access Violations (by default)?

    ....

    The reason the default for first-chance AVs does not stop is that sometimes Windows calls will AV, and then catch the exception themselves and carry on happily. If we did default to stopping on first chance AVs we would stop users in some strange place in say kernel32.dll and many would be very confused.

    So in terms of correctness, I don't think there's anything to worry about. But it does make debugging difficult. Try the various suggestions made by @Deltics. If by making those changes you happen to avoid the exception, that's all to the good. Otherwise you may need to, at least temporarily, suppress the debugger from breaking on exceptions.