NULL into string

[TonyScarpelli]

I'm trying to insert a row into a SQL Server table using the SQLEXEC command, something like this:

* Create the Insert command using built string
lcStr = "INSERT INTO MyTable ("
lcStr = lcStr + "WONUM,ID,TEXTFIELD,NUMERICFIELD)"
lcStr = lcStr + "VALUES ('"
lcStr = lcStr + lcWoNum + "','"
lcStr = lcStr + lcTextField + "'," + lnNumField + ")"

* Insert into SQL table
lnErr=SQLEXEC(gnConnHandle, lcStr)

If the lnNumField field is .NULL., I need the lnNumField to be represented by a string, something like this:

IF ISNULL(NUMERICFIELD)
lnNumField = ""
ELSE
lnNumField = STR(NUMERICFIELD,9,2)
ENDIF

However, this is giving me an SQL error, because the SQL table doesn't like an empty string where a number should be, but I need a string to create the string.

Can anyone tell me how to format a .NULL. into a string?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[Mike Lewis]

Tony,

the SQL table doesn't like an empty string where a number should be

Of course it doesn't. Nor would any other database. How can you place a string, empty or otherwise, in a numeric field?

This has got nothing to do with whether the value is NULL. You simply have to decide which data type you want for the field, and stick to it.

If I've misunderstood your question, my apologies.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

 * Create the Insert command using built string
TEXT TO lcStr NOSHOW TEXTMERGE
     INSERT INTO MyTable (WONUM,ID,TEXTFIELD,NUMERICFIELD)
                 VALUES ('<<lcWoNum>>',
                         'lcTextField',
                         <<lnNumField>>)
ENDTEXT
lnErr=SQLEXEC(gnConnHandle, lcStr)

if that didn't work (I can't test it right now)

 * Create the Insert command using built string
TEXT TO lcStr NOSHOW TEXTMERGE
     INSERT INTO MyTable (WONUM,ID,TEXTFIELD,NUMERICFIELD)
                 VALUES ('<<lcWoNum>>',
                         'lcTextField',
                         <<IIF(ISNULL(lnNumField),[NULL],lnNumField)>>)
ENDTEXT
lnErr=SQLEXEC(gnConnHandle, lcStr)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ilyad]

Function NULLIF

FUNCTION NULLIF(LnSRC1)
RETURN ICASE(VARTYPE(m.LnSRC1) ="X", 'NULL', EMPTY(m.LnSRC1), ;
   'NULL', VARTYPE(m.LnSRC1)=="C", ['] + ALLTRIM(m.LnSRC1) + ['], ;
   VARTYPE(m.LnSRC1)== "D", ['] + DTOC(m.LnSRC1) + ['], ;
   VARTYPE(m.LnSRC1)== "T", ['] + TTOC(m.LnSRC1) + ['], ;
   VARTYPE(m.LnSRC1)= "N" AND !EMPTY(m.LnSRC1),    ALLTRIM(STR(m.LnSRC1)), 'NULL')
ENDFUNC

by Aleksey Klimov (site in Russian

http://www.caws.atnet.ru/vfox/sql2.html)

 

[TonyScarpelli]

Mike Lewis, you got it wrong. You need a string to make a string, it was supposed to be funny about SQL server complaining, I knew that. ",'0'," is different than ",0," in the string I'm creating and sending in the command. I wanted to change ",0," to whatever would be the "Null" equivalent can be in a string.

The other two responses had it figured out as to what I was trying to do, so I will try those two answers today.

Thanks guys.

CU

Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[TonyScarpelli]

And the winner is...

IF ISNULL(NUMERICFIELD)
lnNumField = [NULL]
ELSE
lnNumField = STR(NUMERICFIELD,9,2)
ENDIF

I would never have figured out the syntax on this one: [NULL]. What do the brackets do? I'll have to look that one up. It works, too, no complaints from SQL Server.

Thanks bborissov.

CU all.

Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[bborissov]

Brackets are the string delimiters the same as quotation marks.
These expressions are the same:

m1 = 'bla bla bla'
m1 = "bla bla bla"
m1 = [bla bla bla]

I prefer square brackets because the expression is more visible.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Olaf Doschke]

Hi Tony,

Yes, instead of sending lcSQL = "...,0,..." or "....,,..." you send "...,NULL,...", that's all.

You could also use NVL(STR(numericfield,9,2),"NULL"), as STR(.NULL.) is .NULL. and then NVL() converts it to "NULL". Another more elegant way would be

SET NULLDISPLAY "Null"
lcNumfield = Transform(numericfield)

Bye, Olaf.

 

[ilyad]

That's why I posted NULLIF function. It also handles empty dates, as you may see, and all empty variables.

 

[Olaf Doschke]

Hi ilyad,

yes, nice. But a (nullable) numeric field cannot ever be an empty date or something like that, it's either null or a number.

Bye, Olaf.

 

[ilyad]

Right, that function was generic to work with any type of parameter.