asked on

How can I get the message error from my adoquery?

Hello Guys,

Is There any way to get the message error when I try to run my query and It gives me error with foreignkey; Please See my code:

Try
TQ2.Close;
TQ2.Sql.Clear;
TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);
TQ2.ExecSQL;

Except
Get error in string variable and show to user with showmessage
End;

Thanks

Lukasz Zielinski

try
except
on E: Exception do
ShowMessage(E.ClassName + ' ' + E.Message);
end;

ziolko.

Lukasz Zielinski

so in your case:

try
TQ2.Close;
TQ2.Sql.Clear;
TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);
TQ2.ExecSQL;
except
on E: Exception do
ShowMessage(Format('%s - %s', [E.ClassName, E.Message]));
end;

ziolko.

FactorB

I would also recommend to include
ShowMessage(TQ.FieldByName('LN_LINECOMAND').AsString);
it will be handy for debugging.

hidrau

ASKER

Why I am having error after this code:

on E: Exception do
ShowMessage(E.ClassName + ' ' + E.Message);

it is giving me the message: END, expected but identifier "MudaTentativaErroGravacao" found

"MudaTentativaErroGravacao" is a function created by me

TQ2.ExecSQL;
Except
on E: Exception do
ShowMessage(E.ClassName + ' ' + E.Message);

MudaTentativaErroGravacao(ID);
GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);

End;

Lukasz Zielinski

try this:

TQ2.ExecSQL;
Except
on E: Exception do begin
ShowMessage(E.ClassName + ' ' + E.Message);

MudaTentativaErroGravacao(ID);
GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);
endl
End;

Lukasz Zielinski

ooops typo, should be:

TQ2.ExecSQL;
Except
on E: Exception do begin
ShowMessage(E.ClassName + ' ' + E.Message);

MudaTentativaErroGravacao(ID);
GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);
end;
End;

@FactorB to be really >>handy for debugging.<<
I would add
ShowMessage(TQ2.SQL.Text);

not only field value

ziolko

Lukasz Zielinski

also you might use:

MessageDlg(E.ClassName + ' ' + E.Message, mtError, [mbOK], 0);

instead of

ShowMessage(E.ClassName + ' ' + E.Message)

ziolko.

FactorB

It will be better organized if you wrap the text

ShowMessage(E.ClassName+#10#13+E.Message+#10#13+TQ2.SQL.Text+#10#13+TQ.FieldByName('LN_LINECOMAND').AsString);

Regards,
B

Lukasz Zielinski

hmm just noticed :

TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);

does that mean that LN_LINECOMAND contains SQL command text?

if so, you don't need to display TQ2.SQL.Text AND TQ.FieldByName('LN_LINECOMAND').AsString
because it's the same string

ziolko.

hidrau

ASKER

Hi Ziolko

Take a look at my code.

I am getting go on with it. After on E: Exception do GravaErroLog(E.ClassName + ' ' + E.Message);
the next code is getting that error

Function TFserviceReplicador.RodaTabErro: Boolean;
  Procedure MudaTentativaErroGravacao(ID: String);
  Var
    TQ: TADOQuery;
  Begin
    TQ := TADOQuery.Create(Nil);
    TQ.Connection := cnx1;
 
    Try
      With TQ do Begin
        Close;
        Sql.Clear;
        Sql.Add('UPDATE CTRGRVERRO set Ntentativas=Ntentativas+1');
        Sql.Add('WHERE ID=' + ID);
        ExecSQL;
      End;
    Finally
      FreeAndNil(TQ)
    End;
  End;
Var
  TQ, TQ2 : TADOQuery;
  CnxBS, BS, IP, ID : String;
begin
 
  TQ := TADOQuery.Create(Nil);
  TQ.Connection := cnx1;
 
  TQ2 := TADOQuery.Create(Nil);
 
  Try
    with Tq do Begin
      Close;
      Sql.Clear;
      Sql.Add('SELECT A.*, B.LN_DATAHORA, B.LN_LINECOMAND FROM CTRGRVERRO A');
      Sql.Add('INNER JOIN LINHACOMANDO B ON B.LN_ID=A.CODLINH');
      Sql.Add('AND A.NTentativas < 4');
      Sql.Add('ORDER BY A.CONEXAO, B.LN_ID');
      Open;
    End;
 
    While Not TQ.Eof do Begin
      CnxBS := TQ.FieldByName('CONEXAO').AsString;
      BS    := TQ.FieldByName('BASE').AsString;
      IP    := TQ.FieldByName('IPping').AsString;
      ID    := TQ.FieldByName('ID').AsString;
 
      TQ2.ConnectionString := 'Provider=SQLNCLI.1;Password=cj2492;Persist Security Info=True;User ID=sa;Initial Catalog=' + BS + ';Data Source=' + CnxBS;
 
      If HaLink(IP) Then Begin
         Try
           TQ2.Close;
           TQ2.Sql.Clear;
           TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);
           TQ2.ExecSQL;
 
           TQ2.Close;
           TQ2.ConnectionString := cnx1.ConnectionString;
           TQ2.Sql.Clear;
           TQ2.Sql.Add('DELETE FROM CTRGRVERRO WHERE ID=' + TQ.FieldByName('ID').AsString);
           TQ2.ExecSQL;
         Except
           on E: Exception do GravaErroLog(E.ClassName + '  ' + E.Message);
           MudaTentativaErroGravacao(ID);
           GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);
 
         End;
      End Else Begin
         GravaErroLog('Sem link para roda linha de comando ID:' + TQ.FieldByName('ID').AsString + ' na Conexão: ' + CnxBS);
      End;
      TQ.Next;
    End;
  Finally
    FreeAndNil(TQ);
    FreeAndNil(TQ2)
  End;
End;

Open in new window

Lukasz Zielinski

first of all do you want:

MudaTentativaErroGravacao(ID);
GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);

to be executed ONLY when error occurs or everytime?

next thing you call TQ2.ExecSQL twice in same try/except block you must be aware that when first ExecSQL fails with exception then second ExecSQL will not be executed

Try
TQ2.Close;
TQ2.Sql.Clear;
TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);
TQ2.ExecSQL; << if this will throw exception....

TQ2.Close;
TQ2.ConnectionString := cnx1.ConnectionString;
TQ2.Sql.Clear;
TQ2.Sql.Add('DELETE FROM CTRGRVERRO WHERE ID=' + TQ.FieldByName('ID').AsString);
TQ2.ExecSQL; <<... this will NOT be executed
Except

also in this function:

Procedure MudaTentativaErroGravacao(ID: String);
Var
TQ: TADOQuery;
Begin
TQ := TADOQuery.Create(Nil);
TQ.Connection := cnx1;

Try
With TQ do Begin
Close;
Sql.Clear;
Sql.Add('UPDATE CTRGRVERRO set Ntentativas=Ntentativas+1');
Sql.Add('WHERE ID=' + ID);
ExecSQL; << there's no try/except to catch errors raised by this
End;
Finally
FreeAndNil(TQ)
End;
End;

ziolko.

ASKER CERTIFIED SOLUTION

Lukasz Zielinski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hidrau

ASKER

thanks very much

hidrau

ASKER

ziolko:, when I got the error it run this line

Except
on E: Exception do GravaErroLog(E.ClassName + ' ' + E.Message);
End;

and after running it returns to this line again

Try
TQ2.Close;
TQ2.Sql.Clear;
TQ2.Sql.Add(TQ.FieldByName('LN_LINECOMAND').AsString);
TQ2.ExecSQL;

why?

Lukasz Zielinski

because it is in WHILE loop
if you want to break loop after first error use this:
Except
on E: Exception do begin
GravaErroLog(E.ClassName + ' ' + E.Message);
Break;
end;
End;

note that if you do so
this:
MudaTentativaErroGravacao(ID);
GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);
will NOT be executed

ziolko.