Problem with TMSSQL.Prepare

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Problem with TMSSQL.Prepare

Post by LHSoft » Mon 09 Sep 2013 01:55

Hello,

I have the following procedure:

Code: Select all

procedure TFormLagKas.BuchLagKDT(FilNr, Menge: Integer; Preis, PreisEK: Currency; Kunde, Bediener, Datum, Zeit, Kasse, Journal, Beleg: String);
var
  i: integer;
  s: String;
begin
  try
    for i := 0 to TbLagKas.FieldCount -3 do 
      try KDTSQL.ParamByName(TbLagKas.Fields[i].FieldName).Value := TbLagKas.Fields[i].Value; except end;
    KDTSQL.ParamByName('DatumVK').AsString          := Datum;
    KDTSQL.ParamByName('ZeitVK').AsString           := Zeit;
    KDTSQL.ParamByName('KassenNr').AsString         := Kasse;
    KDTSQL.ParamByName('JournalNr').AsString        := Journal;
    KDTSQL.ParamByName('BelegNr').AsString          := Beleg;

    KDTSQL.ParamByName('PreisEK').AsCurrency        := PreisEK;
    KDTSQL.ParamByName('PreisKasse').AsCurrency     := Preis;
    KDTSQL.ParamByName('MengeKasse').AsInteger      := Menge;
    try
      KDTSQL.ParamByName('Kundennummer').AsInteger  := StrToInt(Kunde);;
    except
      KDTSQL.ParamByName('Kundennummer').Value      := NULL;
    end;
    try
      KDTSQL.ParamByName('Bediener').AsInteger      :=  StrToInt(Bediener);
    except
      KDTSQL.ParamByName('Bediener').Value          := NULL;
    end;
    KDTSQL.ParamByName('SkontoBetrag').AsCurrency   := Skonto;
    KDTSQL.ParamByName('FilNr').AsInteger           := FilNr;
    KDTSQL.Prepare;
    KDTSQL.Execute;
  except
    on e: Exception do
    begin
      SL := TStringList.Create;
      if FileExists(MainForm.MoDir + '_EKA.txt') then SL.LoadFromFile(MainForm.MoDir + '_EKA.txt');
      SL.Insert(0, DateTimeToStr(now) + ' MoLagKDT: Eintrag bereits vorhanden: ' + T1);
      SL.SaveToFile(MainForm.MoDir + '_EKA.txt');
      SL.Free;
      inc(AnzErr);
      ShowMessage(e.Message);
    end;
  end;
end;
KDTSQL: TMSSQL
Since not doing KDTSQL.Prepare everything was OK.
If doing prepare before execute I get errormessage, that it is not allowed to convert a variant to integer, I should use CONVERT instead.
The exception comes when "execute".

If not using prepare, the NULL-Values are inserted well in columns (defined as NULL allowed). I need this, because if no customer defined at sale, the column customernumber (German: Kundennummer) gets NULL-Value.

Why is this problem with prepare?
I use it in cases a Query or SQL is executed in loops with diffrent values and I did'nt get error yet. Why here?

best regards
Hans

AndreyZ

Re: Problem with TMSSQL.Prepare

Post by AndreyZ » Tue 10 Sep 2013 10:37

Hello,

If you specified a value of the parameter using the Value property, it is sent to the server as variant. If you try to prepare a query containing such parameters, SQL Server may generate an error. This is a SQL Server behaviour and we cannot influence it. To avoid the problem, you should not use Value property, but use properties with the exact parameter type, such as AsString, AsInteger, etc. Another solution is to not prepare such queries.

Post Reply