Insert (Save) updated GridView row in database using C# and VB.Net in ASP.Net

Hi akhter,

Check this example. Now please take its reference and correct your code.

HTML

<form id="form1" runat="server">
    <asp:Label ID="lblast" runat="server" Text="1" />
    <asp:GridView ID="GVFood" Width="97%" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" ShowFooter="true"
        RowStyle-CssClass="rows" runat="server" AutoGenerateColumns="false"
        OnRowDataBound="GVFood_RowDataBound" Height="216px" CssClass="auto-style15" ClientIDMode="Static">
        <Columns>
            <asp:TemplateField HeaderText="S.No">
                <ItemTemplate>
                    <%#Container.DataItemIndex+1 %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="B_ID" HeaderText="Codeitem" Visible="false" />
            <asp:BoundField DataField="B_Name" HeaderText="Descriptionitem" ReadOnly="true" />
            <%--<asp:BoundField DataField="QTY" HeaderText="QTY" onkeyup="CalculateTotals();" ControlStyle-Width="20px"  />--%>
            <asp:TemplateField HeaderText="Quantity">
                <ItemTemplate>
                    <asp:TextBox ID="QTY" Text='<%#Eval("QTY") %>' Enabled="true" AutoPostBack="True"
                        OnTextChanged="QTY_TextChanged" runat="server" ControlStyle-Width="50px"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Price">
                <ItemTemplate>
                    <asp:TextBox ID="Rate" runat="server" Width="50px" Enabled="true" AutoPostBack="True"
                        OnTextChanged="Rate_TextChanged" Text='<%#Eval("Rate") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Dis">
                <ItemTemplate>
                    <asp:TextBox ID="txtdisc" runat="server" Enabled="true" AutoPostBack="True"
                        OnTextChanged="txtdisc_TextChanged" Width="50px" Text='<%#Eval("txtdisc") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="S.Total">
                <ItemTemplate>
                    <asp:Label ID="lbltotalDisc" runat="server" Text=""></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Total">
                <ItemTemplate>
                    <asp:Label ID="lbltotal" runat="server" Text='<%# Eval("lbltotal") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:Label ID="lblGrandTotal" runat="server"></asp:Label>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowDeleteButton="True" ButtonType="Link" />
        </Columns>
    </asp:GridView>
    <asp:Button Text="Bind" runat="server" OnClick="B_Nine_Click" />
    <asp:Button Text="Save" runat="server" OnClick="Save" />
</form>

Code

C#

protected void Save(object sender, EventArgs e)
{
    DataTable dt = (DataTable)ViewState["dt"];
    string B_ID, B_QTY, Rate, tDisc;
    int ResultValue = 0;
    foreach (DataRow row in dt.Rows)
    {
        B_ID = row["B_ID"].ToString();
        B_QTY = row["QTY"].ToString();
        Rate = row["rate"].ToString();
        tDisc = row["txtDisc"].ToString();
        ResultValue += this.InsertRows(lblast.Text, B_ID, B_QTY, Rate, tDisc);
    }

    if (ResultValue == dt.Rows.Count)
    {
        string Url = "PVRosel.aspx?ID=" + lblast.Text;
        Response.Write("<script language='javascript'>window.open('" + Url + "','_blank','');");
        Response.Write("</script>");
        string message = "Saved successfully.";
        string script = "window.onload = function(){ alert('";
        script += message;
        script += "');";
        script += "window.location = '";
        script += Request.Url.AbsoluteUri;
        script += "'; }";
        ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
    }
}

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
private int InsertRows(string last, string B_Id, string B_QTy, string B_rate, string tDisc)
{
    using (SqlCommand cmd = new SqlCommand("[Sp_Insert_Sell_D]", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Sell_ID", last);
        cmd.Parameters.AddWithValue("@B_ID", B_Id);
        cmd.Parameters.AddWithValue("@B_qty", B_QTy);
        cmd.Parameters.AddWithValue("@B_rate", B_rate);
        cmd.Parameters.AddWithValue("@Desc", tDisc);
        con.Open();
        int ResultValue = cmd.ExecuteNonQuery();
        con.Close();
        return ResultValue;
    }
}

protected void QTY_TextChanged(object sender, EventArgs e)
{
    GridViewRow row = (sender as TextBox).NamingContainer as GridViewRow;
    string unitprice = (row.FindControl("Rate") as TextBox).Text;
    string qty = (row.FindControl("QTY") as TextBox).Text;
    string disc = (row.FindControl("txtdisc") as TextBox).Text;
    DataTable dt = ViewState["dt"] as DataTable;
    DataRow dr = dt.Rows[row.RowIndex];
    dr["QTY"] = qty;
    dr["Rate"] = unitprice;
    dr["txtDisc"] = disc;
    dt.AcceptChanges();
    ViewState["dt"] = dt;
    BindGridView();
}

protected void Rate_TextChanged(object sender, EventArgs e)
{
    GridViewRow row = (sender as TextBox).NamingContainer as GridViewRow;
    string unitprice = (row.FindControl("Rate") as TextBox).Text;
    string qty = (row.FindControl("QTY") as TextBox).Text;
    string disc = (row.FindControl("txtdisc") as TextBox).Text;
    DataTable dt = ViewState["dt"] as DataTable;
    DataRow dr = dt.Rows[row.RowIndex];
    dr["QTY"] = qty;
    dr["Rate"] = unitprice;
    dr["txtDisc"] = disc;
    dt.AcceptChanges();
    ViewState["dt"] = dt;
    BindGridView();
}

protected void txtdisc_TextChanged(object sender, EventArgs e)
{
    GridViewRow row = (sender as TextBox).NamingContainer as GridViewRow;
    string unitprice = (row.FindControl("Rate") as TextBox).Text;
    string qty = (row.FindControl("QTY") as TextBox).Text;
    string disc = (row.FindControl("txtdisc") as TextBox).Text;
    DataTable dt = ViewState["dt"] as DataTable;
    DataRow dr = dt.Rows[row.RowIndex];
    dr["QTY"] = qty;
    dr["Rate"] = unitprice;
    dr["txtDisc"] = disc;
    dt.AcceptChanges();
    ViewState["dt"] = dt;
    BindGridView();
}

decimal sum = 0;
protected void GVFood_RowDataBound(object sender, GridViewRowEventArgs e)
{
    try
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            GridViewRow row = e.Row;
            string unitprice = (row.FindControl("rate") as TextBox).Text;
            string qty = (row.FindControl("qty") as TextBox).Text;
            string disc = (row.FindControl("txtDisc") as TextBox).Text;
            Label totaldisc = row.FindControl("lbltotalDisc") as Label;
            Label total = row.FindControl("lbltotal") as Label;
            if ((!string.IsNullOrEmpty(unitprice)) && (!string.IsNullOrEmpty(qty)) && (!string.IsNullOrEmpty(disc)))
            {
                sum = Convert.ToDecimal(unitprice) * Convert.ToInt32(qty);
                totaldisc.Text = sum.ToString();
                sum = Convert.ToDecimal(unitprice) * Convert.ToInt32(qty) - Convert.ToDecimal(disc);
                total.Text = sum.ToString();
            }
        }
    }
    catch (Exception ex)
    {
        Response.Write("Error: " + ex.Message);
    }
}

protected void B_Nine_Click(object sender, EventArgs e)
{
    bool ifExist = false;
    int codeitem = 1;
    string item = "12 liter";
    int quantity = 2;
    decimal price = 50;
    decimal txtDisc = 10;

    DataTable dt;
    if (ViewState["dt"] == null)
    {
        dt = new DataTable();
        dt.Columns.Add("B_ID");
        dt.Columns.Add("B_Name");
        dt.Columns.Add("QTY");
        dt.Columns.Add("Rate");
        dt.Columns.Add("txtDisc");
        dt.Columns.Add("lbltotal");
        dt.Rows.Add(codeitem, item, quantity, price, txtDisc);
        ViewState["dt"] = dt;
    }
    else
    {
        dt = ViewState["dt"] as DataTable;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i]["B_Name"].ToString().ToLower() == item.ToLower())
            {
                dt.Rows[i]["QTY"] = Convert.ToInt16(dt.Rows[i]["QTY"]) + quantity;
                ifExist = true;
                break;
            }
        }
        if (!ifExist)
        {
            dt.Rows.Add(codeitem, item, quantity, price, txtDisc);
        }
        ViewState["dt"] = dt;
    }
    BindGridView();
}

private void BindGridView()
{
    if (ViewState["dt"] != null)
    {
        GVFood.DataSource = ViewState["dt"] as DataTable;
        GVFood.DataBind();
    }
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = CType(ViewState("dt"), DataTable)
    Dim B_ID, B_QTY, Rate, tDisc As String
    Dim ResultValue As Integer = 0
    For Each row As DataRow In dt.Rows
        B_ID = row("B_ID").ToString()
        B_QTY = row("QTY").ToString()
        Rate = row("rate").ToString()
        tDisc = row("txtDisc").ToString()
        ResultValue += Me.InsertRows(lblast.Text, B_ID, B_QTY, Rate, tDisc)
    Next

    If ResultValue = dt.Rows.Count Then
        Dim Url As String = "PVRosel.aspx?ID=" & lblast.Text
        Response.Write("<script language='javascript'>window.open('" & Url & "','_blank','');")
        Response.Write("</script>")
        Dim message As String = "Saved successfully."
        Dim script As String = "window.onload = function(){ alert('"
        script += message
        script += "');"
        script += "window.location = '"
        script += Request.Url.AbsoluteUri
        script += "'; }"
        ClientScript.RegisterStartupScript(Me.[GetType](), "SuccessMessage", script, True)
    End If
End Sub

Private con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString").ConnectionString)
Private Function InsertRows(ByVal last As String, ByVal B_Id As String, ByVal B_QTy As String, ByVal B_rate As String, ByVal tDisc As String) As Integer
    Using cmd As SqlCommand = New SqlCommand("[Sp_Insert_Sell_D]", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Sell_ID", last)
        cmd.Parameters.AddWithValue("@B_ID", B_Id)
        cmd.Parameters.AddWithValue("@B_qty", B_QTy)
        cmd.Parameters.AddWithValue("@B_rate", B_rate)
        cmd.Parameters.AddWithValue("@Desc", tDisc)
        con.Open()
        Dim ResultValue As Integer = cmd.ExecuteNonQuery()
        con.Close()

        Return ResultValue
    End Using
End Function

Protected Sub QTY_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, TextBox)).NamingContainer, GridViewRow)
    Dim unitprice As String = (TryCast(row.FindControl("Rate"), TextBox)).Text
    Dim qty As String = (TryCast(row.FindControl("QTY"), TextBox)).Text
    Dim disc As String = (TryCast(row.FindControl("txtdisc"), TextBox)).Text
    Dim dt As DataTable = TryCast(ViewState("dt"), DataTable)
    Dim dr As DataRow = dt.Rows(row.RowIndex)
    dr("QTY") = qty
    dr("Rate") = unitprice
    dr("txtDisc") = disc
    dt.AcceptChanges()
    ViewState("dt") = dt
    BindGridView()
End Sub

Protected Sub Rate_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, TextBox)).NamingContainer, GridViewRow)
    Dim unitprice As String = (TryCast(row.FindControl("Rate"), TextBox)).Text
    Dim qty As String = (TryCast(row.FindControl("QTY"), TextBox)).Text
    Dim disc As String = (TryCast(row.FindControl("txtdisc"), TextBox)).Text
    Dim dt As DataTable = TryCast(ViewState("dt"), DataTable)
    Dim dr As DataRow = dt.Rows(row.RowIndex)
    dr("QTY") = qty
    dr("Rate") = unitprice
    dr("txtDisc") = disc
    dt.AcceptChanges()
    ViewState("dt") = dt
    BindGridView()
End Sub

Protected Sub txtdisc_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast((TryCast(sender, TextBox)).NamingContainer, GridViewRow)
    Dim unitprice As String = (TryCast(row.FindControl("Rate"), TextBox)).Text
    Dim qty As String = (TryCast(row.FindControl("QTY"), TextBox)).Text
    Dim disc As String = (TryCast(row.FindControl("txtdisc"), TextBox)).Text
    Dim dt As DataTable = TryCast(ViewState("dt"), DataTable)
    Dim dr As DataRow = dt.Rows(row.RowIndex)
    dr("QTY") = qty
    dr("Rate") = unitprice
    dr("txtDisc") = disc
    dt.AcceptChanges()
    ViewState("dt") = dt
    BindGridView()
End Sub

Private sum As Decimal = 0

Protected Sub GVFood_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    Try
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim row As GridViewRow = e.Row
            Dim unitprice As String = (TryCast(row.FindControl("rate"), TextBox)).Text
            Dim qty As String = (TryCast(row.FindControl("qty"), TextBox)).Text
            Dim disc As String = (TryCast(row.FindControl("txtDisc"), TextBox)).Text
            Dim totaldisc As Label = TryCast(row.FindControl("lbltotalDisc"), Label)
            Dim total As Label = TryCast(row.FindControl("lbltotal"), Label)
            If (Not String.IsNullOrEmpty(unitprice)) AndAlso (Not String.IsNullOrEmpty(qty)) AndAlso (Not String.IsNullOrEmpty(disc)) Then
                sum = Convert.ToDecimal(unitprice) * Convert.ToInt32(qty)
                totaldisc.Text = sum.ToString()
                sum = Convert.ToDecimal(unitprice) * Convert.ToInt32(qty) - Convert.ToDecimal(disc)
                total.Text = sum.ToString()
            End If
        End If
    Catch ex As Exception
        Response.Write("Error: " & ex.Message)
    End Try
End Sub

Protected Sub B_Nine_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim ifExist As Boolean = False
    Dim codeitem As Integer = 1
    Dim item As String = "12 liter"
    Dim quantity As Integer = 2
    Dim price As Decimal = 50
    Dim txtDisc As Decimal = 10
    Dim dt As DataTable

    If ViewState("dt") Is Nothing Then
        dt = New DataTable()
        dt.Columns.Add("B_ID")
        dt.Columns.Add("B_Name")
        dt.Columns.Add("QTY")
        dt.Columns.Add("Rate")
        dt.Columns.Add("txtDisc")
        dt.Columns.Add("lbltotal")
        dt.Rows.Add(codeitem, item, quantity, price, txtDisc)
        ViewState("dt") = dt
    Else
        dt = TryCast(ViewState("dt"), DataTable)
        For i As Integer = 0 To dt.Rows.Count - 1
            If dt.Rows(i)("B_Name").ToString().ToLower() = item.ToLower() Then
                dt.Rows(i)("QTY") = Convert.ToInt16(dt.Rows(i)("QTY")) + quantity
                ifExist = True
                Exit For
            End If
        Next

        If Not ifExist Then
            dt.Rows.Add(codeitem, item, quantity, price, txtDisc)
        End If

        ViewState("dt") = dt
    End If

    BindGridView()
End Sub

Private Sub BindGridView()
    If ViewState("dt") IsNot Nothing Then
        GVFood.DataSource = TryCast(ViewState("dt"), DataTable)
        GVFood.DataBind()
    End If
End Sub

Screenshot

Note: Change the GridBinding code with your database code. I have hard coded the values for the example.