none
Bind datatable dynamically with gridview to perform insert,update,delete and last to have a column with total in C# asp.net

    Question

  • Greetings!!!

    Code in C# ASP.Net

    I have a gridview to display elements of datatable.

    In gridview i have tried to performed insert update and delete of datatable

    I have also tried to add a column in last row with footertemplate for total of a column called amount where I require sum of all the amounts in that column of a row.

    Lateron this datatable will be saved as table

     protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                InitalRow();
            }
        }
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (RadioButtonList1.SelectedValue == "Cash")
            {
                Panel1.Visible = true;
                Panel2.Visible = false;
                Panel3.Visible = true;
                
            }
            else if (RadioButtonList1.SelectedValue == "Credit")
            {
                Panel2.Visible = true;
                Panel1.Visible = false;
                Panel3.Visible = true;
                
            }
        }
        
        private void InitalRow()
        {
            DataTable dt1 = new DataTable();
            DataRow dr = null;
            dt1.Columns.Add(new DataColumn("RowNum", typeof(string)));
            dt1.Columns.Add(new DataColumn("Product", typeof(string)));
            dt1.Columns.Add(new DataColumn("Qnt", typeof(string)));
            dt1.Columns.Add(new DataColumn("Free", typeof(string)));
            dt1.Columns.Add(new DataColumn("Batch", typeof(string)));
            dt1.Columns.Add(new DataColumn("Expiry", typeof(string)));
            dt1.Columns.Add(new DataColumn("billrate", typeof(string)));
            dt1.Columns.Add(new DataColumn("CST", typeof(string)));
            dt1.Columns.Add(new DataColumn("Discnt", typeof(string)));
            dt1.Columns.Add(new DataColumn("Tax", typeof(string)));
            dt1.Columns.Add(new DataColumn("Purchase", typeof(string)));
            dt1.Columns.Add(new DataColumn("MRP", typeof(string)));
            dt1.Columns.Add(new DataColumn("Selling", typeof(string)));
            dt1.Columns.Add(new DataColumn("Shelf", typeof(string)));
            dt1.Columns.Add(new DataColumn("Amount", typeof(double)));
            dr = dt1.NewRow();
            dr["RowNum"] = 1;
            dr["Product"] = string.Empty;
            dr["Qnt"] = string.Empty;
            dr["Free"] = string.Empty;
            dr["Batch"] = string.Empty;
            dr["Expiry"] = string.Empty;
            dr["billrate"] = string.Empty;
            dr["CST"] = string.Empty;
            dr["Discnt"] = string.Empty;
            dr["Tax"] = string.Empty;
            dr["Purchase"] = string.Empty;
            dr["MRP"] = string.Empty;
            dr["Selling"] = string.Empty;
            dr["Shelf"] = string.Empty;
            dr["Amount"] = 0;
            dt1.Rows.Add(dr);
            ViewState["currtable"] = dt1;
            GridView1.DataSource = dt1;
            GridView1.DataBind();
        }
    
        private void Addnewrow()
        {
            int rowindex = 0;
            if (ViewState["currtable"] != null)
    
            {
                DataTable dt = (DataTable)ViewState["currtable"];
                DataRow dr = null;
                if (dt.Rows.Count > 0)
                {
                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        DropDownList dl = (DropDownList)GridView1.Rows[rowindex].Cells[1].FindControl("ddlprd");
                           TextBox t1 = (TextBox)GridView1.Rows[rowindex].Cells[2].FindControl("txtqnt");
                           TextBox t2 = (TextBox)GridView1.Rows[rowindex].Cells[3].FindControl("txtfree");
                           TextBox t3 = (TextBox)GridView1.Rows[rowindex].Cells[4].FindControl("txtbatch");
                           TextBox t4 = (TextBox)GridView1.Rows[rowindex].Cells[5].FindControl("txtexpiry");
                           TextBox t5 = (TextBox)GridView1.Rows[rowindex].Cells[6].FindControl("txtbillrate");
                           TextBox t6 = (TextBox)GridView1.Rows[rowindex].Cells[7].FindControl("txtcst");
                           TextBox t7 = (TextBox)GridView1.Rows[rowindex].Cells[8].FindControl("txtdiscount");
                           TextBox t8 = (TextBox)GridView1.Rows[rowindex].Cells[9].FindControl("txttax");
                           TextBox t9 = (TextBox)GridView1.Rows[rowindex].Cells[10].FindControl("txtpurchase");
                           TextBox t10 = (TextBox)GridView1.Rows[rowindex].Cells[11].FindControl("txtmrp");
                           TextBox t11 = (TextBox)GridView1.Rows[rowindex].Cells[12].FindControl("txtselling");
                           TextBox t12 = (TextBox)GridView1.Rows[rowindex].Cells[13].FindControl("txtshelf");
                        TextBox t13 = (TextBox)GridView1.Rows[rowindex].Cells[14].FindControl("txtamt");
                        dr = dt.NewRow();
                        dr["RowNum"] = i + 1;
                        dt.Rows[i - 1]["Product"] = dl.SelectedItem.Text;
                        dt.Rows[i - 1]["Qnt"] = t1.Text;
                        dt.Rows[i - 1]["Free"] = t2.Text;
                        dt.Rows[i - 1]["Batch"] = t3.Text;
                        dt.Rows[i - 1]["Expiry"] =t4.Text;
                        dt.Rows[i - 1]["billrate"] = t5.Text;
                        dt.Rows[i - 1]["CST"] = t6.Text;
                        dt.Rows[i - 1]["Discnt"] = t7.Text;
                        dt.Rows[i - 1]["Tax"] = t8.Text;
                        dt.Rows[i - 1]["Purchase"] = t9.Text;
                        dt.Rows[i - 1]["MRP"] = t10.Text;
                        dt.Rows[i - 1]["Selling"] = t11.Text;
                        dt.Rows[i - 1]["Shelf"] = t12.Text;
                        dt.Rows[i - 1]["Amount"] =Convert.ToDouble(t13.Text);
                        rowindex++;
                    }
                    dt.Rows.Add(dr);
                    ViewState["currtable"] = dt;
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
              
            }
                else
                {
                    Response.Redirect("Enter data");
                    }
            setpreviousdata();         
        }
        private void setpreviousdata()
        {
            double d = 0,total=0,tot=0;
            //string tot;
            int rowindex = 0;
            if (ViewState["currtable"] != null)
            {
                DataTable dt = (DataTable)ViewState["currtable"];
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < GridView1.Rows.Count; i++)
    			{
                    DropDownList dl = (DropDownList)GridView1.Rows[rowindex].Cells[1].FindControl("ddlprd");
                    TextBox t1 = (TextBox)GridView1.Rows[rowindex].Cells[2].FindControl("txtqnt");
                    TextBox t2 = (TextBox)GridView1.Rows[rowindex].Cells[3].FindControl("txtfree");
                    TextBox t3 = (TextBox)GridView1.Rows[rowindex].Cells[4].FindControl("txtbatch");
                    TextBox t4 = (TextBox)GridView1.Rows[rowindex].Cells[5].FindControl("txtexpiry");
                    TextBox t5 = (TextBox)GridView1.Rows[rowindex].Cells[6].FindControl("txtbillrate");
                    TextBox t6 = (TextBox)GridView1.Rows[rowindex].Cells[7].FindControl("txtcst");
                    TextBox t7 = (TextBox)GridView1.Rows[rowindex].Cells[8].FindControl("txtdiscount");
                    TextBox t8 = (TextBox)GridView1.Rows[rowindex].Cells[9].FindControl("txttax");
                    TextBox t9 = (TextBox)GridView1.Rows[rowindex].Cells[10].FindControl("txtpurchase");
                    TextBox t10 = (TextBox)GridView1.Rows[rowindex].Cells[11].FindControl("txtmrp");
                    TextBox t11 = (TextBox)GridView1.Rows[rowindex].Cells[12].FindControl("txtselling");
                    TextBox t12 = (TextBox)GridView1.Rows[rowindex].Cells[13].FindControl("txtshelf");
                    TextBox t13 = (TextBox)GridView1.Rows[rowindex].Cells[14].FindControl("txtamt");
    
                    dl.Text = dt.Rows[i]["Product"].ToString();
                    t1.Text = dt.Rows[i]["Qnt"].ToString();
                   t2.Text = dt.Rows[i]["Free"].ToString();
                    t3.Text = dt.Rows[i]["Batch"].ToString();
                    t4.Text = dt.Rows[i]["Expiry"].ToString();
                    t5.Text = dt.Rows[i]["billrate"].ToString();
                    t6.Text = dt.Rows[i]["CST"].ToString();
                    t7.Text = dt.Rows[i]["Discnt"].ToString();
                    t8.Text = dt.Rows[i]["Tax"].ToString();
                    t9.Text = dt.Rows[i]["Purchase"].ToString();
                    t10.Text = dt.Rows[i]["MRP"].ToString();
                    t11.Text = dt.Rows[i]["Selling"].ToString();
                    t12.Text = dt.Rows[i]["Shelf"].ToString();
                    t13.Text = dt.Rows[i]["Amount"].ToString();
                    
                    total = Convert.ToDouble(t13.Text);
                    d = d + total;
                    tot = d;
                    //rowindex++;
                    
                        
                    //d = Convert.ToDouble(GridView1.Rows[i].Cells[14].Text.ToString());
    
                    
    
    			}
                   // GridView1.FooterRow.Cells[1].Text = tot;
                }
            }
        }
    
        protected void btnadd_Click(object sender, EventArgs e)
        {
            Addnewrow();
            //CleartextBoxes(this);
        }
    
      
    
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            //if (GridView1.Rows.Count > 0)
            //{
            //    double tot = 0, amt = 0;
            //    for (int i = 0; i < GridView1.Rows.Count; i++)
            //    {
            //        TextBox t1 = (TextBox)GridView1.Rows[i].Cells[14].FindControl("txtamt");
            //        amt = Convert.ToDouble(t1.Text.ToString());
            //        tot += amt;
    
            //    }
            //    if (e.Row.RowType == DataControlRowType.Footer)
            //    {
            //        TextBox total = (TextBox)e.Row.FindControl("txttotal");
            //        total.Text = tot.ToString();
            //        ((TextBox)GridView1.FindControl("txttotal")).Text = total.Text.ToString();
            //    }
            //}
            //else
            //{
            //    //double d1=0;
            //    //((TextBox)GridView1.FindControl("txttotal")).Text = d1.ToString();
            //}
            //double total=0;
            //if (e.Row.RowType == DataControlRowType.DataRow)
            //{
            //    double amt = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem,"Amt"));
            //    total = total + amt;
            //}
            //if (e.Row.RowType == DataControlRowType.Footer)
            //{
            //    TextBox tot = (TextBox)e.Row.FindControl("txttotal");
            //    tot.Text
            //}
        }
     
       
        protected void ddlprd_SelectedIndexChanged(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
                SqlConnection con = new SqlConnection(constr);
                int rowindex=0;
                DropDownList dlprd=(DropDownList)GridView1.Rows[rowindex].Cells[1].FindControl("ddlprd");
                if (dlprd.SelectedIndex > 0)
                {
                    //con.Open();
                    string str = dlprd.SelectedItem.Text.ToString();
              
                    SqlDataAdapter da = new SqlDataAdapter("select shelf_no from Product_details where prd_name='" + str+"'", con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    TextBox t1 = (TextBox)GridView1.Rows[rowindex].Cells[13].FindControl("txtshelf");
                    t1.Text = dt.Rows[0].ItemArray[0].ToString();
                    GridView1.Rows[rowindex].Cells[13].Text = t1.Text.ToString();
                    con.Close();
                }
                
        }
        protected void btnsave_Click(object sender, EventArgs e)
        {
            string voucher = string.Empty;
            string srno = string.Empty;
            string product = string.Empty;
            string qnt = string.Empty;
            string free = string.Empty;
            string batch = string.Empty;
            string expiry = string.Empty;
            string bill = string.Empty;
            string cst = string.Empty;
            string dis = string.Empty;
            string tax = string.Empty;
            string purchase = string.Empty;
            string mrp = string.Empty;
            string sell = string.Empty;
            string shelf = string.Empty;
            string amt = string.Empty;
    
            int gvrow = GridView1.Rows.Count;
    
            string constr = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
            SqlConnection con = new SqlConnection(constr);
              string add;
            //con.Open();
            foreach (GridViewRow grcolumn in GridView1.Rows)
            {
                voucher = txtvou_no.Text;
                srno = grcolumn.Cells[1].Text;
                product = grcolumn.Cells[2].Text;
                qnt = grcolumn.Cells[3].Text;
                free = grcolumn.Cells[4].Text;
                batch = grcolumn.Cells[5].Text;
                expiry = grcolumn.Cells[6].Text;
                bill = grcolumn.Cells[7].Text;
                cst = grcolumn.Cells[8].Text;
                dis = grcolumn.Cells[9].Text;
                tax = grcolumn.Cells[10].Text;
                purchase = grcolumn.Cells[11].Text;
                mrp = grcolumn.Cells[12].Text;
                sell = grcolumn.Cells[13].Text;
                shelf = grcolumn.Cells[14].Text;
                amt = grcolumn.Cells[15].Text;
                add = "insert into purchase_detail values(@voucher,@sr,@prd,@qnt,@free,@batch,@expiry,@bill,@cst,@dis,@tax,@pur,@mrp,@sell,@shelf,@amt)";
                SqlCommand cmd = new SqlCommand(add, con);
                con.Open();
                cmd.Parameters.Add("@voucher", SqlDbType.BigInt).Value=Convert.ToInt64(voucher);
                cmd.Parameters.Add("@sr", SqlDbType.Int).Value=Convert.ToInt32(srno);
                cmd.Parameters.Add("@prd", SqlDbType.VarChar).Value=product;
                cmd.Parameters.Add("@qnt", SqlDbType.Int).Value=Convert.ToInt32(qnt);
                cmd.Parameters.Add("@free", SqlDbType.Int).Value=Convert.ToInt32(free);
                cmd.Parameters.Add("@batch", SqlDbType.VarChar).Value=batch;
                cmd.Parameters.Add("@expiry", SqlDbType.Date).Value=Convert.ToDateTime(expiry);
                cmd.Parameters.Add("@bill", SqlDbType.Decimal).Value=Convert.ToDouble(bill);
                cmd.Parameters.Add("@cst", SqlDbType.Decimal).Value=Convert.ToDouble(cst);
                cmd.Parameters.Add("@dis", SqlDbType.Decimal).Value=Convert.ToDouble(dis);
                cmd.Parameters.Add("@tax", SqlDbType.Decimal).Value=Convert.ToDouble(tax);
                cmd.Parameters.Add("@pur", SqlDbType.Decimal).Value=Convert.ToDouble(purchase);
                cmd.Parameters.Add("@mrp", SqlDbType.Decimal).Value=Convert.ToDouble(mrp);
                cmd.Parameters.Add("@sell", SqlDbType.Decimal).Value=Convert.ToDouble(sell);
                cmd.Parameters.Add("@shelf", SqlDbType.VarChar).Value=shelf;
                cmd.Parameters.Add("@amt", SqlDbType.Decimal).Value=Convert.ToDouble(amt);
                cmd.ExecuteNonQuery();
                con.Close();
            }
     <asp:GridView ID="GridView1" runat="server" ShowFooter="true" 
            AutoGenerateColumns="false" Font-Names="Verdana" Font-Size="Small" 
            onrowdatabound="GridView1_RowDataBound"
            >
                                <Columns>
                                <asp:BoundField DataField="RowNum" HeaderText="Sr No" />
                                <asp:TemplateField HeaderText="Product">
                                <ItemTemplate>
                                <asp:DropDownList ID="ddlprd" runat="server" AutoPostBack="True" 
                        DataSourceID="SqlDataSource5" DataTextField="prd_name" 
                        DataValueField="prd_name" OnSelectedIndexChanged="ddlprd_SelectedIndexChanged">
                    </asp:DropDownList>
                                </ItemTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Quantity">
                                <ItemTemplate>
                                 <asp:TextBox ID="txtqnt" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
                                
                                <asp:TemplateField HeaderText="Free">
                                <ItemTemplate>
                                <asp:TextBox ID="txtfree" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
                                
                                <asp:TemplateField HeaderText=" Batch No">
                                <ItemTemplate>
                               <asp:TextBox ID="txtbatch" runat="server" Width="90px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Expiry ">
                                <ItemTemplate>
                                  <asp:TextBox ID="txtexpiry" runat="server" Width="40px"></asp:TextBox>
                                     <asp:ImageButton ID="imgPopup" ImageUrl="~/Images/calendar.png" ImageAlign="Bottom"
            runat="server" CausesValidation="False" />
        <cc1:CalendarExtender ID="Calendar1" PopupButtonID="imgPopup" runat="server" TargetControlID="txtexpiry"
            Format="dd/MM/yyyy"/>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Bill Rate">
                                <ItemTemplate>
                                <asp:TextBox ID="txtbillrate" runat="server" Width="40px" 
                             AutoPostBack="true"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
    
                                 <asp:TemplateField HeaderText=" CST(Rs)">
                                <ItemTemplate>
                                <asp:TextBox ID="txtcst" runat="server" AutoPostBack="true" Width="40px">
                        </asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Discount ">
                                <ItemTemplate>
                                  <asp:TextBox ID="txtdiscount" runat="server" Width="40px" 
                             AutoPostBack="true"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Tax ">
                                <ItemTemplate>
                               <asp:TextBox ID="txttax" runat="server" AutoPostBack="true" Width="40px">
                        </asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                 <asp:TemplateField HeaderText="Purchase rate">
                                <ItemTemplate>
                                <asp:TextBox ID="txtpurchase" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                 <asp:TemplateField HeaderText=" MRP ">
                                <ItemTemplate>
                               <asp:TextBox ID="txtmrp" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                 <asp:TemplateField HeaderText="Selling Rate">
                                <ItemTemplate>
                                <asp:TextBox ID="txtselling" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Shelf Number">
                                <ItemTemplate>
                                <asp:TextBox ID="txtshelf" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
                                <FooterTemplate>
                                <asp:Label ID="totallbl" runat="server" Text='<%# Eval("tot") %>' Width="40px" />
                                </FooterTemplate>
                                </asp:TemplateField>
    
                                <asp:TemplateField HeaderText="Amount">
                                <ItemTemplate>
                                <asp:TextBox ID="txtamt" runat="server" Width="40px"></asp:TextBox>
                                </ItemTemplate>
    
                                <FooterStyle HorizontalAlign="Right" />
                                <FooterTemplate>
                                
                                
                                <asp:Button ID="btnadd" runat="server" Text="Add" OnClick="btnadd_Click"/>
                                </FooterTemplate>
                                </asp:TemplateField>
                                
                                
                                
                                </Columns>
    
                                </asp:GridView>

            Label1.Text = "Data save successfully";
        }
    }
    
    

    Pls help out with some examples 


    Yagnesh Panchal


    • Edited by Yagnesh Panchal Tuesday, January 14, 2014 6:31 PM To code I have used
    Tuesday, January 14, 2014 6:27 PM