A solution to the EmptyDataTemplate insert problem

Scott A. Pearson — Tue, 05 Aug 2008 12:29:48 GMT

A solution to the EmptyDataTemplate problem: how to insert via GridView when table is empty. This is Oracle-based and does not use a SqlDataSource.

If you have worked with the GridView feature for any length of time, then you probably have had to address the "EmptyDataTemplate" situation, where there are no records in the table from which you are pulling.

Here is a sample screen shot of the typical solution when the table is empty. This solution will work, although it is not too elegant. Also, it does not provide consistency to the user, who normally would be working with a GridView, assuming a populated table.

TYPE	*
D_OR_W
DOW
LOCKED
Insert Cancel

Here is a screen shot of the GridView that is based off of a table that has no records. This GridView was "tricked" into thinking that it had one record in the underlying table. The edit row has been disabled (i.e. read only) in the code behind.

		Type	Daily Or Weekly	Day Of Week	Locked?

Setting the Visible attribute will remove the edit row enirely from the GridView:
GridView3.Rows[0].Visible = false;

		Type	Daily Or Weekly	Day Of Week	Locked?

Here is a screen shot of the same GridView, but with a single record just added by the user.

		Type	Daily Or Weekly	Day Of Week	Locked?

HOW TO DO IT:

Assumptions:

1) Your web page already has the Insert functionality properly working within your GridView when the table is populated.

2) You are NOT using the Configure Data Source option. That is, you have written your own code to pull data from the table.

3) You do NOT have an EmptyDataTemplate defined in the .aspx file. If it is there, remove it.

4) This code uses an Oracle database, not SQL Server/other. The Oracle psuedo-table DUAL is used to "fake out" the GridView. (If you are using SQL Server, remove the FROM DUAL clause. Check out the "DUAL table" entry at this site: http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm).

HTML - This GridView lives in one of four tabs in a MultiView. The MultiView is not necessary to implement this solution.

<asp:GridView ID="GridView3" AutoGenerateColumns="False"
    DataKeyNames="TYPE" runat="server" AllowPaging="True" AllowSorting="True"
   ShowFooter="True" OnLoad="Page_Load" OnPageIndexChanging="GridView3_Page"
   OnRowEditing="GridView3_Edit" OnRowUpdating="GridView3_Update"
    OnRowDataBound="GridView3_RowDataBound" OnSorting="GridView3_Sort"
    OnRowDeleting="GridView3_Delete" OnDataBinding="GridView3_DataBinding"
    OnRowCommand="GridView3_RowCommand" PageSize="20" >

   <Columns>

        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button ID="lbEditPCL" runat="server" CommandName="Update" Text="Update" />
            </ItemTemplate>
            <FooterTemplate>
                <asp:Button ID="lbInsertPCL" runat="server" CommandName="Insert" Text="Insert" />
            </FooterTemplate>
        </asp:TemplateField>

        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button ID="lbDeletePCL" runat="server" CommandName="Delete" Text="Delete" OnClientClick="return confirm('Are you sure?')"/>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Type" SortExpression="Type">
            <ItemStyle Wrap="False" />
            <HeaderStyle Width="30px" />
            <ItemTemplate>
                <asp:TextBox ID="txtEditTYPE" ReadOnly="true" Text='<%# Bind("TYPE") %>' Width=40 runat="server"></asp:TextBox>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertTYPE" runat="server">
                    <asp:ListItem>A</asp:ListItem>
                    <asp:ListItem>B</asp:ListItem>
                    <asp:ListItem>C</asp:ListItem>
                    <asp:ListItem>D</asp:ListItem>
                    <asp:ListItem>E</asp:ListItem>
                    <asp:ListItem>F</asp:ListItem>
                    <asp:ListItem>G</asp:ListItem>
                    <asp:ListItem>H</asp:ListItem>
                    <asp:ListItem>I</asp:ListItem>
                    <asp:ListItem>J</asp:ListItem>
                    <asp:ListItem>K</asp:ListItem>
                    <asp:ListItem>L</asp:ListItem>
                    <asp:ListItem>M</asp:ListItem>
                    <asp:ListItem>N</asp:ListItem>
                    <asp:ListItem>O</asp:ListItem>
                    <asp:ListItem>P</asp:ListItem>
                    <asp:ListItem>Q</asp:ListItem>
                    <asp:ListItem>R</asp:ListItem>
                    <asp:ListItem>S</asp:ListItem>
                    <asp:ListItem>T</asp:ListItem>
                    <asp:ListItem>U</asp:ListItem>
                    <asp:ListItem>V</asp:ListItem>
                    <asp:ListItem>W</asp:ListItem>
                    <asp:ListItem>X</asp:ListItem>
                    <asp:ListItem>Y</asp:ListItem>
                    <asp:ListItem>Z</asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Daily Or Weekly" SortExpression="D_or_W">
            <ItemTemplate>
                <asp:DropDownList ID="ddlEditDorW" Text='<%# Bind("D_OR_W") %>' Width=70 runat="server">
                    <asp:ListItem>D</asp:ListItem>
                    <asp:ListItem>W</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertDorW" Width=70 runat="server">
                    <asp:ListItem>D</asp:ListItem>
                    <asp:ListItem>W</asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
            <ItemStyle Width="50px" Wrap="False" />
            <HeaderStyle Width="70px" Wrap="True" />
        </asp:TemplateField>

        <asp:TemplateField HeaderText="DOW" SortExpression="DOW">
            <ItemTemplate>
                <asp:DropDownList ID="ddlEditDOW" Text='<%# Bind("DOW") %>' runat="server">
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem>MONDAY</asp:ListItem>
                    <asp:ListItem>TUESDAY</asp:ListItem>
                    <asp:ListItem>WEDNESDAY</asp:ListItem>
                    <asp:ListItem>THURSDAY</asp:ListItem>
                    <asp:ListItem>FRIDAY</asp:ListItem>
                    <asp:ListItem>SATURDAY</asp:ListItem>
                    <asp:ListItem>SUNDAY</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertDOW" runat="server">
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem>MONDAY</asp:ListItem>
                    <asp:ListItem>TUESDAY</asp:ListItem>
                    <asp:ListItem>WEDNESDAY</asp:ListItem>
                    <asp:ListItem>THURSDAY</asp:ListItem>
                    <asp:ListItem>FRIDAY</asp:ListItem>
                    <asp:ListItem>SATURDAY</asp:ListItem>
                    <asp:ListItem>SUNDAY</asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Locked?" SortExpression="Locked">
            <HeaderStyle Width="35px" Wrap="True" />
            <ItemTemplate>
                <asp:DropDownList ID="ddlEditLocked" Text='<%# Bind("LOCKED") %>' Width=60 runat="server">
                    <asp:ListItem>N</asp:ListItem>
                    <asp:ListItem>Y</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertLocked" Width=60 runat="server">
                    <asp:ListItem>N</asp:ListItem>
                    <asp:ListItem>Y</asp:ListItem>
                </asp:DropDownList>
            </FooterTemplate>
        </asp:TemplateField>

   </Columns>

   <EditRowStyle BackColor="White" />
   <AlternatingRowStyle BackColor="LightCyan" />
   <FooterStyle BackColor="Aquamarine" BorderStyle="Double" HorizontalAlign="Left" />
   <EmptyDataRowStyle BackColor="White" />
   <RowStyle BackColor="White" />
</asp:GridView>
  
<asp:Label ID="hlbl_UserID3" runat="server" Text="hlbl_UserID3" Visible="False"></asp:Label><asp:Label ID="hlbl_UserName3" runat="server" Text="hlbl_UserName3" Visible="False"></asp:Label><asp:Label ID="hlbl_SortParms_tab3" runat="server" Text="hlbl_SortParms_tab3" Visible="False"></asp:Label><br />
</asp:View>

CODE BEHIND

public Boolean LoadGridView3()

    {
        lblError.Text = "";
        string SqlString;

        // STEP 1 --- Get a count of the # of records in the table.
        OracleConnection ora_con = new OracleConnection(myConString);
        try
        {
            ora_con.Open();
            string sCountString = "SELECT COUNT(*) FROM process_control_lock";

            OracleCommand cmd = new OracleCommand(sCountString);
            cmd.Connection = ora_con;
            cmd.CommandType = CommandType.Text;
            object oCount = cmd.ExecuteScalar();
            if (oCount == null)
            {
                nPCLRecCount = 0;
            }
            else
            {
                nPCLRecCount = int.Parse(oCount.ToString());
            }
        }
        catch
        {
        }

        // STEP 2 -- If there are no records in the table, then use this SELECT to create a dummy-type record
        // in the data set so the GridView will display. The call to NewRow() below is needed, also.
        // NOTES: If you are using a DropDownList, then you must provide a default value, as you can see below.
        // If you are using SQL Server, then try removing the FROM DUAL clause.
        if (nPCLRecCount == 0)
        {
            SqlString = "SELECT DISTINCT NULL AS TYPE, 'D' AS D_OR_W, " +
                                        "NULL AS DOW, 'Y' AS LOCKED " +
                                        "FROM DUAL";
        }
        else // There are one or more records in the table, so pull all of them.
        {
            SqlString = "SELECT * FROM process_control_lock " + hlbl_SortParms_tab3.Text;
        }

        try
        {
            OracleDataAdapter ora_da = new OracleDataAdapter(SqlString, ora_con);
            DataSet ds = new DataSet();
            int nFillCount = ora_da.Fill(ds);
            ora_da.Dispose();
            ora_con.Close();
            ora_con.Dispose();

            // STEP 3 -- If there are no records in the table, then fake out the
            // GridView by creating a new row on-the-fly in the data source.
            if (nPCLRecCount == 0)
            {
                DataRow drNewRow = ds.Tables[0].NewRow();
                GridView3.DataSource = drNewRow.Table.DefaultView;
                GridView3.DataBind();

                // STEP 4 -- Either REMOVE or DISABLE the edit row of the GridView.
                // This will remove the edit row enirely from the GridView.
                GridView3.Rows[0].Visible = false;

                // Or, keep the edit row but disable it.
                Button lbEditBtn = (Button)GridView3.Rows[0].FindControl("lbEditPCL");
                lbEditBtn.Enabled = false;
                Button lbDeleteBtn = (Button)GridView3.Rows[0].FindControl("lbDeletePCL");
                lbDeleteBtn.Enabled = false;
                TextBox tbEdit_Type = (TextBox)GridView3.Rows[0].FindControl("txtEditTYPE");
                tbEdit_Type.Enabled = false;
                DropDownList ddlEdit_DorW = (DropDownList)GridView3.Rows[0].FindControl("ddlEditDorW");
                ddlEdit_DorW.Enabled = false;
                DropDownList ddlEdit_DOW = (DropDownList)GridView3.Rows[0].FindControl("ddlEditDOW");
                ddlEdit_DOW.Enabled = false;
                DropDownList ddlEdit_Locked = (DropDownList)GridView3.Rows[0].FindControl("ddlEditLocked");
                ddlEdit_Locked.Enabled = false;

                // Put the selected table name into the Insert table name.
                //TextBox tbInsert_Table = ((TextBox)GridView3.FooterRow.FindControl("txtInsertINDEXTABLE"));
            }
            else
            {
                GridView3.DataSource = ds.Tables[0].DefaultView;
                GridView3.DataBind();
            }
       }
        catch (Exception ex)
        {
            errtext = "Unable to load records from PROCESS_CONTROL_LOCK. Please Contact support. " + ex.Message;
            lblError.Text = errtext;
            ora_con.Close();
            ora_con.Dispose();
            return false;
        }

        return true;
    }

Step 5 -- Run the code

Scott Pearson

A solution to the EmptyDataTemplate insert problem