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
LVL 1
hidrauAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


ziolko.
0
ziolkoCommented:
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.
0
FactorBCommented:
I would also recommend to include
ShowMessage(TQ.FieldByName('LN_LINECOMAND').AsString);
it will be handy for debugging.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

hidrauAuthor Commented:
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;
0
ziolkoCommented:
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;
0
ziolkoCommented:
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
0
ziolkoCommented:
also you might use:

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

instead of

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

ziolko.
0
FactorBCommented:
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
0
ziolkoCommented:
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.
0
hidrauAuthor Commented:
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

0
ziolkoCommented:
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.
0
ziolkoCommented:
to make it simpler:

if you want  this:

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

to be executed only when error is raised use:


      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 Begin  << Begin was added
             GravaErroLog(E.ClassName + '  ' + E.Message);
              MudaTentativaErroGravacao(ID);
              GravaErroLog('Erro ao grava linha de comando ID:' + ID + ' na Conexão: ' + CnxBS);
            End; << this was added

         End;

BUT if you want it to be executed EVERYTIME use this:

      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);
         End;

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

      End Else Begin
         GravaErroLog('Sem link para roda linha de comando ID:' + TQ.FieldByName('ID').AsString + ' na Conexão: ' + CnxBS);


ziolko.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hidrauAuthor Commented:
thanks very much
0
hidrauAuthor Commented:
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?
0
ziolkoCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.