InvalidOperation type String from data source Error

Question

I have encounter an error which is

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.

on below code. This code I been use since last month and its working, I'm not sure whats the root cause of this. Kindly advise

DataTable sheetTable = loadSingleSheet(@"" + var_SourceFilePath + "MBF_Cancel_Temp.xlsx" + "", sheetName);

loading sheet as below

private static DataTable loadSingleSheet(string fileName, string sheetName)
    {
        DataTable sheetData = new DataTable();
        using (OleDbConnection conn = returnConnection(fileName))
        {
            conn.Open();
            // retrieve the data using data adapter
            OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "] ", conn);
            sheetAdapter.Fill(sheetData);

            //OleDbCommand oledbcmd = new OleDbCommand("select F1, F2, F3, F4, F7, F8, F9, F10, F11, F12, F13, F14, F15, F5 from [" + sheetName + "] WHERE F5 = '100,000'", conn);
            OleDbCommand oledbcmd = new OleDbCommand("select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 from [" + sheetName + "] ", conn);
            //OleDbCommand oledbcmd = new OleDbCommand("select NO, LOC, [EFF DATE], [EXP DATE], [NAME], [I#C], [D#O#B], [ADD1], [ADD2], [ADD3], [ADD4], [ADD5], [P#C], [BEN#NAME], [BEN#I#C] from [" + sheetName + "] ", conn);
            //conn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(var_MSDBConn);
            bulkcopy.DestinationTableName = "AMB_Cancel_Temp";

            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);
            }

            conn.Close();

        }
        return sheetData;
    }


private static DataTable loadSingleSheet(string fileName, string sheetName)
{
    DataTable sheetData = new DataTable();
    using (OleDbConnection conn = returnConnection(fileName))
    {
        conn.Open();
        // retrieve the data using data adapter
        OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "] ", conn);
        sheetAdapter.Fill(sheetData);

        //OleDbCommand oledbcmd = new OleDbCommand("select F1, F2, F3, F4, F7, F8, F9, F10, F11, F12, F13, F14, F15, F5 from [" + sheetName + "] WHERE F5 = '100,000'", conn);
        OleDbCommand oledbcmd = new OleDbCommand("select F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 from [" + sheetName + "] ", conn);
        //OleDbCommand oledbcmd = new OleDbCommand("select NO, LOC, [EFF DATE], [EXP DATE], [NAME], [I#C], [D#O#B], [ADD1], [ADD2], [ADD3], [ADD4], [ADD5], [P#C], [BEN#NAME], [BEN#I#C] from [" + sheetName + "] ", conn);
        //conn.Open();
        OleDbDataReader dr = oledbcmd.ExecuteReader();
        SqlBulkCopy bulkcopy = new SqlBulkCopy(var_MSDBConn);
        bulkcopy.DestinationTableName = "AMB_Cancel_Temp";

        while (dr.Read())
        {
            bulkcopy.WriteToServer(dr);
        }

        conn.Close();

    }
    return sheetData;
}

`@""` results to an empty string (and so does `""`). Are you sure you meant that? What do you expect `@"" + var_SourceFilePath + "MBF_Cancel_Temp.xlsx" + ""` to produce? What is `var_SourceFilePath `? What does that method do? — Patrick Hofman, Jan 27 '15 at 07:34
var_SourceFilePath was the location path where to get the excel sheet. — Damon Ng, Jan 27 '15 at 07:39
The code posted has nothing to do with the question. Please post the code that produces the error *in the question itself*. not as an answer. Also specify *where* the error occurs, whether you tried debugging the code and what happened during debugging. — Panagiotis Kanavos, Jan 27 '15 at 07:45
Why do you call `dr.Read()`? `WriteToServer(dr)` will do so itself until all rows are read. At best you lose the first row. — Panagiotis Kanavos, Jan 27 '15 at 07:50
possible duplicate of [Can't insert data table using sqlbulkcopy](http://stackoverflow.com/questions/9273243/cant-insert-data-table-using-sqlbulkcopy) — Panagiotis Kanavos, Jan 27 '15 at 07:52

score 0 · Accepted Answer · answered Jan 27 '15 at 07:56

This error can occur if one of the table's columns is too short to store the value you are trying to store. This will occur for example if you have a 40-character string but your field is defined as nvarchar(30). It's not possible to be certain though, as you havent posted the table's definition.

The solution is to check the size of your columns and ensure they are large enough to accept the values you want to input. An alternative is to truncate the size of the incoming values to the size of the columns.

Hi Pana , I resolved the issue with other method. Thanks for the advise — Damon Ng, Jan 27 '15 at 08:37

InvalidOperation type String from data source Error

1 Answers1