locked
Error inserting data using SqlBulkCopy RRS feed

  • Question

  • I'm trying to batch insert data into SQL 2008 using `SqlBulkCopy`.

    Here is my table:

        IF OBJECT_ID(N'statement', N'U') IS NOT NULL
        DROP TABLE [statement]
        GO
        CREATE TABLE [statement](
          [ID] INT IDENTITY(1, 1) NOT NULL,
          [date] DATE NOT NULL DEFAULT GETDATE(),
          [amount] DECIMAL(14,2) NOT NULL,
        CONSTRAINT [PK_statement] PRIMARY KEY CLUSTERED
        (
            [ID] ASC
        ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) ON [PRIMARY]
        GO

    Here is my code:

        private DataTable GetTable()
        {
            var list = new List<DataColumn>();
            list.Add(new DataColumn("amount", typeof(SqlDecimal)));
            list.Add(new DataColumn("date", typeof(SqlDateTime)));
            table.Columns.AddRange(list.ToArray());

            var table = new DataTable("statement");
            table.Columns.AddRange(list.ToArray());
        
            var row = table.NewRow();
            row["amount"] = (SqlDecimal)myObj.Amount; // decimal Amount { get; set; }
            row["date"] = (SqlDateTime)myObj.Date; // DateTime Date { get; set }
            table.Rows.Add(row);
        
            return table;
        }

        private void WriteData()
        {
            using (var bulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
            {
                //table.Columns.ForEach(c => bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));
                bulk.BatchSize = 25;
                bulk.DestinationTableName = "statement";
                bulk.WriteToServer(GetTable()); // a table from GetTable()
            }
        }

    So I'm getting error:
    > The given value of type `SqlDateTime` from the data source cannot be converted to type `date` of the specified target column.

    Why?? How can I fix that? Help me, please!
    Sunday, June 21, 2009 2:26 PM

All replies

  • This should solve it:

     list.Add(new DataColumn("date", typeof(DateTime)));

    We don't support mapping SqlDateTime to new SQL 2008 date and time types.

    Also note you need to use TimeSpan for mapping to Time type.
    Monday, November 9, 2009 7:45 PM