Improve company productivity with a Business Account.Sign Up

x
?
Solved

Getting a Field In FoxPro To Accept Null

Posted on 2011-02-28
4
Medium Priority
?
1,871 Views
Last Modified: 2013-11-24
I am trying to do an insert statement into a foxpro database I created.  I need a numeric value to remain empty for processing reasons of another program.  If I leave the field out in my insert query I get an error that says "Field Name does not accept null".  I need to figure out how to get Foxpro to be able to accept a null value.
0
Comment
Question by:DPottsLM
  • 2
4 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 34999030
0
 
LVL 44

Expert Comment

by:pcelba
ID: 34999114
If you have FoxPro then you may do this change in IDE which is very easy:

Open the table by USE command, issue MODIFY STRUCTURE, and check the NULL box in appropriate column.

You may also issue following command:

ALTER TABLE YourTable ALTER COLUMN YourColumn NULL

If you don|t have Visual FoxPro IDE the you may issue above command via OLE DB but the table must be open exclusively.

BTW, empty for numeric field means value = 0.  FoxPro also supports BLANK values... NULL is something else than empty or blank in FoxPro and it is probably the best choice.

0
 
LVL 31

Expert Comment

by:Olaf Doschke
ID: 35005758
If you define a field not nullable the behvior you get is a 0 stored into that field, not an error:

Create Cursor curTest (int I not null, cText C(10))
Insert into curTest (cText) Values ('test')

Open in new window


So as far as I see it the error must have another reason than a not nullable field.

If the developer did define the table as follows, you'd have that error indeed:
Create Cursor curTest (int I not null default null, cText C(10))
Insert into curTest (cText) Values ('test')

Open in new window


But that would mean the developer of that database made a logical error, as a not nullable feidl should not have a null default value. The only reason I see is, he wanted to explicitly force a user of the table to insert a non null value, even if that's 0, but that would also be achived, if the field had no default value at all.

So afterall, I'd only be able to tell more, if you specify the exact error you get from which code.

Bye, Olaf.
0
 
LVL 31

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 35005783
Hm

another thing: If you do this you get a non nullable field
Create Cursor curTest (int I default null, cText C(10))
Insert into curTest (cText) Values ('test')

Open in new window


Yes, in regard of nullable the default is NOT, setting the default value to null does not automatically make the field nullable, so this needs to be explicitly set in the table designer or in code:
Create Cursor curTest (int I null default null, cText C(10))
Insert into curTest (cText) Values ('test')

Open in new window

This way the field is nullable and get's null if skipped in an insert.

Bye, Olaf.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question