Joined:	23/08/2014
Level:	Silver
Points:	104

Points:	5

The given value of type String from the data source cannot be converted to type int of the specified

importing excel data into sql server table

Code as follows

protected void btnexport_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
string CurrentFilePath = string.Concat(Server.MapPath("~/masterdata/Test1.xlsx" + FileUpload1.FileName));
FileUpload1.SaveAs(CurrentFilePath);

string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", CurrentFilePath);

OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = constr;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
connection.Open();
System.Data.Common.DbDataReader dr = command.ExecuteReader();
string sqlConnectionString = @"Data Source = DESKTOP; Initial Catalog = Sample; User ID = sa; Password = 12345";

SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
bulkInsert.DestinationTableName = "tblarmerregistration";
bulkInsert.WriteToServer(dr);
}
}

In run mode i click th export button and debug and shows error as follows

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

how to solve this error.

Joined:	28/09/2005
Level:	Gold
Points:	637

Points:	4

I think you have to do explicit column mapping type of columns are not same in destination and source tables like

// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";

// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID =
new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);

SqlBulkCopyColumnMapping mapName =
new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);

SqlBulkCopyColumnMapping mapMumber =
new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);

// Write from the source to the destination.
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}

Hope this will give idea to move forward.

Thanks!
B.Ramana Reddy

The given value of type String from the data source cannot be converted to type int of the specified

About dotnetspider.com

Quick Links

General

Services