1,105,625 Community Members

Gridview using a VIEW insted of a table

Member Avatar
pepyrs
Light Poster
25 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
nakor77
Junior Poster
136 posts since Sep 2011
Reputation Points: 21 [?]
Q&As Helped to Solve: 33 [?]
Skill Endorsements: 6 [?]
 
0
 

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.

Member Avatar
pepyrs
Light Poster
25 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
pepyrs
Light Poster
25 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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>
Question Answered as of 2 Years Ago by nakor77
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: