Inserting Excel sheet into SQL Server database

In your extended properties for the excel connection string, you may wish to set HDR=YES and IMEX=1. HDR=YES will read the first row in your excel sheet and read the data stored in each cell as a column name. IMEX=1 tells JET to insert the data regardless of datatype from each cell.

Have a look at this code I have written myself, it may help you.

        //ExcelDA Class
        public ExcelDA()
    {
        connString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ofd.FileName + "; Extended Properties=" + '"' + "Excel 8.0; HDR=Yes; IMEX=1" + '"';
    }

    /// <summary>
    /// Method used to retrieve a datatable from excel sheet
    /// </summary>
    public DataTable GetProductsExcel()
    {
        StringBuilder excBuilder = new StringBuilder();
        excBuilder.Append("SELECT * FROM [Sheet1$];");
        //
        try
        {
            return ExecuteSqlStatement(excBuilder.ToString());
        }
        catch (Exception) 
        { 
            MessageBox.Show("Error retreiving data");
            return null;
        }
    }
    private DataTable ExecuteSqlStatement(string command)
    {
        using (OleDbConnection conn = new OleDbConnection(connString))
        {
            try
            {
                conn.Open();
                using (OleDbDataAdapter adaptor = new OleDbDataAdapter(command, conn))
                {
                    DataTable table = new DataTable();
                    adaptor.Fill(table);
                    return table;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
        }
    }
    //SqlDA class
    public void ExecuteSQLCommand(string comm)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                using (SqlCommand command = new SqlCommand(comm, conn))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
        catch (ArgumentOutOfRangeException ex)
        {
            throw ex;
        }

        catch (ArgumentException ex)
        {
            throw ex;
        }
        catch (SqlException ex) { throw ex; }
    }
    public void AddNames(string fName, string sName)
    {
          StringBuilder sqlQuery = new StringBuilder();
          //build sql insert statement here
          ExecuteSQLCommand(sqlBuilder.ToString());
    }

    //Program class
    ExcelDA reader = new ExcelDA();
    DataTable names = reader.GetSuppliersExcel();
    //string array to store excel row data for names
    string[] arr2 = new string[2] { "", ""};
        //recursive loop to retrieve each row and values from each rows columns
        foreach (DataRow row in suppliers.Rows)
        {
            for (int i = 0; i < names.Columns.Count; i++)
            {
                if (i == (names.Columns.Count - 1))
                {
                    arr2[i] = (row[i].ToString());
                }
                else
                {
                    arr2[i] = (row[i].ToString());
                }
            }
        }

            try
            {
                sql.AddNames(arr2[0], arr2[1]);
                Console.WriteLine("Added Data to SQL");
            }
            catch (Exception) { }

Sql server has a import tool called Sql Server Data Tools... there you can find a activity to import from a excel file... very "end-user" deal, by that i mean the you can configure everything on drag and drop basis...

I don't know if it's the root cause, but you're referencing ofd.FileName in your Click event when there's no guarantee that the dialog has a valid file selected. Since you're storing that value in a text box I would change it to:

string excelConnectionString = 
    @"Provider=Microsoft.Jet.OLEDB.4.0;data source=" 
          + textBox1.Text 
          + @";Extended Properties=Excel 8.0;";

I would also verify that you're able to query the data (e.g. by writing to a text file) as a test before trying to implement bulk copy. Also be aware that the source data must have exactly the same structure (including column order) unless you supply a ColumnMapping.