CODEWITHSUNDEEP
c#sqldatagridviewsqlbulkcopy
lemunk
lemunk

Reputation: 2636

C# bulkcopy to SQL table type issue

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

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

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.

Upvotes: 2

Views: 1774

Answers (1)

Pleun
Pleun

Reputation: 8920

[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.

Upvotes: 1

Related Questions