Field is Null Error but It is Just Blank 
Author Message
 Field is Null Error but It is Just Blank

I am copying from one recordset to another.  The first recordset has a blank
character column and is not NULL by design.

When I copy the field value to another recordset during an update I get the
"cannot insert NULL into column XXXX error.

Is there a way to stop the record set from inserting a NULL if the value is
just ""?

I do not want to have to code my own insert sql command but I am going to if
there is no workaround.

Thanks,

--
Jeff W Stevens
CMSystems
http://www.*-*-*.com/
18957 E Crestridge Circle
Aurora, CO 80015
303-221-1527
FAX: 303-221-0375



Fri, 30 Aug 2002 03:00:00 GMT  
 Field is Null Error but It is Just Blank
Hi Jeff,

you don't say which database you use or how you make the insertions, so I'll
make some assumptions.

If you use the Jet engine and you make your copy by opening a rowset on the
source table, opening a rowset on the destination table, copying row by row,
column by column from source rowset to destination rowset, I have a
suggestion:
To the Jet engine, an empty string is actually a NULL string. If you want a
zero-length string (which I guess you do) you should pass a set of
double-quotes to Jet, like this:

OLESTR("\"\"") (do remember that all strings in Jet 4.0 is UNICODE)

I have spend some late hours figuring this out ;-)

HTH
S{*filter*}.


Quote:
> I am copying from one recordset to another.  The first recordset has a
blank
> character column and is not NULL by design.

> When I copy the field value to another recordset during an update I get
the
> "cannot insert NULL into column XXXX error.

> Is there a way to stop the record set from inserting a NULL if the value
is
> just ""?

> I do not want to have to code my own insert sql command but I am going to
if
> there is no workaround.

> Thanks,

> --
> Jeff W Stevens
> CMSystems
> http://www.*-*-*.com/
> 18957 E Crestridge Circle
> Aurora, CO 80015
> 303-221-1527
> FAX: 303-221-0375




Sat, 31 Aug 2002 03:00:00 GMT  
 Field is Null Error but It is Just Blank
In ORACLE, for example, "" is equivalent to NULL, if datatype is character
type. In other words, "" and NULL string are the same. I suspect this is the
same in many other RDBMS-es, so you cannot put the "" into the field if it
has not-null constraint.


Sun, 01 Sep 2002 03:00:00 GMT  
 Field is Null Error but It is Just Blank
If your using a JET database, and you setup the tables via Access, there is
an option on the parameters to allow zero lenght value for text fields.
I've never tried it to see if it works, but figured I'd mention it.

When doing this on other databases, I've used a " " (single space) to
signify an "empty" column, that isn't NULL.  This is just my own convention,
but it seems to work okay.  To check if it's a "empty" value, I strip spaces
(usually I move my string into a CString for ease of manipulation) and then
check lenght.  When copying the field directly, nothing special needs to be
one since it has a value.


Quote:
> I am copying from one recordset to another.  The first recordset has a
blank
> character column and is not NULL by design.

> When I copy the field value to another recordset during an update I get
the
> "cannot insert NULL into column XXXX error.

> Is there a way to stop the record set from inserting a NULL if the value
is
> just ""?

> I do not want to have to code my own insert sql command but I am going to
if
> there is no workaround.

> Thanks,

> --
> Jeff W Stevens
> CMSystems
> http://www.cmsysinc.com
> 18957 E Crestridge Circle
> Aurora, CO 80015
> 303-221-1527
> FAX: 303-221-0375




Sun, 01 Sep 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. I am new to programming and am lost

2. What field am I getting into?

3. am I parse error

4. I am recieving a compiler error

5. Please help me - I am getting linker error !!!

6. Why I am getting Error C2091: function returns function

7. Newbie:- Why am i getting a linking error

8. Linker error - which library am I missing ?

9. Don't understand why I am getting the C2236 error

10. Why am I getting this error - compiler bug?

11. how good am I? Am I Good Enough????

12. Can CRecordset automatically trim blanks from fields?

 

 
Powered by phpBB® Forum Software