I developed a mvc 4 application that uses the sql bulk copy upload technique to upload data to databases.
Once I choose an excel file, the system may upload data.
Everything is functioning OK, however I'd want to set a default value if the excel field is empty.
This is how Excel column values are extracted.
"s1.Gender, " + "s1.Country_of_Birth, " + "s1.Date_of_Birth, " +
I currently manage this at the database level, but I want to set a default value at the code level.
for instance, I applied the following restriction to provide a default value for "date of birth."
ALTER TABLE [dbo].[tbl_HEI_student] ADD CONSTRAINT [DF_tbl_HEI_student_Date_of_Birth] DEFAULT (getdate()) FOR [Date_of_Birth]
However, SQL bulk copy upload technique ignores adding that default value database when I upload an excel file to a database.
How can I use the controller method's default value?
The fact that the column accepts NULLS must be your issue.
If your column supports NULL and you enter a NULL value, the characters WILL will be inserted. In the absence of a value for the column, the DEFAULT shall be taken into account.
Set the NOT NULL option for your column.
ALTER TABLE [dbo].[tbl_HEI_student] ALTER COLUMN [Date_of_Birth] DATE NOT NULL
I give you the Standard Definition.
You've probably observed that you can't bulk insert NULL values into those columns to compel SQL Server to use the default value; instead, the NOT NULL option I mentioned would cause SQL Server to accept either no value or a value other than NULL.
I see a couple possibilities.
Another query relating to your issue is Here.
Remember that your database structure should assist you in enforcing your business rules and maintaining accurate data. Keep the field as NOT NULL and adjust your solution if the date should never be NULL.