sqlbulkcopy.WriteToServer throws Exception: column doesn't accept null value

See more:

C#

C#4.0

SqlBulkCopy

Hi,

I'm writing a method to import data from excel into a table in sql server. Excel sheet has columns "StudentID", "CourseID", "FirstName", "LastName", "StudentEmail", "StudentPassword". The table also has the exact same column names.

I'm using the code given below.

public void importdatafromexcel(string excelfilepath)
       {
           //declare variables - edit these based on your particular situation
           string ssqltable = "tblStudent";
           // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have
           // different
           string myexceldataquery = "select StudentID,CourseID,FirstName,LastName,StudentEmail,StudentPassword from [sheet1$]";
           try
           {
               //create our connection strings
               string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + excelfilepath + ";Extended Properties='Excel 12.0 xml;HDR=YES;'";
               string ssqlconnectionstring = "Data Source=NIHA\\SQLExpress;Initial Catalog=ExamReg;Integrated Security=True";
               //execute a query to erase any previous data from our destination table
               string sclearsql = "delete from tblStudent";
               SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
               SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
               sqlconn.Open();
               sqlcmd.ExecuteNonQuery();
               sqlconn.Close();
               //series of commands to bulk copy data from the excel file into our sql table
               OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
               OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
               oledbconn.Open();
               OleDbDataReader dr = oledbcmd.ExecuteReader();
               SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
               bulkcopy.DestinationTableName = ssqltable;
               /*bulkcopy.ColumnMappings.Clear();
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("StudentID", "StudentID"));
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("CourseID", "CourseID"));
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("FirstName", "FirstName"));
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("LastName", "LastName"));
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("StudentEmail", "StudentEmail"));
               bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("StudentPassword", "StudentPassword"));*/
               DataTable dt = new DataTable();
               dt.Load(dr);

               bulkcopy.WriteToServer(dt);


               oledbconn.Close();
               bulkcopy.Close();
           }
           catch (Exception ex)
           {
               //handle exception
           }
       }

When the control comes to the line

bulkcopy.WriteToServer(dt)

, I'm getting an exception that reads "Column 'StudentID' cannot accept null values". But my excel sheet doesn't have any null values anywhere. Still I'm getting this exception.

I have also tried by uncommenting the column mappings. But still I'm getting the same exception.

I have tried searching for a solution in google but to no use. Can someone please help me out with this?

Thanks!