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

LordSinclair

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...