sqlbulkcopy.WriteToServer throws Exception：column不接受null值 [英] sqlbulkcopy.WriteToServer throws Exception: column doesn't accept null value
问题描述
我正在编写一种方法将excel中的数据导入到sql server中的表中。 Excel工作表包含StudentID，CourseID，FirstName，LastName，StudentEmail，StudentPassword列。该表也有完全相同的列名。
我正在使用下面给出的代码。
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)这一行时，我得到一个异常，读取列'StudentID'不能接受空值。但我的Excel工作表在任何地方都没有任何空值。我仍然得到这个例外。
我也试过取消注释列映射。但我仍然得到同样的例外。
我试图在谷歌搜索解决方案但没有用。有人可以帮我解决这个问题吗？
谢谢！
, 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!
推荐答案
;
尝试
{
// 创建我们的连接字符串
string sexcelconnectionstring = @ < span class =code-string> Provider = Microsoft.ACE.OLEDB.12.0; data source = + excelfilepath + ;扩展属性='Excel 12.0 xml; HDR = YES;';
string ssqlconnectionstring = 数据源= NIHA \\SQLExpress;初始目录= ExamReg;集成安全性=真;
// 执行查询y从目标表中删除任何先前的数据
string sclearsql = 从tblStudent删除;
SqlConnection sqlconn = new SqlConnection（ssqlconnectionstring）;
SqlCommand sqlcmd = new SqlCommand（sclearsql，sqlconn）;
sqlconn.Open（）;
sqlcmd.ExecuteNonQuery（）;
sqlconn.Close（）;
// 将数据从excel文件批量复制到我们的sql表中的一系列命令
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 （例外情况）
{
// 处理异常
}
}
"; 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)这一行时，我得到一个异常，读取列'StudentID'不能接受空值。但我的Excel工作表在任何地方都没有任何空值。我仍然得到这个例外。
我也试过取消注释列映射。但我仍然得到同样的例外。
我试图在谷歌搜索解决方案但没有用。有人可以帮帮我吗？
谢谢！
, 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!
列studentId应该是表格中的标识栏我猜猜。
这是我为解决这个问题所做的工作。
只需要映射下面需要插入的列
SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping（CUSTOMER，CUSTOMER）;
sbc.ColumnMappings.Add（mapID）;
不要绑定你的标识列在这个列表中休息所有列你可以然后做
bulkcopy.WriteToServer（dt）;
会工作。
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.
这篇关于sqlbulkcopy.WriteToServer throws Exception：column不接受null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
