The value of the specified String type of the data source can not be converted to the nvarchar type of the specified target column SqlBulkCopy

0

My problem is the following I am wanting to pass an excel file to sql server. When I take the file and I want to pass it, I throw this error:

  

"The value of the specified String type of the data source can not be   convert to the nvarchar type of the specified target column ".

And therefore it does not insert it into the database.

I'm trying and I can not do conversion

Any idea what might be causing this error?

protected void Button5_Click(object sender, EventArgs e)
{       
    try
    {
        //string conexion = "Provider = Microsoft.ACE.OleDb.12.0;Data Source = C:/Users/dtorres/Desktop/Horamar/1131008.xlsx ;Extended Properties = \"Excel 8.0;HDR = Yes\"";

        string path = Path.GetFileName(FileUpload1.FileName);
        path = path.Replace(" ", "");
        FileUpload1.SaveAs(Server.MapPath("/ExcelFile/") + path);
        String Excelpath = Server.MapPath("/ExcelFile/") + path;

        string conexion = "Provider = Microsoft.ACE.OleDb.12.0;Data Source = " + Excelpath + ";Extended Properties = \"Excel 8.0;HDR = Yes\"";


        OleDbConnection origen = default(OleDbConnection);
        origen = new OleDbConnection(conexion);

        OleDbCommand seleccion = default(OleDbCommand);
        seleccion = new OleDbCommand("select * from [Hoja2$]", origen);

        OleDbDataAdapter adaptador = new OleDbDataAdapter();

        adaptador.SelectCommand = seleccion;

        DataSet ds = new DataSet();

        adaptador.Fill(ds);


        grDatos.DataSource = ds.Tables[0];
        grDatos.DataBind();

        origen.Close();


        SqlConnection conexion_destino = new SqlConnection();

        conexion_destino.ConnectionString = "Data Source = (Local); Integrated Security = True; Initial Catalog = openGL ";

        conexion_destino.Open();

        SqlBulkCopy importar = default(SqlBulkCopy);
        importar = new SqlBulkCopy(conexion_destino);
        importar.DestinationTableName = "mayorizacion";



        importar.WriteToServer(ds.Tables[0]);
        conexion_destino.Close();

    }
    catch (Exception ex)
    {

        Label3.Text = ex.Message;
    }
}
    
asked by Dieguito Weed 24.10.2018 в 18:20
source

1 answer

1

The origin of the error is in the data.

It is possible that one of your "excel" columns has no data.

Review the table in SQL Server and compare the information in Excel. Check it well because one of those columns may have incorrect values.

As mentioned in your comment , it was necessary to increase the size of the column to nvarchar(50) .

    
answered by 24.10.2018 / 20:27
source