Delphi X SQL Server (inserting null value)

binho
binho used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004

Commented:
use
ParamsByName('FIELD2').Clear;

works also for oracle

Author

Commented:
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

Commented:
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. :-)
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
Kalroth

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

Fabio
Top Expert 2004
Commented:
>Kretzschmar, I tried that, but I get the error

well, you must define the parameterType,
i guessed you've already done it,
because of persistent paramaters,
but well at runtime (can be also done at designtime),
like

ParamsByName('FIELD2').DataType := ftSmallInt; //guess thats the type you need
ParamsByName('FIELD2').Clear;

hope thats helps

meikl ;-)
Top Expert 2004

Commented:
try also ftInteger,
if the datatype above not works

Author

Commented:
Kalroth

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

Fabio

Author

Commented:
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial