CODEWITHSUNDEEP
c#asp.netsql
Ajesh.G
Ajesh.G

Reputation: 21

How to update multiple tables using single query

I have three tables

  1. Categorycrt productid(primary key), productcategory, Girthfrom, GirthTo

  2. Itemcre Itemid(primary key). Itemname,

  3. Pricefix PriceId(primarykey), Itemid(foreign key of Itemcre), Productid(foreign Key of Categorycrt), Rate

How do I update the Pricefix table?

I have a gridview for pricefix table.

My aspx page

<lang="html">
   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    onrowcancelingedit="GridView1_RowCancelingEdit"
    onrowdeleting="GridView1_RowDeleting" 
    onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating">
<columns>
       <asp:TemplateField HeaderText="PriceId">
       <itemtemplate>
       <asp:Label ID="lblPriceId" runat="server" Text='&lt;%#Eval("PriceId") %>'>'> 
       </itemtemplate>

        <asp:TemplateField HeaderText="ItemName">
       <itemtemplate>
       <asp:Label ID="lblItemId" runat="server" Text='&lt;%#Eval("Itemname") %>'>'> 
       </itemtemplate>
       <edititemtemplate>
       <asp:TextBox ID="txtItemId" runat="server" Text='&lt;%#Eval("Itemname") %>'>'>
       </edititemtemplate>

        <asp:TemplateField HeaderText="ProductCategory">
       <itemtemplate>
       <asp:Label ID="lblPdtId" runat="server" Text='&lt;%#Eval("ProductCategory") %>'>'> 
       </itemtemplate>
       <edititemtemplate>
       <asp:TextBox ID="txtPdtId" runat="server" Text='&lt;%#Eval("ProductCategory") %>'>'>
       </edititemtemplate>

        <asp:TemplateField HeaderText="Rate">
       <itemtemplate>
       <asp:Label ID="lblRate" runat="server" Text='&lt;%#Eval("Rate") %>'>'> 
       </itemtemplate>
       <edititemtemplate>
       <asp:TextBox ID="txtRate" runat="server" Text='&lt;%#Eval("Rate") %>'>'>
       </edititemtemplate>

           <asp:TemplateField HeaderText="Action">
               <itemtemplate>
                   <asp:LinkButton ID="edit" runat="server" CommandName="Edit" Text="Edit">
                   <asp:LinkButton ID="Delete" runat="server" CommandName="Delete" Text="Delete">
               </itemtemplate>
               <edititemtemplate>
                   <asp:LinkButton ID="Update" runat="server" CommandName="Update" Text="Update">
                   <asp:LinkButton ID="Cancel" runat="server" CommandName="Cancel" Text="cancel">
               </edititemtemplate>

       </columns>

While clicking on the edit button of the grid update and cancel link button appears for the selected row. I need to update the productcategory, itemname and rate of the pricefix table.actually in pricefix table the value stored as foreignkey value of productcategory and, itemname....actually while updating the gridview of pricefix the updated productcategory and itemname should be changed in their parent table i.e, Itemcre and Categorycrt and rate inPricefix table itself.

Upvotes: 0

Views: 405

Answers (1)

Anthony Horne
Anthony Horne

Reputation: 2522

You should really then be using a stored procedure to affect the change. You can update the data source updatecommand to a procedure and nicely cater for any issues that arise in transactions.

It will be somewhere along the lines of:

CREATE PROCEDURE USP_UPDATEPRICE
(
@productcategory INT,
@itemname INT
@rate INT
)
AS

UPDATE
    pricefix 
SET
    RATE = @rate
FROM
    pricefix
INNER JOIN
    PriceCAt
         ON pricefix.id = pricecat.pricefix
WHERE
     pricecat.itemname = @pricename
and
     pricecat.cat = @cat

GO

For your assignment, you really do need to do some reading up on simple updates - otherwise your studies will be for nothing.

Upvotes: 1

Related Questions