September 1, 2009 at 11:50 am
I have an asp.net form (C#), that is reading an excel file and doing an SqlBulkCopy into my staging table into a SQL 2005 , database, but its skipping a column in the excel file but inserting all of the remaining columns.
What would cause the SqlBulkCopy to skip a column? I've used the following:
[Code]
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy t = new SqlBulkCopy(DbConnection()))
{
t.DestinationTableName = "StagingTable";
t.ColumnMappings.Add("Vr", "Vr");
t.ColumnMappings.Add("Task", "Task");
t.WriteToServer(dr);
t.Close();
}
}
connection.Close();
}
[/Code]
and I've even used the straight SqlBulkCopy
[Code]
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy t = new SqlBulkCopy(DbConnection()))
{
t.DestinationTableName = "StagingTable";
t.WriteToServer(dr);
t.Close();
}
}
connection.Close();
}
[/Code]
I've changed the column type in the db from, text, numeric, varchar, nvarchar, and still no luck.
Its defined as a "General" in the excel file. the data in that column is number (1,2,3,4,5 and so on)
what else could be causing this issue?
September 1, 2009 at 12:04 pm
I found the issue, but its going to cause an issue in the future.
What's happening is, if a column is NULL on the first row, then that column is NULL for all rows within that excel file even though there may be data in that column a few rows down. So if the file looks like this:
name location state task
smith NW Washington
smith NE Maine 1
it will fail
however, if the file looks like this:
name location state task
smith NW Washington 0
smith NE Maine 1
the SqlBulkCopy will work just fine. So is there anything I can add to allow NULL of the column in the first row is empty/blank in the spreadsheet that is being inserted?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply