How to insert new,update, delete existing record in single row of gridview

Question

I am developing ASP.NET application and i am using gridview in that.

Here, i am not using any textbox controls outside of the gridview to save the record.

everything i want to maintain in same row of the records.

for example,

when page load,

1. if any record exists for the customer, it should show the records in label else, gridview should show textbox for user entry.
2. remaining edit and update operation is same as we normally used.

First point is difficult to handle display the existing or add new record while page loading.

How to do this in simple gridview...?

I have done lot of gridview application. but, handling the new record and show the existing one is new concept in my application.

kindly help, how to do this with database..

Answer 1

I started a new answer.

So, additions are a combo box example, and a file uploader for each row.

Hence this markup now:

        <asp:ListView ID="ListView1" runat="server" 
            DataKeyNames="ID" 
            OnItemDataBound="ListView1_ItemDataBound" >
            <ItemTemplate>
                <tr>
                    <td>
                        <asp:TextBox ID="txtFirst" runat="server" Text='<%# Eval("Firstname") %>' Width="90px"/> </td>
                    <td>
                        <asp:TextBox ID="txtLast" runat="server" Text='<%# Eval("LastName") %>' Width="90px" /></td>
                    <td>
                        <asp:DropDownList ID="cboCity" runat="server"
                            DataTextField="City">
                        </asp:DropDownList>
                    </td>
                    <td>
                        <asp:TextBox ID="txtHotel" runat="server" Text='<%# Eval("HotelName") %>'
                            width="220px" /></td>
                    <td>
                        <asp:TextBox ID="txtDescription" runat="server" TextMode="MultiLine"
                            width="270px" Rows="2"
                            Text='<%# Eval("Description") %>' /></td>
                    <td style="text-align:center">
                        <asp:CheckBox ID="Active" runat="server" /></td>
                    <td>
                        <asp:TextBox ID="txtBookingDate" runat="server" 
                            Text='<%# Eval("BookingDate","{0:d}") %>'
                            TextMode="Date"
                            /></td>
                    <td>
                        <button id="cmdDelete" runat="server" class="btn myshadow" style="margin-left:15px"
                            onserverclick="cmdDelete_ServerClick"
                            >
                            <span aria-hidden="true" class="glyphicon glyphicon-trash"></span>
                        </button>
                    </td>
                    <td>
                        <asp:FileUpload ID="FileUpload1" runat="server" />
                        <asp:TextBox ID="txtFileName" runat="server"
                            Text='<%# Eval("UpLoadFile") %>'
                            onclick="mytoggle(this)"
                            > 
                        </asp:TextBox>
                    </td>
                </tr>
            </ItemTemplate>
            <LayoutTemplate>
                <table id="itemPlaceholderContainer" runat="server" border="0" 
                    style="width:100%" class="table table-hover table-bordered">
                    <tr runat="server">
                        <th  runat="server">First Name</th>
                        <th runat="server">Last Name</th>
                        <th runat="server">City</th>
                        <th runat="server">Hotel Name</th>
                        <th runat="server">Description</th>
                        <th runat="server">Active</th>
                        <th runat="server">Booking Date</th>
                        <th runat="server">Delete</th>
                        <th runat="server">File</th>
                    </tr>
                    <tr id="itemPlaceholder" runat="server">
                    </tr>
                </table>
            </LayoutTemplate>
        </asp:ListView>
    <button id="cmdAdd" runat="server" class="btn myshadow"
        onserverclick="cmdAdd_Click1">
        <span aria-hidden="true" class="glyphicon glyphicon glyphicon-share"> Add</span>
    </button>
    <button id="cmdSave" runat="server" class="btn myshadow" style="float:right"
        onserverclick="cmdSave_Click1">
        <span aria-hidden="true" class="glyphicon glyphicon-floppy-saved"> Save</span>
    </button>

And our code behind is this:

        private DataTable rstPeople = new DataTable();
        private DataTable rstCity = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadGrid();
                Session["MyTable"] = rstPeople;
            }
            else
                rstPeople = (DataTable)Session["MyTable"];
        }
        public void LoadGrid()
        {
            string strSQL = "SELECT City FROM City ORDER BY City";
            rstCity = General.MyRst(strSQL); // load city choices before binding
            strSQL = @"SELECT * FROM tblHotelsA ORDER BY HotelName";
            rstPeople = General.MyRst(strSQL);
            rstPeople.PrimaryKey = new DataColumn[] { rstPeople.Columns["ID"] };
            ListView1.DataSource = rstPeople;
            ListView1.DataBind();
        }
        public void LoadGridMemory()
        {
            string strSQL = "SELECT City FROM City ORDER BY City";
            rstCity = General.MyRst(strSQL); // load city choices before binding
            ListView1.DataSource = rstPeople;
            ListView1.DataBind();
        }
        protected void cmdSave_Click1(object sender, EventArgs e)
        {
            // pull grid rows back to table.
            GridToTable();
            
            // now send table back to database with updates
            string strSQL = "SELECT * FROM tblHotelsA WHERE ID = 0";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL,
                                        new SqlConnection(Properties.Settings.Default.TEST4)))
            {
                cmdSQL.Connection.Open();
                SqlDataAdapter daupdate = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder cmdBuild = new SqlCommandBuilder(daupdate);
                daupdate.Update(rstPeople);
            }
            
        }
        void GridToTable()
        {
            foreach (ListViewItem rRow in ListView1.Items)
            {
                int PK = (int)ListView1.DataKeys[rRow.DataItemIndex]["ID"];
                DataRow OneDataRow = rstPeople.Rows.Find(PK);
                OneDataRow["FirstName"] = ((TextBox)rRow.FindControl("txtFirst")).Text;
                OneDataRow["LastName"] = ((TextBox)rRow.FindControl("txtLast")).Text;
                OneDataRow["City"] = ((DropDownList)rRow.FindControl("cboCity")).Text;
                OneDataRow["HotelName"] = ((TextBox)rRow.FindControl("txtHotel")).Text;
                OneDataRow["Description"] = ((TextBox)rRow.FindControl("txtDescription")).Text;
                OneDataRow["Active"] = ((CheckBox)rRow.FindControl("Active")).Checked;
                TextBox tDate = (TextBox)rRow.FindControl("txtBookingDate");
                if (tDate.Text == "")
                {
                    OneDataRow["BookingDate"] = DBNull.Value;
                }
                else
                {
                    DateTime BookingDate = DateTime.ParseExact(tDate.Text, "d", null);
                    OneDataRow["BookingDate"] = BookingDate;
                }
                FileUpload MyFile = (FileUpload)rRow.FindControl("FileUpLoad1");
                if (MyFile.HasFile)
                {
                    // save the file to the uploads folder
                    string sServerFile = Server.MapPath($@"~/UpLoadFiles/{MyFile.FileName}");
                    MyFile.SaveAs(sServerFile);
                    OneDataRow["UpLoadFile"] = MyFile.FileName;
                }
            }
        }
        protected void cmdAdd_Click1(object sender, EventArgs e)
        {
            // add a new row to the grid
            GridToTable(); // save any pending edits
            DataRow OneRow = rstPeople.Rows.Add();
            rstCity = General.MyRst("SELECT City FROM City ORDER BY City");
            ListView1.DataSource = rstPeople;
            ListView1.DataBind();
            ListViewItem  NewRow = (ListViewItem)ListView1.Items[ListView1.Items.Count - 1];
            TextBox txtFirst = (TextBox)NewRow.FindControl("txtFirst");
            txtFirst.Focus();
        }
        protected void cmdDelete_ServerClick(object sender, EventArgs e)
        {
            HtmlButton cmdDel = (HtmlButton)sender;
            ListViewItem lvRowToDel = (ListViewItem)cmdDel.NamingContainer;
            int PK = (int)ListView1.DataKeys[lvRowToDel.DataItemIndex]["ID"];
            rstPeople.Rows.Find(PK).Delete();
            ListView1.DataSource = rstPeople;
            ListView1.DataBind();   
        }
        protected void ListView1_ItemDataBound(object sender, ListViewItemEventArgs e)
        {
            if (e.Item.ItemType == ListViewItemType.DataItem)
            {
                DataRowView RowData = (DataRowView)e.Item.DataItem;
                DropDownList cboCity = (DropDownList)e.Item.FindControl("cboCity");
                cboCity.DataSource = rstCity;
                cboCity.DataBind();
                cboCity.Items.Insert(0, "Select City");
                if (RowData["City"] != null)
                {
                    cboCity.Text = RowData["City"].ToString();
                }
                FileUpload MyFileUpLoader = (FileUpload)e.Item.FindControl("FileUpLoad1");
                TextBox UpLoadFile = (TextBox)e.Item.FindControl("txtFileName");
                if (RowData["UpLoadFile"] == DBNull.Value)
                {
                    MyFileUpLoader.Style.Add("display", "inline");
                    UpLoadFile.Style.Add("display", "none");
                }
                else
                {
                    MyFileUpLoader.Style.Add("display", "none");
                    UpLoadFile.Style.Add("display", "inline");
                }
            }
        }

And now our result is this:

So, certainly some more love to this page should be added. I would for example add a "wait" spinner when save is pressed, since uploading of files could cause quite a noticeable delay, but such tweaks and polishing up can be added to the above proof of concept. I assume that save redirects to some other page after saving. I also currently do save the pending up-loads when you add a new row. This saved a lot of code issues. I suppose we could defer the uploading only on save button. But above is a great proof of concept here.

Answer 2

First up, I not sure I would spend the time and effort to have "some blank" row in the GridView.

Think of ANY system you EVER used.

The data and page tends to allow free editing, and then ONE save/submit button exists for the page.

And having a edit/save for each row is very "clumsy" or "clunky" from a user point of view.

So, in most cases, I will provide a edit button, but NOT attempt to edit the data in-line.

Hence, this UI I suggest:

However, if you REALLY do want row editing in a Grid?

Then I suggest dumping the Edit/Save for each row, and having ONE save button for all edits. The reason for this is your users will be "used" to this type of UI. When they edit excel sheets, they don't go edit/save for each row. When they shop on Amazon, they have a final save/submit for check out.

When users work with a desktop accounting package, they have ONE save button for the WHOLE invoice.

And to add a new row? Place a "Add new" button right after the table, so when they tab out of the row they just added, then the add button now has the focus, and they can hit that button again (by using the space bar). This means a user can add multiple rows WITHOUT having to use the mouse, and it becomes a 100% keyboard type of operation (nice, easy and VERY fast for data entry).

I tend to use a ListView for this type of UI. The main reason is the markup is cleaner then a Gridview, and your formatting and markup is more flexible.

So, then I suggest this UI:

So, note how there is no blank row, but the user is required to add the Add new button. However, we help the user, since when you hit Add, then we set the focus in the new row. And note how I am able to tab off the last row, and now focus is on the Add button - a tap of the space bar, and we adding a new row - all done without having to touch the mouse.

As noted, as markup becomes "more" complex, then I move from a GridView to a ListView. They both work similar, but the markup is cleaner for above.

More amazing, is there is not a lot of code for the above.

The markup is thus this:

                <asp:ListView ID="ListView1" runat="server" DataKeyNames="ID"  >
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:TextBox ID="txtFirst" runat="server" Text='<%# Eval("Firstname") %>' Width="90px"/> </td>
                            <td>
                                <asp:TextBox ID="txtLast" runat="server" Text='<%# Eval("LastName") %>' Width="90px" /></td>
                            <td>
                                <asp:TextBox ID="txtCity" runat="server" Text='<%# Eval("City") %>' Width="90px" /></td>
                            <td>
                                <asp:TextBox ID="txtHotel" runat="server" Text='<%# Eval("HotelName") %>'
                                    width="220px" /></td>
                            <td>
                                <asp:TextBox ID="txtDescription" runat="server" TextMode="MultiLine"
                                    width="270px" Rows="2"
                                    Text='<%# Eval("Description") %>' /></td>
                            <td style="text-align:center">
                                <asp:CheckBox ID="Active" runat="server" /></td>
                            <td>
                                <asp:TextBox ID="txtBookingDate" runat="server" 
                                    Text='<%# Eval("BookingDate","{0:d}") %>'
                                    TextMode="Date"
                                   /></td>
                            <td>
                                <button id="cmdDelete" runat="server" class="btn myshadow" style="margin-left:15px"
                                    onserverclick="cmdDelete_ServerClick"
                                    >
                                    <span aria-hidden="true" class="glyphicon glyphicon-trash"></span>
                                </button>

                            </td>
                        </tr>
                    </ItemTemplate>
                    <LayoutTemplate>
                        <table id="itemPlaceholderContainer" runat="server" border="0" 
                           style="width:100%" class="table table-hover table-bordered">
                            <tr runat="server">
                                <th  runat="server">First Name</th>
                                <th runat="server">Last Name</th>
                                <th runat="server">City</th>
                                <th runat="server">Hotel Name</th>
                                <th runat="server">Description</th>
                                <th runat="server">Active</th>
                                <th runat="server">Booking Date</th>
                                <th runat="server">Delete</th>
                            </tr>
                            <tr id="itemPlaceholder" runat="server">
                            </tr>
                        </table>
                    </LayoutTemplate>
                </asp:ListView>

            <button id="cmdAdd" runat="server" class="btn myshadow"
                onserverclick="cmdAdd_Click1">
                <span aria-hidden="true" class="glyphicon glyphicon glyphicon-share"> Add</span>
            </button>


            <button id="cmdSave" runat="server" class="btn myshadow" style="float:right"
                onserverclick="cmdSave_Click1">
                <span aria-hidden="true" class="glyphicon glyphicon-floppy-saved"> Save</span>
            </button>

And the code behind is quite clean also:

        private DataTable rstPeople = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadGrid();
                Session["MyTable"] = rstPeople;
            }
            else
                rstPeople = (DataTable)Session["MyTable"];

        }
        public void LoadGrid()
        {

            string strSQL =
                @"SELECT * FROM tblHotelsA ORDER BY HotelName";

            rstPeople = General.MyRst(strSQL);
            rstPeople.PrimaryKey = new DataColumn[] { rstPeople.Columns["ID"] };

            ListView1.DataSource = rstPeople;
            ListView1.DataBind();
        }



        protected void cmdSave_Click1(object sender, EventArgs e)

        {
            // pull grid rows back to table.
            GridToTable();
            
            // now send table back to database with updates

            string strSQL = "SELECT * FROM tblHotelsA WHERE ID = 0";

            using (SqlCommand cmdSQL = new SqlCommand(strSQL,
                                        new SqlConnection(Properties.Settings.Default.TEST4)))
            {
                cmdSQL.Connection.Open();
                SqlDataAdapter daupdate = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder cmdBuild = new SqlCommandBuilder(daupdate);

                daupdate.Update(rstPeople);
            }
        }

        void GridToTable()
        {
            foreach (ListViewItem rRow in ListView1.Items)
            {
                int PK = (int)ListView1.DataKeys[rRow.DataItemIndex]["ID"];
                DataRow OneDataRow = rstPeople.Rows.Find(PK);

                OneDataRow["FirstName"] = ((TextBox)rRow.FindControl("txtFirst")).Text;
                OneDataRow["LastName"] = ((TextBox)rRow.FindControl("txtLast")).Text;
                OneDataRow["City"] = ((TextBox)rRow.FindControl("txtCity")).Text;
                OneDataRow["HotelName"] = ((TextBox)rRow.FindControl("txtHotel")).Text;
                OneDataRow["Description"] = ((TextBox)rRow.FindControl("txtDescription")).Text;
                OneDataRow["Active"] = ((CheckBox)rRow.FindControl("Active")).Checked;
                TextBox tDate = (TextBox)rRow.FindControl("txtBookingDate");
                if (tDate.Text == "")
                {
                    OneDataRow["BookingDate"] = DBNull.Value;
                }
                else
                {
                    DateTime BookingDate = DateTime.ParseExact(tDate.Text, "d", null);
                    OneDataRow["BookingDate"] = BookingDate;
                }
            }
        }

        protected void cmdAdd_Click1(object sender, EventArgs e)
        {
            // add a new row to the grid

            GridToTable();

            DataRow OneRow = rstPeople.Rows.Add();
            ListView1.DataSource = rstPeople;
            ListView1.DataBind();

            ListViewItem  NewRow = (ListViewItem)ListView1.Items[ListView1.Items.Count - 1];
            TextBox txtFirst = (TextBox)NewRow.FindControl("txtFirst");

            txtFirst.Focus();


        }

        protected void cmdDelete_ServerClick(object sender, EventArgs e)
        {
            HtmlButton cmdDel = (HtmlButton)sender;
            ListViewItem lvRowToDel = (ListViewItem)cmdDel.NamingContainer;

            int PK = (int)ListView1.DataKeys[lvRowToDel.DataItemIndex]["ID"];
            rstPeople.Rows.Find(PK).Delete();

            ListView1.DataSource = rstPeople;
            ListView1.DataBind();   
        }
    }

The nice part is we have ONE save. And above would have a cancel button also. This allows the user to freely tab around, edit almost like Excel, but has ONE save button, and then all delete, all edits, and all new rows are saved with ONE command.

note also how I have a date popup. I did not require any 3rd party popup, since if you drop in a standard text box, and set the TextMode="date", then you have a built-in popup, and one that does not require bootstrap, or the AjaxToolKit popup. (so, less code, less messy markup).

So, all in all, the markup and the code to drive the whole above example is not a lot of code, but more important, there is no edit/save nag of the user for each row, and adding of rows, or editing of rows is simply that of the user typing in data. Remember, users don't think in terms of database operations, but only a page of data, and some submit button. And since my edit code, add code and even delete code operates against the data, then we write far less code, but more important is that code is easy on the eyes.