Why does my parameterized query with Integer fields fail?

Question

I have a TQuery object, pointed to a dBase database and I'm wondering how I should go about parameterizing my insert statement.

The following INSERT-query will will work fine with qry.ExecSQL:

qry.SQL.Text :=
  'INSERT INTO KUNDE ' +
  '(FNAVN, ENAVN, INSTNR) ' +
  'VALUES ' +
  '(:FirstName, :LastName, ' + IntToStr(InstructorNo) + ' )';

qry.ParamByName('FirstName').AsString := FirstName;
qry.ParamByName('LastName').AsString := LastName;

But, this fully parameterized version fails with BDE error 'Type mismtach in expression':

qry.SQL.Text :=
'INSERT INTO KUNDE ' +
'(FNAVN, ENAVN, INSTNR) ' +
'VALUES ' +
'(:FirstName, :LastName, :InstructorNo)';

qry.ParamByName('FirstName').AsString := FirstName;
qry.ParamByName('LastName').AsString := LastName;
qry.ParamByName('InstructorNo').AsInteger := InstructorNo;

I have tried various variations of the assignment of InstructorNo, such as .Value instead of AsInteger, but they all produce the same error.

The column 'INSTNR' is defined as Numeric, maxwidth=4, decimals=0. The value I'm attempting to assign is 999.

The function parameter InstructorNo is of type Integer.

Is this some kind of known bug in BDE?

EDIT: I have partly figured this one out

I can overcome this issue on some of the fields by using .AsSmallInt instead of .AsInteger, however on another numeric field neither Integer, SmallInt or Word works. The only way around that was to manually insert the value into the SQL statement. What's so special with a dBase Numeric field with maxwidth=6 ?

ANOTHER EDIT: Finally got it

I had to use .AsFloat to get the value stored. Though a bit weird to use a float type field for a CustomerID.

Answer 1

By assigning the value through .AsInteger, the parameter gets marked as a 4-byte Integer.
That will not fit in a 2-byte dBase integer (4 position integer is 2 bytes).
Hence the error message.

--jeroen