Problem with TMSSQL.Prepare

[LHSoft]

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]

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.