FiveTech Software tech support forums

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

Rick:

A silly question

Do you have defined the field to accept NULL?

Regards

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

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

Rick Lipkin wrote: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

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

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

Works fine here using latest xHarbour from CVS.

EMG

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

Perhaps you should try with latest xHarbour from CVS.

EMG

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.

Rao

YES ..

#xtranslate NULL => VTWrapper( 1, nil )

Was the answer !!

Thanks
Rick Lipkin

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

EMG

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

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

EMG