C# bulkcopy to SQL table type issue

Question

Using C#, .net 4, Visual Studio 2010, and SQL Server 2008.

I currently have a table called NewPeriodPareto contains the following columns:

PG nvarchar(50)
Part nvarchar(50)
Sales float
LostSales float
Pareto6 int
p6 image
Pareto5 int
p5 image
Pareto4 int
p4 image
Pareto3 int
p3 image
CurrentPareto int
pNew Image
NewPareto int

The following is my code that try's to fill the table:

private void CreateNewPeriod()
{
        DataSet dsDG = new DataSet();
        DataTable dt = new DataTable();

        dsDG = (DataSet)ParetoGrid.DataSource;
        dt = dsDG.Tables[0];

        string ThetableName = "NewPeriodPareto";
        BulkInsertDataTable(myConn, ThetableName, dt);
}

public static void BulkInsertDataTable(string connectionString, string tableName, DataTable table)
{
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            bulkCopy.DestinationTableName = tableName;
            connection.Open();

            bulkCopy.WriteToServer(table);
            connection.Close();
        }
}

As you can see the code provided attempts to copy the data from my datatable. The datatable is bound to the My data grids source.

The following is the error I get:

@bulkCopy.WriteToServer(table);
The given value of type Int32 from the data source cannot be converted to type image of the specified target column.

Now the first thing I thought is that There was a type set wrong in one of my fields of my SQL table, but after checking many times, there appears to be nothing wrong.

So what I'm after is a solution to this little problem, or even an alternative/simpler way of achieving the same goal, that is to fill an SQL table from a programs DataTable.

the datagrid view is made from a stored procedure and added columns for images from code. is this what you mean? — lemunk
– lemunk, Commented Apr 30, 2012 at 13:53
Yes. I assumed that you are using table adapters to retrieve data. Does order of columns in grid match table definition? — Nikola Markovinović
– Nikola Markovinović, Commented Apr 30, 2012 at 13:55
yes, that was one of my first things to look at, the datatypes match as does the column order, this is what is making me confused about the error. Im thinking theres something when the datagridview.source equals the dataset — lemunk
– lemunk, Commented Apr 30, 2012 at 14:01
The only difference is that my procedure gets "some of the columns, All the columns except the image columns. Those are generated at runtime. So, has this go to do with the source looking at the data from the query only? — lemunk
– lemunk, Commented Apr 30, 2012 at 14:16

Pleun · Accepted Answer · 2012-04-30 15:29:16Z

1

[Short of time, so i am going to hive you a hint]

you should look into ColumnMappings of the SqlBulkCopy. This allows you to explicitly map the column of your source to the target. There might be some extra columns like an ID or similar that are in the way right now.

answered Apr 30, 2012 at 15:29

Pleun

8,9102 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pleun Over a year ago

msdn.microsoft.com/en-us/library/…

Collectives™ on Stack Overflow

C# bulkcopy to SQL table type issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related