Click here to Skip to main content
15,314,644 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:

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$]";
               //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);
               //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);
               OleDbDataReader dr = oledbcmd.ExecuteReader();
               SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
               bulkcopy.DestinationTableName = ssqltable;
               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();


           catch (Exception ex)
               //handle exception

When the control comes to the line
, 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?

Mahesh Bailwal 8-Dec-13 10:06am
Can you debug at run time and check what values are getting filled in DataTable.
Amogh Natu 8-Dec-13 12:01pm
The data table is getting filled with all the data from the Excel sheet. That is all correct. Only when the control reaches that line, I'm getting that exception.
Mahesh Bailwal 8-Dec-13 12:13pm
One more way of debugging is to remove not null constrain from StudentID column in SQL server and than do bulk insert and after that check SQL database if there is any null value got inserted for StudentID column.

1 solution

The column studentId should be the identity column in your table I guess.
Here is a what I did to solve this .
Just map the columns that you need to insert like below

SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping("CUSTOMER", "CUSTOMER");

dont bind your identity column in this list rest all columns you can and then do

will work.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900