Page 1 of 3
How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 1:51 am
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 2:06 am
by Armando
Rick:
A silly question
Do you have defined the field to accept NULL?
Regards
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 2:32 am
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 3:05 am
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 7:58 am
by Enrico Maria Giordano
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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 8:30 am
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 2:43 pm
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 3:17 pm
by Enrico Maria Giordano
Works fine here using latest xHarbour from CVS.
EMG
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 3:30 pm
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Tue Apr 21, 2009 7:58 pm
by Enrico Maria Giordano
Perhaps you should try with latest xHarbour from CVS.
EMG
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Wed Apr 22, 2009 2:56 am
by 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.
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Wed Apr 22, 2009 7:11 pm
by Rick Lipkin
Rao
YES ..
#xtranslate NULL => VTWrapper( 1, nil )
Was the answer !!
Thanks
Rick Lipkin
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Wed Apr 22, 2009 7:38 pm
by Enrico Maria Giordano
It seems you are using an old version of win32ole.prg.
EMG
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Wed Apr 22, 2009 8:18 pm
by 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
Re: How to write NULL to a Sql table
![Post Post](./styles/prosilver/imageset/icon_post_target.gif)
Posted:
Wed Apr 22, 2009 8:45 pm
by Enrico Maria Giordano
Sorry, I'm running out of ideas...
![Sad :-(](./images/smilies/icon_sad.gif)
EMG