CODEWITHSUNDEEP
delphi
nick
nick

Reputation: 3239

TADODataset catch Post method error code in Delphi

I have a dataset, and I'm using this to catch the errors:

try
  FDataSource.DataSet.Post;    
  ShowMessage('success message!');
except
  on E : EDatabaseError do
  begin
    if (Pos('duplicate value', E.Message) > 0) or (Pos('duplicate key', E.Message) > 0) then
      ShowMessage('my custom error message')
    else
      ShowMessage('generic db error message');
  end;
end;

This is a horrible solution cause it's relying on finding the string 'duplicate value' or 'duplicate key' on the error message.

I want to be able to get some error code.

Is there any way to get it?

Upvotes: 1

Views: 619

Answers (1)

MartynA
MartynA

Reputation: 30715

You may be able to catch the error via your AdoConnection object.

The TAdoConnection has an Errors object (see definition in AdoInt.Pas). To investigate it, I used a stored proc on the server defined as

create PROCEDURE [dbo].[spRaiseError](@AnError int)
AS
BEGIN
  declare @Msg Char(20)
  if @AnError > 0
    begin
      Select @Msg = 'MyError ' + convert(Char(8), @AnError)
      RaisError(@Msg, 16, -1)
    end
  else
    select 1
END

Then, in my Delphi code I have something like this:

uses [...] AdoInt, AdoDB, [...]

procedure TForm1.Button1Click(Sender: TObject);
var
  S : String;
  IErrors : Errors;
  IError : Error;
  ErrorCount : Integer;
  i : Integer;
begin
  S := 'exec spRaiseError ' + Edit1.Text;
  AdoQuery1.SQL.Text := S;
  try
    AdoQuery1.Open;
  except
    IErrors := AdoConnection1.Errors;
    ErrorCount := IErrors.Count;
    for i := 0 to ErrorCount - 1 do begin
      IError := IErrors.Item[i];
      S := Format('error: %d, source: %s description: %s', [i, IError.Source, IError.Description]);
      Memo1.Lines.Add(S);
    end;
    Caption := IntToStr(ErrorCount);
  end;
end;

If I set AdoQuery1's Sql.Text to 'select * from anything' I get

error: 0, source: Microsoft OLE DB Provider for SQL Server description: Invalid object name 'anything'.

If you try it out, you should find that the contents of the Errors collection is cumulative, but Errors has a Clear method to clear it.

See https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/error-object?view=sql-server-2017 https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/errorvalueenum?view=sql-server-2017 for more info (links courtesy of Remy Lebeau)

Upvotes: 3

Related Questions