Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

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

  • 15-11-2013 10:57am
    #1
    LordSinclair
    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