Problem with SQL Server's default column property in ASP.NET

I've already added defined the default value 'default.gif' to a column of datatype ntext.

then on the aspx page I placed a file upload control and take it's file name value as a parameter in an INSERT query to the column. what I was trying to do is to be able to point to a specific file when there's no one uploaded so every item will have a picture even the user didn't upload one!

what I get is an empty value in the field, not even null a开发者_StackOverflownd not even an two quotations.

Just blank! I wonder why it doesn't add the default value!

The default value for a column is only used when an INSERT operation is performed, and either:

• The column is not included in the column list for the insert operation, or,
• The column is included in the column list for the insert operation, but the corresponding position in the VALUES list is given as DEFAULT, e.g.:

INSERT INTO Table1 (FileName,/* other columns */)

VALUES(DEFAULT,/* other values */)

I'm guessing that whatever your data layer is (you haven't specified one, that I can see) is incapable of generating the INSERT statement in this form.

Your code:

using (SqlCommand comm = new SqlCommand("INSERT INTO Teachers(Name, PictureFileName, Blog, IsAdmin) VALUES (@Name, @PictureFileName, @Blog, @IsAdmin);", conn))

You'll need to add some new code, something like:

string insertStatement = "INSERT INTO Teachers(Name, Blog, IsAdmin";
if(!String.IsNullOrWhitespace(/* Wherever you get the picture file name from */)
{
   insertStatement += ", PictureFileName";
}
insertStatement += ") VALUES (@Name, @Blog, @IsAdmin";
if(!String.IsNullOrWhitespace(/* Wherever you get the picture file name from */)
{
   insertStatement += ", @PictureFileName";
}
insertStatement += ");";

Then use that insertStatement variable in the SqlCommand constructor - you might want to make adding the @PictureFileName parameter conditional too, and if this is a performance critical portion of the app, you might want to use a stringbuilder to construct this insert statement, or use string formatting code.

Make sure the query implementing the upload inserts a NULL into the column, not a blank string. Just passing through a blank string (i.e. '') does not necessarily mean you'll get the default value inserted.