Gridview using a VIEW insted of a table

[pepyrs]

Hi guys,

I have a problem when using gridview which source is a database VIEWS, not a table.

I have a MySQL database running, ASP web application(C# behind). Then the GridView's source is a combination of 8 views from the database. (

SELECT * FROM v1,v2,v3... v8

)
Then the problem is when I edit a cell in the GridView and hit Update it changes all the data (sequence numbers) and increment the numbers with one for each view. It's pretty strange result.

Here I have the code behind, the SELECT statement(which should be correct) and the UPDATE statement(where is the problem maybe) :

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:dbcssConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:dbcssConnectionString.ProviderName %>" 
            
            SelectCommand="select * from vv1, vv2, vv3, vv4, vv5, vv6, vv7, vv8
where vv1.pad = vv2.pad AND vv2.pad = vv3.pad AND vv3.pad = vv4.pad AND vv4.pad = vv5.pad AND vv5.pad = vv6.pad AND vv6.pad = vv7.pad AND vv7.pad = vv8.pad;" 
            UpdateCommand="UPDATE vv1, vv2, vv3, vv4, vv5, vv6, vv7, vv8 SET  Lift1 = @lift1, Lift2 = @lift2, Lift3 = @lift3, Task4 = @task4, Task5 = @task5, Task6 = @task6, Task7 = @task7, Task8 = @task8 WHERE ???">
        </asp:SqlDataSource>

I know that I didn't completed the UPDATE command but I think the problem is coming from the WHERE clauses in either of the commands.

Hope you can understand me and give me directions

Thanks a lot in advance

Pepys

[nakor77]

If you are using a view for your data source and want to perform an update that will affect multiple tables then it would probably be best to use the GridView's OnUpdating event to get the values and update each table individually.

[pepyrs]

You might be right but I think mine is a different scenario.
I have only one table in the database. I use 8 views for displaying the data I need, here is the first one

CREATE VIEW vv1 AS
SELECT  pad, sequence_no AS lift1 from management
where left(activity_id, 9) = 'MOUN.SECB';

I use the views so I can get a proper displaying of the data 'AS' Lift1 and so forth until lift8. (activity_id is different for each).
So the GridView is displaying the 'set' of these 8 views and then should update the table in the DB(only the sequence being changed).

Now when I edit a number(sequence) f.x in Lift1, the other lifts until 8 get values from 1 to 8 respectively.

[pepyrs]

I got it now.
To anyone who needs something like this, here is my code:
(it was all about the WHERE clause in the UpdateCommand)

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1" Width="893px" BackColor="White" 
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <Columns>
                <asp:CommandField ShowEditButton="True" CancelText="  X" />
                <asp:BoundField DataField="pad" HeaderText="Pad" 
                    SortExpression="pad" ReadOnly="False">
                <ControlStyle Height="5px" Width="40px" />
                </asp:BoundField>
                <asp:BoundField DataField="lift1" HeaderText="lift1" SortExpression="lift1" />
                <asp:BoundField DataField="lift2" HeaderText="lift2" SortExpression="lift2" />
                <asp:BoundField DataField="lift3" HeaderText="lift3" SortExpression="lift3" />
                <asp:BoundField DataField="Task4" HeaderText="Task4" SortExpression="Task4" />
                <asp:BoundField DataField="Task5" HeaderText="Task5" SortExpression="Task5" />
                <asp:BoundField DataField="Task6" HeaderText="Task6" SortExpression="Task6" />
                <asp:BoundField DataField="Task7" HeaderText="Task7" SortExpression="Task7" />
                <asp:BoundField DataField="Task8" HeaderText="Task8" SortExpression="Task8" />
            </Columns>
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:dbcssConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:dbcssConnectionString.ProviderName %>" 
            SelectCommand="select vv1.pad, lift1,  lift2, lift3, task4, task5, task6, task7, task8 from vv1, vv2, vv3, vv4, vv5, vv6, vv7, vv8
	where vv1.pad = vv2.pad AND vv2.pad = vv3.pad AND vv3.pad = vv4.pad AND vv4.pad = vv5.pad AND vv5.pad = vv6.pad AND vv6.pad = vv7.pad AND vv7.pad = vv8.pad;
" 
            
            UpdateCommand="UPDATE vv1, vv2, vv3, vv4, vv5, vv6, vv7, vv8 SET vv1.lift1=@lift1, vv2.lift2=@lift2, vv3.lift3=@lift3, task4 = @task4,task5 = @task5,task6 = @task6,task7 = @task7,task8 = @task8
WHERE vv1.pad = @pad AND vv2.pad = @pad AND vv3.pad = @pad AND vv4.pad = @pad AND vv5.pad=@pad AND vv6.pad = @pad AND vv7.pad = @pad AND vv8.pad=@pad;">
        </asp:SqlDataSource>