Update multiple GridView rows in database using Linq and Entity Framework in ASP.Net

johnnybuns
 
on Nov 29, 2019 05:11 PM
Sample_139630.zip
1441 Views

How to update multiple rows in database using Linq ASP.Net

<div class="row">
    <div class="col-md-9">
        <asp:Label ID="Label1" runat="server" Text="Order Number"></asp:Label>&nbsp;&nbsp;
        <asp:DropDownList ID="OrderDLL" runat="server" Width="100px" DataSourceID="OrderDLLODS"
            DataTextField="OrderID" DataValueField="OrderID">
        </asp:DropDownList>
        &nbsp;&nbsp;
        <asp:Button ID="OrderFetch" runat="server" Text="Fetch" OnClick="OrderFetch_Click" />&nbsp;&nbsp;
        <asp:ListView ID="CustomerInfo" runat="server" DataSourceID="CustomerDLLODS">
            <ItemTemplate>
                <asp:Label ID="Customer" runat="server" Text="Customer: "></asp:Label>&nbsp;&nbsp;
                <asp:Label ID="TracksBy" runat="server" Text='<%# Eval("LastName")+", "+Eval("FirstName") %>'></asp:Label>&nbsp;&nbsp;
                <asp:Label ID="Contact" runat="server" Text="Contact: "></asp:Label>&nbsp;&nbsp;
                <asp:Label ID="SearchArg" runat="server" Text='<%# Eval("Phone") %>'></asp:Label>&nbsp;&nbsp;&nbsp;&nbsp;
            </ItemTemplate>
        </asp:ListView>
        <asp:Label ID="Label3" runat="server" Text="Picker"></asp:Label>&nbsp;&nbsp;
        <asp:DropDownList ID="PickerDLL" runat="server" Width="150px" DataSourceID="PickerDLLODS"
            DataTextField="DisplayText" DataValueField="IDValueField">
        </asp:DropDownList>
    </div>
</div>
<asp:GridView ID="OrderList" runat="server" AutoGenerateColumns="False" GridLines="Horizontal"
    CssClass="table">
    <Columns>
        <asp:TemplateField HeaderText="Product">
            <ItemTemplate>
                <asp:Label runat="server" ID="Product" Width="180px" Text='<%# Eval("Product") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Qty">
            <ItemTemplate>
                <asp:Label runat="server" ID="QtyOrdered" Width="40px" Text='<%# Eval("QtyOrdered") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Comment">
            <ItemTemplate>
                <asp:Label ID="CustomerComment" runat="server" Text='<%# Eval("CustomerComment") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Picked">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="QtyPicked" Width="60px" Text='<%# Bind("QtyPicked") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Pick Issue">
            <ItemTemplate>
                <asp:TextBox ID="PickIssue" runat="server" Width="100px" Text='<%# Bind("PickIssue") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <EmptyDataTemplate>
        No data to view for the Active Order.
    </EmptyDataTemplate>
</asp:GridView>
<asp:Button ID="Save" runat="server" Text="Save" Width="200px" OnClick="Save_Click" />
<asp:ObjectDataSource ID="OrderDLLODS" runat="server" OldValuesParameterFormatString="original_{0}"
    SelectMethod="Orders_UnDelivedList" TypeName="GroceryListSystem.BLL.OrdersController">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="CustomerDLLODS" runat="server" OldValuesParameterFormatString="original_{0}"
    SelectMethod="Customer_Get" TypeName="GroceryListSystem.BLL.OrderListsController">
    <SelectParameters>
        <asp:ControlParameter ControlID="OrderDLL" PropertyName="SelectedValue" Name="CustomerID"
            Type="int32"></asp:ControlParameter>
    </SelectParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="PickerDLLODS" runat="server" OldValuesParameterFormatString="original_{0}"
    SelectMethod="Pickers_List" TypeName="GroceryListSystem.BLL.PickersController">
</asp:ObjectDataSource>

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Dec 04, 2019 05:03 AM

Hi johnnybuns,

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

Database

CREATE TABLE ProductDetail
(
	Id INT IDENTITY PRIMARY KEY,
	Product VARCHAR(50),
	QtyOrdered INT,
	CustomerComment VARCHAR(500),
	QtyPicked INT,
	PickIssue VARCHAR(50),
)


INSERT INTO ProductDetail VALUES('Product 1', 10, 'Comment 1', 0, '')
INSERT INTO ProductDetail VALUES('Product 2', 10, 'Comment 2', 0, '')
INSERT INTO ProductDetail VALUES('Product 3', 10, 'Comment 3', 0, '')
INSERT INTO ProductDetail VALUES('Product 4', 10, 'Comment 4', 0, '')

HTML

<asp:GridView ID="OrderList" runat="server" AutoGenerateColumns="False" GridLines="Horizontal"
    CssClass="table">
    <Columns>
        <asp:TemplateField HeaderText="Product">
            <ItemTemplate>
                <asp:Label runat="server" ID="Product" Width="180px" Text='<%# Eval("Product") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Qty">
            <ItemTemplate>
                <asp:Label runat="server" ID="QtyOrdered" Width="40px" Text='<%# Eval("QtyOrdered") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Comment">
            <ItemTemplate>
                <asp:Label ID="CustomerComment" runat="server" Text='<%# Eval("CustomerComment") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Picked">
            <ItemTemplate>
                <asp:TextBox runat="server" ID="QtyPicked" Width="60px" Text='<%# Bind("QtyPicked") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Pick Issue">
            <ItemTemplate>
                <asp:TextBox ID="PickIssue" runat="server" Width="100px" Text='<%# Bind("PickIssue") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <EmptyDataTemplate>
        No data to view for the Active Order.
    </EmptyDataTemplate>
</asp:GridView>
<asp:Button ID="Save" runat="server" Text="Save" Width="200px" OnClick="Save_Click" />

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        TestEntities entities = new TestEntities();
        OrderList.DataSource = entities.ProductDetails.ToList();
        OrderList.DataBind();
    }
}

protected void Save_Click(object sender, EventArgs e)
{
    TestEntities entities = new TestEntities();
    foreach (GridViewRow row in OrderList.Rows)
    {
        string product = (row.FindControl("Product") as Label).Text.Trim();
        int qtyPicked = Convert.ToInt32((row.FindControl("QtyPicked") as TextBox).Text.Trim());
        string pickIssue = (row.FindControl("PickIssue") as TextBox).Text.Trim();
        ProductDetail detail = entities.ProductDetails.Where(x => x.Product == product).FirstOrDefault();
        detail.QtyPicked = qtyPicked;
        detail.PickIssue = pickIssue;
        entities.SaveChanges();
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim entities As TestEntities = New TestEntities()
        OrderList.DataSource = entities.ProductDetails.ToList()
        OrderList.DataBind()
    End If
End Sub

Protected Sub Save_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim entities As TestEntities = New TestEntities()
    For Each row As GridViewRow In OrderList.Rows
        Dim product As String = (TryCast(row.FindControl("Product"), Label)).Text.Trim()
        Dim qtyPicked As Integer = Convert.ToInt32((TryCast(row.FindControl("QtyPicked"), TextBox)).Text.Trim())
        Dim pickIssue As String = (TryCast(row.FindControl("PickIssue"), TextBox)).Text.Trim()
        Dim detail As ProductDetail = entities.ProductDetails.Where(Function(x) x.Product = product).FirstOrDefault()
        detail.QtyPicked = qtyPicked
        detail.PickIssue = pickIssue
        entities.SaveChanges()
    Next
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Screenshots

Database Record

The Form

Database Record after update