How to update two tables from GridView update command in ASP.NET?

Answer 1

Many times you need to display data on a GridView control from two database tables by joining these tables. When you have displayed data from two tables, you may need to update these two tables by using update command of GridView control. In this article, I will get data from publishers and pub_info tables of PUBS database and display it on GridView control. I will update values of these two tables using update command of GridView control. In update command of GridView control, I will use a stored procedure to update values.

Let’s start our example.

1.Open Visual Studio 2010
2.File > New > Web Site
3.Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
4.Website > Add New Item > Web Form > Click Add
5.Write a stored Procedure in PUBS database to use for this example

CREATE PROCEDURE dbo.UpdateTwoTables
       (
       @pub_id char(4),
       @pub_name varchar(40),
       @city varchar(20),
       @state char(2),
       @country varchar(30),
       @pr_info text
       )
AS
       BEGIN

       UPDATE publishers SET pub_name = @pub_name, city = @city, state  = @state WHERE pub_id = @pub_id
       Update pub_info SET pr_info = pr_info WHERE pub_id = @pub_id

       END
       RETURN

6.Now Drag and Drop a “GridView” in Default aspx page.
7.Click on small arrow at the top right corner of the GridView
8.Choose Data Source and Select New Data Source
9.Select Database and Click Ok
10.Select New Connection and provide detail for Server Name and Database. I have used PUBS database for this example. You have to download PUBS database and to use for this example. Click Ok to proceed
11.Save the connection string and click Next
12.Select “Specify a custom SQL statement or stored procedure” and click Next
13.Select “SQL statement” for SELECT tab and write below select statement

SELECT publishers.pub_id, publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pr_info FROM publishers INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id

14.Select stored procedure for UPDATE tab and select “UpdateTwoTables” stored procedure.
15.Click Next and then Click Finish.
16.Enable Editing for GridView control.
17.Now you can see code below in Default.aspx page. you can also copy and paste code below in your aspx file

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="pub_id" DataSourceID="SqlDataSource1">
    <Columns>
        <asp:CommandField ShowEditButton="True" />
        <asp:BoundField DataField="pub_id" HeaderText="pub_id" ReadOnly="True"
            SortExpression="pub_id" />
        <asp:BoundField DataField="pub_name" HeaderText="pub_name"
            SortExpression="pub_name" />
        <asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
        <asp:BoundField DataField="state" HeaderText="state" SortExpression="state" />
        <asp:BoundField DataField="country" HeaderText="country"
            SortExpression="country" />
        <asp:BoundField DataField="pr_info" HeaderText="pr_info"
            SortExpression="pr_info" />
        </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:PUBSConnectionString %>" SelectCommand="SELECT publishers.pub_id, publishers.pub_name, publishers.city, publishers.state, publishers.country, pub_info.pr_info FROM publishers INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id
" UpdateCommand="UpdateTwoTables" UpdateCommandType="StoredProcedure">
    <UpdateParameters>
        <asp:Parameter Name="pub_id" Type="String" />
        <asp:Parameter Name="pub_name" Type="String" />
        <asp:Parameter Name="city" Type="String" />
        <asp:Parameter Name="state" Type="String" />
        <asp:Parameter Name="country" Type="String" />
        <asp:Parameter Name="pr_info" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>

18.Now press F5 and see the page in browser. Click edit and update values.