0

I try to import an excel file, and in one of cells it contains the following bracelet character "("

and If I don't remove this character, it results with the below errors. If I remove the character there is no error. How to solve the issue ?

[InvalidOperationException: String or binary data would be truncated.] [InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.]

Here are the codes:

private void UploadData(string path, string dbTableName)
{
    //Create connection string to Excel work book
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
    //Create Connection to Excel work book
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel

    excelConnection.Open();
    DataTable dbSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get First Sheet Name
    OleDbCommand cmd = new OleDbCommand("Select * from [" + dbSchema.Rows[0]["TABLE_NAME"].ToString() + "]", excelConnection);

    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();
    SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);

    //Give your Destination table name
    sqlBulk.DestinationTableName = dbTableName;

    try
    {
        sqlBulk.WriteToServer(dReader);
        if (dbTableName == "TempTP")
        {
            SDatabaseManagerData.DatabaseManagerData.UpdateTP();
        }
        else if (dbTableName == "TempGTIP")
        {
            SDatabaseManagerData.DatabaseManagerData.UpdateGTIP();
        }

        lbl_Error.Visible = true;
        lbl_Error.Text = "Database updated!";

    }
    catch (SqlException ex)
    {
        lbl_Error.Visible = true;
        lbl_Error.Text = "Database updated edilemedi! Hata: " + ex.Message;
    }

    excelConnection.Close();
}
HOY
  • 1,067
  • 10
  • 42
  • 85
  • Are you sure the inserted text isn't longer than the column allows? – TomT Jan 13 '14 at 21:26
  • @TomT Yes, I have increased the nvarchar to 1500 characters to see if the issue is length related. – HOY Jan 14 '14 at 06:14

0 Answers0