How to validate columnname before sorting by column in DataGridView

Question

When I try to sort a certain column in my DataGridView by clicking on the columnheader, I get an exception. I know this happens because the columname contains a whitespace.

My question is now: How to put square brackets around the name when clicking the header?

I haven't found a property for the header click event yet or something similar to avoid this kind of problem dynamically. Is there any solution without changing the name in the file or datatable?

Answer 1

Here is the code, see below code screen shot, sheet data and code focusing on the concept.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;

namespace Example_C1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public string ConnectionString(string FileName, string Header)
        {
            OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
            if (System.IO.Path.GetExtension(FileName).ToUpper() == ".XLS")
            {
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX=1;HDR={0};", Header));
            }
            else
            {
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX=1;HDR={0};", Header));
            }

            Builder.DataSource = FileName;

            return Builder.ConnectionString;
        }

        public DataTable LoadData(string FileName, string SheetName, DateTime TheDate)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            DataTable dt = new DataTable();

            using (OleDbConnection cn = new OleDbConnection { ConnectionString = ConnectionString(FileName, "Yes") })
            {
                cn.Open();
                using (OleDbCommand cmd = new OleDbCommand { CommandText = 
                    @"
                        SELECT 
                            [Dates], 
                            [Office Plan] As OfficePlan 
                        FROM [Sheet2$] 
                        WHERE [Dates] = #8/21/2013#", Connection = cn })
                {
                    OleDbDataReader dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }

                return dt;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DateTime TheDate = new DateTime(2010, 8, 21);
            DataGridView1.DataSource = LoadData(
                Path.Combine(Application.StartupPath, "WS1.xlsx"), "Sheet2", TheDate).DefaultView;
            DataGridView1.Columns["OfficePlan"].HeaderText = "Office Plan";
        }
    }
}

Please note I used an existing example I did which is why there is a where condition.