check datatype in SqlBulkCopy

[pranavcool]

Hi, I am inserting data from excel sheet to sql server table. The following is the code which is used to export data from excel ti sql server table.

private void insdata()
{
try
{
string filename = "~\\Documents\\" + ViewState["MissionCat"];
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(filename) + "; Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                connection.Open();

                OleDbCommand command = new OleDbCommand("Select \[Mission\] ,\[Civilian\] ,\[Military\] ,\[Police\]" +
                 ",\[Unknown\] ,\[Non-UN\] ,\[Total\] FROM \[Sheet1$\]", connection);

                string sqlConnectionString = ConfigurationManager.ConnectionStrings\["conn"\].ToString();

                //truncate the table before inserting new data.
                SqlConnection cnntrunc = new SqlConnection(sqlConnectionString);
                SqlCommand truntbleBU = new SqlCommand();
                truntbleBU.Connection = cnntrunc;
                string trunsqlBU = null;
                trunsqlBU = "TRUNCATE TABLE MissionCatReport";
                truntbleBU.CommandText = trunsqlBU;
                cnntrunc.Open();
                truntbleBU.ExecuteNonQuery();
                cnntrunc.Close();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "MissionCatReport";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

There is numeric field in table such as total. Now i am getting an error when a user tries to export data from excel to sql table if the total column in excel sheet contains string value instead of numeric value because the total column in sql table is numeric. I want to keep the total column in sql tabl

[Abhishek Sur]

Well, the problem is you cant get total as no data is stored within the Total, rather it holds only the Formula. Rather you have to calculate the total yourself in SQL. :rose:

Abhishek Sur

---

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.