How to write NULL to a Sql table

[Rick Lipkin]

To All

How do I write NULL to a numeric or a nvarchar field on a Sql Server table .. when I do this :

oRsFac:Fields("permnum"):Value := if(i->permnum = 0, '', i->permnum )

"permnum" is a nvarchar(255) and i->permnum is numeric 10,0 .. The if() statement writes a zero to the SQL table and not the '' or NULL value ..

Same problem when I test for a char field and want to write NULL to the SQL table ..

Any Ideas ??

Thanks
Rick Lipkin

[Armando]

Rick:

A silly question

Do you have defined the field to accept NULL?

Regards

[Armando]

Rick:

With this sample I have no problem

Code: Select all  Expand view

LOCAL nValor    := 10.50
// The field "UNI_USU" is VARCHAR(10) NOT NULL Type
oRsUni:Fields("UNI_USU"):Value := IIF(nValor = 0.00,'',nValor)
oRsUni:UpDate()
 

Neither with this an other example I have no problem neither

Code: Select all  Expand view

LOCAL nValor    := 10.50
oRsUni:Fields("UNI_USU"):Value := IIF(nValor > 0.00,'',nValor)
oRsUni:UpDate()
 

Regards

[Rick Lipkin]

To All

YES .. all the fields are marked to accept 'null' .. for some reason on SQL Server the in line if() does not seem to want to accept '' as null ( for me ) ..

I have in the mean time used a regular if statement and only append a field if it is NOT null .. leaving the field NULL where it needs to be .. a lot of extra code

Rick Lipkin

[Enrico Maria Giordano]

oRsFac:Fields("permnum"):Value := if(i->permnum = 0, '', i->permnum )

Try

Code: Select all  Expand view

oRsFac:Fields("permnum"):Value   := if(i->permnum = 0, , i->permnum )

EMG

[gkuhnert]

Rick,

as SQL Statement you could write something like

Code: Select all  Expand view

UPDATE fac SET permnum = null WHERE permnum = 0

On the recordset it might then look like:

Code: Select all  Expand view

oRsFac:Fields("permnum"):Value := if(i->permnum = 0, nil, i->permnum )
   or
oRsFac:Fields("permnum"):Value := if(i->permnum = 0, null, i->permnum )
 

but I don't know if it works on the recordset

[Rick Lipkin]

To All

oRsRel:Fields("qualified_ind"):Value := if(i->qualify = " ", , )
oRsRel:Fields("qualified_ind"):Value := if(i->qualify = " ", nil ,nil )

Both expressions should return NULL and what happends in MS Sql server the field is just appends blank spaces ..

Rick Lipkin

[Enrico Maria Giordano]

Works fine here using latest xHarbour from CVS.

EMG

[Rick Lipkin]

Enrico

Using FWH 811 and the xHarbour build 1.1.0 rev 6195 .. the database is MS Sql Server .. not any other sql flavor.

Rick Lipkin

[Enrico Maria Giordano]

Perhaps you should try with latest xHarbour from CVS.

EMG

[nageswaragunupudi]

The solution I found long time back and working for me for assigning NULL to fields or parameter values.

Code: Select all  Expand view

#xtranslate NULL =>   VTWrapper( 1, nil )
...
...
// usage:
oRs:Fields(n):Value := NULL
oCmd:Parameters( n ):Value := NULL
 

I have not checked with the latest version of xHarbour, if assigning NIL has the same effect. If this works then its a lot better.

[Rick Lipkin]

Rao

YES ..

#xtranslate NULL => VTWrapper( 1, nil )

Was the answer !!

Thanks
Rick Lipkin

[Enrico Maria Giordano]

It seems you are using an old version of win32ole.prg.

EMG

[Rick Lipkin]

Enrico

Using the official binaries from FTDN for xHarbour 409 .. not linking in anything else .. and it appears the FTDN version has a newer build revision than xHarbour.org

Rick

[Enrico Maria Giordano]

Sorry, I'm running out of ideas...

EMG