Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Updating a GridView with an Sqldatasource using Inner Join...

  • 15-11-2013 11:57AM
    #1
    Closed Accounts Posts: 83 ✭✭


    Hi folks,

    I have two GridViews in my aspx page, GridView1 and GridView2.

    GridView2 is bound to the ID Column of GridView1 (put properly, the ID Column of Gridview1 is a Control Parameter for GridView2).

    GridView2 has the following Sqldatasource:
           <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:xxxxxxxxxxxxxxxxxxx %>" 
            OldValuesParameterFormatString="original_{0}"
            SelectCommand = "SELECT MachineProduct.Id, MachineProduct.Description, MachineProduct.Name, MachineProduct.StockCode, MachineProduct.Cost, MachineProduct.Quantity, MachineProduct.InvoiceAmountDue, MachineProduct.OutputVATAmount FROM [MachineProduct] INNER JOIN [MachineSpecProduct] ON MachineProduct.Id = MachineSpecProduct.ProductID WHERE (MachineSpecProduct.SpecID = @MyId)"
            UpdateCommand="UPDATE [MachineSpecProduct] SET [ProductId] = @ProductID FROM [MachineSpecProduct.ID] INNER JOIN [MachineSpecProduct] ON MachineProduct.Id = MachineSpecProduct.ProductID = @Id"
            onselecting="SqlDataSource2_Selecting">
        <SelectParameters>
        <asp:Parameter Name="MyID" Type="Int16" />
        </SelectParameters>
        <UpdateParameters>
        <asp:Parameter Name="Id" Type="Int16" />
        <asp:ControlParameter Name="ProductID" ControlID="ctl00$ContentPlaceHolder2$GridView2$ctl02$EditProductID" PropertyName="SelectedValue" Type="Int16" />
        </UpdateParameters>
        </asp:SqlDataSource>
    

    My [MachineSpecProduct] table has a SpecID coloumn and a ProductID column. I read the SpecID value from the ID column in GridView1 and use this to populate a list of products that is returned in GridView2.

    This is all working fine...

    In my GridView2, I want to be able to edit the ProductID value in my [MachineSpecProduct], and update my [MachineSpecProduct] table, with the ID value of my Product in the [MchineProduct] table, (which I have returned via a DropDownList bound back to my [MachineProduct] table, and I'm using this Dropdownlist as a control parameter for my update...
                <asp:TemplateField HeaderText="StockCode" SortExpression="StockCode">
                <ItemTemplate>
                <asp:Label ID="StockCodeLabel" runat="server" Text='<%# Bind("StockCode") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                <asp:DropDownList ID="EditProductID" runat="server" DataSourceID="SqlDataSource3" DataTextField="StockCode" DataValueField="Id">
                </asp:DropDownList>
                </EditItemTemplate>
                </asp:TemplateField>
    

    The problem here for what I'm trying to do (an UPDATE), using the code above (and below), I know my SQL is wrong here in relation the INNER JOIN, I haven't used an inner join before this week so am a bit out of my depth, that and my SQL not being too great!

    Any help with this is as always, greatly appreciated...

    EDIT: The SELECT aspect of this is working perfectly, it is only the UPDATE part that I'm having a problem getting right...


Advertisement