Click here to Skip to main content
14,662,530 members
Rate this:
Please Sign up or sign in to vote.
See more:
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!
Posted
Comments
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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");
sbc.ColumnMappings.Add(mapID);

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

bulkcopy.WriteToServer(dt);
will work.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100