SQLBulkCopy Throws InvalidOperationException Nullable Ints

c# invalidoperationexception nullable sql sqlbulkcopy

Question

I have a DataTable that I am attempting to upload, but I run into the following exception:
"The given value of type String from the data source cannot be converted to type int of the specified target column."

The only reason I can think that it could possibly be doing this is that there are empty cells in the DataTable being uploaded to a column of type INT. I try switching all empty values for DBNull.Value, but that doesn't seem to work for this specific column. The upload process is as follows:

private void UploadTable(string tableName, DataTable table)
    {
        foreach (DataRow r in table.Rows)
        {
            foreach (DataColumn c in table.Columns)
                if (r[c].ToString().Trim() == "")
                    r[c] = DBNull.Value;
        }

        using (SqlBulkCopy copy = new SqlBulkCopy(sqlCon))
        {

            copy.DestinationTableName = tableName;
            try
            {
                copy.WriteToServer(table);
            }
            catch (Exception ex)
            {
                lblError.Content = ex.ToString();
            }
        }
    }

The schema that I upload to is:

[dbo].[table](
[Date] [smalldatetime] NOT NULL,
[Dollar] [varchar](50) NULL,
[CName] [varchar](100) NULL,
[tick] [varchar](10) NULL,
[id1] [varchar](10) NOT NULL,
[Total Quantity] [int] NULL,
[Quantity] [int] NULL
)

Is there something special regarding SQLBulkCopy and nullable int coolumns that I don't know about? Any input is appreciated.

Accepted Answer

Bad file input caused errors- had decimals in it and was not supposed to


Popular Answer

Did you specify the column type to be int when you add datacolumns to the datatable?




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why