multiple sql staements in SqlDataSources update command

I have a gridview with a sqlDataSource with the SelectCommand as
"SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"

what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
SelectCommand= "SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
UpdateCommand="UPDATE [ItemForSale] SET [Quantity] = @.myQuantity FROM FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
WHERE ItemForSale.ID = @.ID" >
<UpdateParameters>
<asp:Parameter Name="myQuantity" Type="Int32" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>

</asp:SqlDataSource>

Make sure the ID column in your ItemForSale is a primary key of the table.

|||I need to update the movie table AND the ItemForSale table, the example above would only update the ItemForSale table.|||

The ID column should include in your select statement.

Here is a working sample for updating one column from each table using two UPDATE statements.

<%

@.PageLanguage="VB"AutoEventWireup="false"CodeFile="test2.aspx.vb"Inherits="test2" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:GridViewID="GridView1"runat="server"DataSourceID="SqlDataSource1"DataKeyNames="ID"><Columns><asp:CommandFieldShowEditButton="True"/></Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT Movie.ID, Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"UpdateCommand="UPDATE [Movie] SET [Title] = @.Title FROM Movie INNER JOIN ItemForSale ON ItemForSale.ID = Movie.ID WHERE Movie.ID = @.ID;UPDATE [ItemForSale] SET [Quantity] = @.Quantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID

WHERE ItemForSale.ID = @.ID">

<UpdateParameters><asp:ParameterName="ID"Type="Int32"/><asp:ParameterName="Quantity"Type="Int32"/><asp:ParameterName="Title"Type="String"/></UpdateParameters></asp:SqlDataSource></div></form>

</

body>

</

html>