How to fix error in exporting Excel data to SQL Server database?

Question

Cannot insert the value null into column 'COLUMN1',table'DB1.dbo.table1';Column does not allow nulls. Insert fails.the statement has been terminated.

This always shows if edit or save another data onto excel columns.

 using (SqlCommand cmd = new SqlCommand("Insert into dbo.Table1 Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database="+ filepath + ";HDR=YES','SELECT * FROM [Sheet1$]')", conn))

Is there a best way to handle this....

Answer 1

Did you try an SQL Statement like this:

INSERT INTO Table1 SELECT ISNULL(YourColumn, GETDATE()) FROM OPENROWSET(...)

GETDATE() is only one of many options. If your column does not support NULL values, you have to provide another value.

Greets Flo

Answer 2

Also refer this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926