Bulkcopy to SQL table problem in C#

c# datagridview sql sqlbulkcopy

Question

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

Right now I have a table namedNewPeriodPareto is divided into the following columns:

  • PG nvarchar(50)
  • Nvarchar part (50)
  • Revenue float
  • float for LostSales
  • Pairwise int
  • P6 photo
  • int Pareto5
  • P5 photo
  • int Pareto4
  • P4 picture
  • int Pareto3
  • P3 photo
  • int CurrentPareto
  • New Picture
  • Int NewPareto

My code, which tries to fill the table, is as follows:

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();
        }
}

You can see that the code tries to replicate the information from my datatable. The source of My data grids is where the datatable is bound.

This is the error I experience:

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

Initially, I believed that one of the columns in my SQL database had an incorrect type set, but after several checks, it seems that nothing is amiss.

I'm looking for a solution to this little issue or maybe a another, easier approach to do the same task, which is to populate a SQL database using data from a program's DataTable.

1
2
4/18/2017 8:39:20 PM

Accepted Answer

[I have little time, so I'll give you a clue]

You need to investigate the SqlBulkCopy's ColumnMappings. This enables you to directly map the source column to the destination. There may be some additional columns, such as an ID or something similar, that are now blocking the view.

0
4/30/2012 3:29:16 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow