0

I'm using vfp9 but the program may have been written in an earlier language. When i open up the database through vfp, the field is read only, unlike the other fields. Doing it manually (select max + 1) doesn't work on the insert, it just makes the field = 0.

I'm having trouble finding examples online for the proper syntax, can anyone help?

Edit: OK, the solution was to leave the field blank. The field is in a table that is eventually appended to a larger table (the one with an autonumber field). Before it's appended, the field is used in several seeks, though.

I'd prefer not to fix the code everywhere by simply setting the field to null before the append. replace field with null gives an error though. What's the proper syntax for setting a table field to null?

Edit again: dropping the column worked. Apparently you can't set a number field to null.

DanielST
  • 13,783
  • 7
  • 42
  • 65
  • You can set integer (and probably other) numeric type fields to null if you set the field to accept nulls. – DaveB Feb 07 '12 at 00:10
  • would that screw up other programs that use the same table? I'm hesitant to mess with table structures that are already built and being used. – DanielST Feb 07 '12 at 16:49
  • If you have code that is not expecting a null, then yes, that code may throw an error. Without setting the field to accept nulls, you may need to find another solution to your problem. – DaveB Feb 07 '12 at 17:12

2 Answers2

1

If your table structure is as follows:

Structure for table:    D:\SO9162291.DBF
Number of data records: 2       
Date of last update:    02/06/12
Code Page:              1252    
Field  Field Name      Type                Width    Dec   Index   Collate Nulls
    1  COUNT           Integer                 4                            Yes
    2  CONTENTS        Character              24                             No
** Total **                                   30

The count field is set to allow nulls, so you can set a field of type integer to a null value using any of the following methods.

USE so9162291
INSERT INTO so9162291 (count, contents) VALUES (null, "Test record")
UPDATE so9162291 set count = null WHERE contents = "Test record"
REPLACE count WITH null
DaveB
  • 9,470
  • 4
  • 39
  • 66
  • Actually, when doing an insert to a table with auto-increment, you should completely leave that column OUT of the insert command, it will be auto-generated... insert into YourTable ( contents ) values ( 'test' ) -- will automatically assign the "count" integer value. – DRapp Feb 08 '12 at 23:58
  • @DRapp - My response was geared more to the question of "What's the proper syntax for setting a table field to null?" and not auto-increment fields. I do agree with your comment. Thanks for the input. – DaveB Feb 09 '12 at 00:04
0

I would perform the following SQL queries:

SELECT MAX(id)+1 AS laCount FROM so9162291 INTO CURSOR temp

INSERT INTO so9162291 (count, contents) VALUES(temp.laCount,"Test record")
Cory
  • 1,794
  • 12
  • 21
Staidly
  • 26
  • 2