>>>I haven't been following the whole thread but here's my two cents:
>>>
>>>If the update is being done through the view and the problem column is not in the view than the VFP ODBC driver has to decide what value to put into the column. It seems to have it's mind made up that it wants to insert a NULL. But the column is not defined with to accept NULLs. An easy solution would be to define a DEFAULT on the problem column.
>>>
>>>-Mike
>>
>>Ah, perhaps, I made a blunder missing that initial question mentioned that the error was during tableupdate(). Now it looks clear.
>
>As far as I can see this is not the case. I cannot add a new record via a view and ODBC under any circumstances, even thogh I have now simplified the situation. Here is the structure of my 'customer' table:
>
>***** Table setup for CUSTOMER *****
>CREATE TABLE 'CUSTOMER.DBF' NAME 'CUSTOMER' (ICUST_ID I NULL DEFAULT getnextkey("icust_id","customer","stress"), ;
> CSEARCHNAME C(40) NOT NULL DEFAULT "Enter name", ;
> IMEDIA_ID I NOT NULL, ;
> CACCT_REF C(10) NOT NULL, ;
> LMAILPREF L NULL DEFAULT .F., ;
> CCREDIT_INFO C(20) NOT NULL DEFAULT "To be evaluated", ;
> MNOTES M NOT NULL DEFAULT "Entered in the system on : "+DTOC(DATE())+" at : "+TTOC(DATETIME(),2)+CHR(13)+"*----------"+CHR(13), ;
> LCORP_INDIV L NOT NULL, ;
> YCREDIT_LIMIT Y NOT NULL, ;
> YCREDIT_BALANCE Y NOT NULL, ;
> ICURRENCY I NOT NULL, ;
> ISTATUS I NOT NULL, ;
> IVAT_STATUS I NOT NULL, ;
> DREC_ADDED T NOT NULL DEFAULT DATETIME())
>
>***** Create each index for CUSTOMER *****
>SET COLLATE TO 'MACHINE'
>INDEX ON CACCT_REF TAG CACCT_REF
>INDEX ON CSEARCHNAME TAG CSEARCHNME
>ALTER TABLE 'CUSTOMER' ADD PRIMARY KEY ICUST_ID TAG ICUST_ID
>
>and here is the structure of my now simplified one table remote view:
>
>
>CREATE SQL VIEW "TEST_VIEW" ;
> REMOTE CONNECT "MAINSTRESSCONNECTION" ;
> AS SELECT * FROM customer Customer WHERE Customer.icust_id = ?pnCust_id ORDER BY Customer.icust_id
>
>DBSetProp('TEST_VIEW', 'View', 'UpdateType', 1)
>DBSetProp('TEST_VIEW', 'View', 'WhereType', 3)
>DBSetProp('TEST_VIEW', 'View', 'FetchMemo', .F.)
>DBSetProp('TEST_VIEW', 'View', 'SendUpdates', .T.)
>DBSetProp('TEST_VIEW', 'View', 'UseMemoSize', 255)
>DBSetProp('TEST_VIEW', 'View', 'FetchSize', 100)
>DBSetProp('TEST_VIEW', 'View', 'MaxRecords', -1)
>DBSetProp('TEST_VIEW', 'View', 'Tables', 'customer')
>DBSetProp('TEST_VIEW', 'View', 'Prepared', .F.)
>DBSetProp('TEST_VIEW', 'View', 'CompareMemo', .T.)
>DBSetProp('TEST_VIEW', 'View', 'FetchAsNeeded', .F.)
>DBSetProp('TEST_VIEW', 'View', 'FetchSize', 100)
>DBSetProp('TEST_VIEW', 'View', 'Comment', "")
>DBSetProp('TEST_VIEW', 'View', 'BatchUpdateCount', 1)
>DBSetProp('TEST_VIEW', 'View', 'ShareConnection', .F.)
>
>
>In order to create my new record, all that I am doing at present is to browse my view. This throws up the dialog to ask me for a cust_id. I enter 0 (but I've tried entering valid values). The empty browse window comes up and I hit CTRL + Y to append a new record. I fill in the cSearchName and then skip down to the next record. At that point I get the error:
>
>Connectivity Error: [Microsoft][ODBC Visual FoxPro Driver]Index does not accept NULL
>
>I get the standard OK, Revert and Help dialog. I do not have anything else running. Buffering is not on. This happens no matter which table I select to base my view on. Any suggestions would be hugely appreciated. I have been working on this for one and a half days now and I still can't add a record! This feels like one of those things that ends up being really obvious, reight under your nose, but I am certain that I can't see it.

Hi again Steve,

I did exactly what your code said above - apart from hard coding the default new id.

I got a problem moving onto the next record, but not the same as yours. It errored telling me that field x couldn't be null. This is because the Update criteria on the view only sends modified fields - the fields in the browse that you haven't been into have been modified so the the FoxPro default values - 0 for numeric etc, don't get saved.

Could it be something wrong in your New Id function?

the saga continues....

Ian