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)
{
string ssqltable = "tblStudent";
string myexceldataquery = "select StudentID,CourseID,FirstName,LastName,StudentEmail,StudentPassword from [sheet1$]";
try
{
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";
string sclearsql = "delete from tblStudent";
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
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;
DataTable dt = new DataTable();
dt.Load(dr);
bulkcopy.WriteToServer(dt);
oledbconn.Close();
bulkcopy.Close();
}
catch (Exception ex)
{
}
}
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!