null IS NOT NULL

I’ve just spent a while answering a question about passing null values as parameters, so I thought I would re-use it here! The poster was having problems inserting data when the value for a column as NULL, and it seemed that they were contemplating using different insert statements for every possible nullable column. I hope you will recognise that this is not a great thing to do!!

The poster didn’t post DDL, so the code fragment would take some time to form back into a compiled program, so I referred them to http://msdn.microsoft.com/en-us/library/ms172138(VS.80).aspx which has a section on “Assigning Null Values” and would give the poster a better insight into using NULLs. After all, it is far more beneficial to the poster that they learn how to do something correctly than for me to re-write the code for them (which is my day job that I charge for!!).

When the a reply came back it indicated he/she had not grasped the points in the article or hadn’t read it properly, so I decided that it may be best to show by example. I created a test database and cut-down table using this script. This showed the basics of the problem the poster had.

I then wrote a test program that demonstrated how to use DBNull.Value or SQLGuid.Null to assign null values, and how just assigning null will fail.

So if you post on the forums, make sure that you cut out the irrelevant information. Don’t post DDL for a 50 column table if 48 aren’t needed. Cut down any code that you are using to something that can be cut and pasted into visual studio and compiled without additional work. Make sure what you post does work, syntax errors in DDL should not occur (unless that IS your issue!). Go over what you have posted to make sure it makes sense and will work/fail for others in the same way as your application. Post the error numbers and messages you are getting from the posted application.