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>

Multiple rows

Hi everybody:
I have this result from a query:
category id pos name
Fixed
1 100009 999003 BLA BLA BLA 1
1 100010 999003 BLA BLA BLA 1
2 100321 999007 EX EX EX 0
2 100322 999007 EX EX EX 0
2 100323 999007 EX EX EX 0
2 100324 999007 EX EX EX 0
2 100325 999007 EX EX EX 0
2 100326 999007 EX EX EX 0
Is there any way (not using UNION) to get the rows where Fixed NOT 0
but only one from where Fixed = 0 ?<acidscan@.gmail.com> wrote in message
news:1175639463.958180.180500@.n76g2000hsh.googlegroups.com...
> Hi everybody:
> I have this result from a query:
> category id pos name
> Fixed
> 1 100009 999003 BLA BLA BLA 1
> 1 100010 999003 BLA BLA BLA 1
> 2 100321 999007 EX EX EX 0
> 2 100322 999007 EX EX EX 0
> 2 100323 999007 EX EX EX 0
> 2 100324 999007 EX EX EX 0
> 2 100325 999007 EX EX EX 0
> 2 100326 999007 EX EX EX 0
> Is there any way (not using UNION) to get the rows where Fixed NOT 0
> but only one from where Fixed = 0 ?
>
Please post DDL so that we don't have to guess what the keys are. Please
post the query that produces this result.
Explain the logic. Which row do you want to return where Fixed = 0? You mean
just some random row?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||id pos name Fixed
100009 999003 BLA BLA BLA 1
100010 999003 BLA BLA BLA 1
100321 999007 EX EX EX 0
100322 999007 EX EX EX 0
100323 999007 EX EX EX 0
100324 999007 EX EX EX 0
100325 999007 EX EX EX 0
100326 999007 EX EX EX 0
I made it shorted to remove the format. This is a simple select
returning some elements from a table. What i would like to do is only
show the first 3 elements based on the "fixed" column, first showing
the elements where "fixed" is not 0 and later all the elements where
"fixed" is 0 grouped into one row.
I could do this making a select where fixed != 0 and a "union all"
with another select where fixed = 0 using only the first element. This
union will return:
id pos name Fixed
100009 999003 BLA BLA BLA 1
100010 999003 BLA BLA BLA 1
100321 999007 EX EX EX 0
My question is: is there any shorter way (not using the union) to do
this ?|||<acidscan@.gmail.com> wrote in message
news:1175642227.533516.273640@.y66g2000hsf.googlegroups.com...
> id pos name Fixed
> 100009 999003 BLA BLA BLA 1
> 100010 999003 BLA BLA BLA 1
> 100321 999007 EX EX EX 0
> 100322 999007 EX EX EX 0
> 100323 999007 EX EX EX 0
> 100324 999007 EX EX EX 0
> 100325 999007 EX EX EX 0
> 100326 999007 EX EX EX 0
> I made it shorted to remove the format. This is a simple select
> returning some elements from a table. What i would like to do is only
> show the first 3 elements based on the "fixed" column, first showing
> the elements where "fixed" is not 0 and later all the elements where
> "fixed" is 0 grouped into one row.
> I could do this making a select where fixed != 0 and a "union all"
> with another select where fixed = 0 using only the first element. This
> union will return:
> id pos name Fixed
> 100009 999003 BLA BLA BLA 1
> 100010 999003 BLA BLA BLA 1
> 100321 999007 EX EX EX 0
> My question is: is there any shorter way (not using the union) to do
> this ?
>
You didn't specify a key so I'll assume that ID is the key. If I'm right,
try:
SELECT id, pos, name, fixed
FROM tbl
WHERE fixed <> 0
OR id =(SELECT MIN(id)
FROM tbl
WHERE fixed = 0);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--