Sqlbulkcopy gives error on float conversion (asp.net c#)

Question

Iam trying to insert bulk of excel to sql

sqlBulk.WriteToServer(dReader); gives error

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

Iam using below code

 System.Data.SqlClient.SqlConnection strConnection;
    strConnection = new System.Data.SqlClient.SqlConnection("Data Source=L06DPSGCD0001;Initial Catalog=ProductionEfficiency;User ID=sample;Password=sample");

    string path = ("C:\\Users\\s3802616\\Desktop\\" + (FileUpload1.FileName));

    System.Diagnostics.Debug.WriteLine("C:\\Users\\s3802616\\Desktop\\" + (FileUpload1.FileName));
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=" + "\"" + "Excel 12.0;HDR=YES;" + "\"";
    //Create Connection to Excel work book

    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel

    OleDbCommand cmd = new OleDbCommand("Select [Model],[Process],[STD_Hours],[UOM],[Section] from [Sheet1$]", excelConnection);
    //OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
    excelConnection.Open();
    strConnection.Open();
    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();
    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    //Give your Destination table name

    sqlBulk.DestinationTableName = "dbo.tblProcessStdHours";
    sqlBulk.WriteToServer(dReader);
    excelConnection.Close();
    strConnection.Close();
    lblStatus.Text = "Uploaded Successfully";

Pls Suggest.

score 1 · Accepted Answer · answered Aug 30 '16 at 08:22

1

Error says it all.

You are trying to copy "string/chars" in target column which is of "float" type.

You need to change data type of your target column in database table "dbo.tblProcessStdHours" to varchar.

answered Aug 30 '16 at 08:22

Dheeraj Kumar

3,917
8
43
80

I tried that too, it gives {"The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."} – Shankar Naren Aug 30 '16 at 08:48
i change all to varchar one by one, finally it shows "Received an invalid column length from the bcp client for colid 1." I think this is because of Sl. column which is auto generated in sql. how to make it in excel or i need to remove "Sl" serial no from Sql. – Shankar Naren Aug 30 '16 at 09:23
You can try altering the length. If you need larger size of text to get stored you can set column size to max – Dheeraj Kumar Aug 30 '16 at 09:24
Mmmm.. Actually in Sql 6 coulmns defined, in Excel 5 columns, extra is "SL" in Sql which is the primary Key i set. Is that the problem.... – Shankar Naren Aug 30 '16 at 09:27
If SL is an identity column, then it will be auto-generated and shouldnt create any problem. If its not identity column, you have to pass primary key as well. – Dheeraj Kumar Aug 30 '16 at 09:29
You have to alter columns, recreate columns with varchar(max) size. – Dheeraj Kumar Aug 30 '16 at 09:44
Ohhh i did, i getting uploaded successfully,but the "Process" and "Section" column is NULL, what to do, we are almost nearer to the solution...... – Shankar Naren Aug 30 '16 at 09:54
are those columns of your sql or excel? – Dheeraj Kumar Aug 30 '16 at 09:56
value are there in Excel but after uploading sql, the sql data is null. – Shankar Naren Aug 30 '16 at 09:57
What values are there in sql? Are they compatible with sql data type? – Dheeraj Kumar Aug 30 '16 at 09:58
Check your column names and mapping, if they are correctly passed – Dheeraj Kumar Aug 30 '16 at 09:59
Yes "Process" is alpha numeric with special characters, Section is just around 10 characters both are defined in sql as varchars – Shankar Naren Aug 30 '16 at 10:00

Sqlbulkcopy gives error on float conversion (asp.net c#)

1 Answers1