Cannot Insert A Null Value Into Table Record

[chris80]

Using the sql profiler gives me no clue.

This is the insert query from the profiler.



exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'



The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.

Somehow up to a few weeks ago those two columns were updated.

Tracing the code and using the immediate window the user_name variable stores the login name corectly.

Someone mentioned "error 28" any ideas?

Thanks in advance

 

[The_Doc_Man]

Unless you go through VBA, you cannot change a field to be NULL (in the sense that IsNull([Field]) would be TRUE). Through SQL, I don't believe there is "official" syntax for it. Once you have something in a field, you can empty it by making it a zero (numerics) or a zero-length string (text), but NULL for a field value has the meaning that the field has never been initialized. Well, once it has been initialized, it has been initialized. Sort of like losing one's virginity. You can't go back again (officially).

Now if I misread that and you are talking about the ASCII NULL character, it is CHR$(0). But... oddly enough, an ASCII NUL is a character so a field having been set to ASCII NUL is neither NULL (in the IsNull sense) nor empty (in the IsEmpty sense).