Why DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xxx'?

Question

I am importing data from excel sheet but it works for one sheet but not another with the same columns and correct data. It works for many files but not few. I have attached the samples and error below with code. At bind, it throws error.

The sheet that is working:

and the sheet that is causing the error:

Error:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Amount'.

Code:

private void ImportDataToGrid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
        DataColumn dc = new DataColumn("NITNo", typeof(string));
        dc.DefaultValue = txtNitNo.Text.ToString();
        dt.Columns.Add(dc);

        dc = new DataColumn("WorkNo", typeof(string));
        dc.DefaultValue = txtWorkNo.Text.ToString();
        dt.Columns.Add(dc);

        //Bind Data to GridView
        gvBOQ.Caption = Path.GetFileName(FilePath);
        gvBOQ.DataSource = dt;
        gvBOQ.DataBind();
    }

GV:

<asp:GridView ID="gvBOQ" runat="server" AutoGenerateColumns="false" EmptyDataText="No Data Found"
    CssClass="table table-responsive table-bordered table-striped">
    <Columns>
        <asp:TemplateField HeaderText="S.No">
            <ItemTemplate>
                <%# Container.DataItemIndex+1 %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="NIT No">
            <ItemTemplate>
                <asp:Literal ID="liNITNo" runat="server" Text='<%#Eval("NITNo") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Work No">
            <ItemTemplate>
                <asp:Literal ID="liWorkNo" runat="server" Text='<%#Eval("WorkNo") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Item Code">
            <ItemTemplate>
                <asp:Literal ID="liItemCode" runat="server" Text='<%#Eval("ItemCode") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Item Description">
            <ItemTemplate>
                <asp:Literal ID="liItemDesc" runat="server" Text='<%#Eval("DescriptionOfItem") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Quantity">
            <ItemTemplate>
                <asp:Literal ID="liQuantity" runat="server" Text='<%#Eval("Quantity") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Unit">
            <ItemTemplate>
                <asp:Literal ID="liUnit" runat="server" Text='<%#Eval("Unit") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Rate">
            <ItemTemplate>
                <asp:Literal ID="liRate" runat="server" Text='<%#Eval("Rate") %>' />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Amount">
            <ItemTemplate>
                <asp:Literal ID="liAmount" runat="server" Text='<%#Eval("Amount") %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Answer 1

In these situations, the problem is usually caused by there being a space () in the column name.

I would recommend checking for that, and removing any spaces there.