asked on

Delphi X SQL Server (inserting null value)

Hi,

I have to execute a SQL statement into a SQL Server database using a TQuery component. I already used "INSERT INTO TABLE (FIELD1, FIELD2, ..) VALUES (:FIELD1, :FIELD2, ...)

I'm using
ParamsByName('FIELD1').AsInteger := Variable1;
ParamsByName('FIELD2').AsInteger := Variable2;

The problem is: Variable2 can be null. In Oracle, I would use ParamsByName('FIELD1').AsString := Variable2, so if Variable2 is null it would by the same as ParamsByName('FIELD1').AsString := ''

In SQL Server that won't work, because FIELD2 is a smallint field and it doesn't allow me to use AsString. Any idea?

Thanks

kretzschmar

use
ParamsByName('FIELD2').Clear;

works also for oracle

binho

ASKER

Kretzschmar, I tried that, but I get the error

Query1: Field 'FIELD2' is of an unknown type.

It seems Delphi doesn't recognize the parameter (as if I didn't set a value to the parameter). Note: I'm not using Query1.FieldByName('FIELD2')..., but ParamByName('FIELD2')

Tks

Kalroth

A quick solution would be to add an if statement.

ParamsByName('FIELD1').AsInteger := Variable1;
if Variable1 = NULL then
ParamsByName('FIELD2').AsInteger := Variable1
else
ParamsByName('FIELD2').AsInteger := Variable2;

I'm assuming that you're working with variants all the way. It's allowed to put null values in a field, unless NOT NULL has been specified on the field. Then you'd have to insert any numeric value instead.

Unless I totally misunderstood you, that is. :-)

binho

ASKER

Kalroth

If I use the way you suggested, I will get an "Invalid variant type conversion" error

Fabio

ASKER CERTIFIED SOLUTION

kretzschmar

membership

This solution is only available to members.

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

Start Free Trial

kretzschmar

try also ftInteger,
if the datatype above not works

binho

ASKER

Kalroth

If I use the way you suggested, I will get an "Invalid variant type conversion" error

Fabio

binho

ASKER

kretzschmar,

It worked out fine. Wouldn't it be easier if I just use a TQuery with Insert method and just write the FieldByName('FIELDXX').AsInteger := ... code to those parameters which are not null??
So, if I don't write the code to the "null situation", the field will be null by default...