Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to update two tables from GridView update command in ASP.NET

Author: Alanna Kremer

This article will illustrate how to update two SQL Server database tables from one GridView update command in ASP.NET.

 

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.

 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to display column total on GridView Footer in ASP.NET

Disclaimer - Privacy
© 2002-2014 DevASP.net