Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem deleting from a GridView INSIDE a DataList 1

Status
Not open for further replies.
WallyAnchor

WallyAnchor

Programmer
May 17, 2008
12
NZ
(Reposted from Visual Basic(Microsoft) -VB.NET/VB2005 Forum, as this forum seems a better place for it...)

Hi.

I have a DataList that shows a number of GridViews. The datasource for the DataList returns a Name/title for each GridView, along with the Select/Delete commands and comma seperated list of datakeys.

Each GridView potentially has a different number of columns, and the column names differ. For this reason I've set AutoGenerateColumns="True", and am adding a delete commandfield. The select/delete commands are being set in the codebehind.

My problem is, although the select & delete commands are being set in the datasource when the page is displayed, they are lost on postback, after clicking a delete button in a gridview, and I get the error "Deleting is not supported by data source 'dsInner' unless DeleteCommand is specified."

What am I doing wrong? How can I get the datasource to retain the DeleteCommand that I'm setting in the codebehind?

Here's a rather simplified version of my page:
Code: 
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>

            <asp:DataList ID="dlItems" runat="server" DataSourceID="dsOuter" RepeatDirection="Horizontal" RepeatLayout="Flow" ItemStyle-Width="200">
                <ItemTemplate>
                    <asp:Label ID="lblItemHeader" runat="server" Text='<%#Container.DataItem("ItemName") %>' />
         
                    <asp:GridView ID="gvInfo" runat="server" GridLines="None" DataSourceID="dsInner" AutoGenerateColumns="True">
                        <Columns>
                            <asp:CommandField DeleteText="delete" ShowDeleteButton="True" />
                        </Columns>
                    </asp:GridView>

                    <asp:SqlDataSource ID="dsInner" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" />

                </ItemTemplate>
            </asp:DataList>

            <asp:SqlDataSource ID="dsOuter" runat="server" SelectCommand="GetOuterItems" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" />
        </div>
    </form>
</body>
</html>
and the codebehind:
Code: 
Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub dlItems_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlItems.ItemCreated
        If Not IsPostBack() Then
            Dim dataItems As Object() = CType(e.Item.DataItem, Data.DataRowView).Row.ItemArray
            Dim tmpItem As DataListItem = e.Item
            Dim datakeys As String() = CType(dataItems(4), String).Split(",")

            Dim dsInnerItem As SqlDataSource = tmpItem.FindControl("dsInner")
            Dim tmpGV As GridView = tmpItem.FindControl("gvInfo")
            tmpGV.DataKeyNames = datakeys

            dsInnerItem.SelectCommand = dataItems(1)
            dsInnerItem.SelectCommandType = SqlDataSourceCommandType.StoredProcedure

            If dataItems(2) Then 'Show OK/Hide
                dsInnerItem.DeleteCommand = dataItems(3)
                dsInnerItem.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure
                For Each key As String In datakeys
                    dsInnerItem.DeleteParameters.Add(key, 0)
                Next
            End If
        End If
    End Sub

End Class
 
Sort by date Sort by votes
First of all I would suggest to not use the SqlDataSource controls and such - they are a pain.

 
Upvote 0 Downvote
I've never really had any problem with them before, and I'm not entirely sure they are the problem.

What would you suggest to use instead?

Cheers
Wally
 
Upvote 0 Downvote
whether or not the sqldatasource is the problem youcan't argue that using the sqldatasource blurs the debugging process because you can't step through the code or automate a test against markup to assert proper behavior.

that said, while sqldatasource does work. there are better ways to write maintainable applications. the first place to start is abandoning asp.net controls that don't deal with presenation.

try placing the code above in the item bound event instead of item created. item create is fired with each postback as the control is recreated with each request. item databound only fires when data is bound (usually if not postback, or a button is clicked).

Jason Meckley
Programmer
Specialty Bakers, Inc.
 
Upvote 0 Downvote
Hi.

Ok, I moved the code to the item databound event. I'm not entirely sure why I had it in the itemcreated event - must have been clutching at straws. Anyway, this didn't make any difference - exact same behaviour.

So I ditched the SqlDataSource control & filled a DataAdapter and bound that to my GridView.
Ok so far...
But I now can't see how to get the delete sql command to be executed?
Can I use the DataAdapter I used to fill my GridView? (I can't seem to get a handle on it anyway)
Or should I explicitly execute my delete command? If I do that, to make the gridview reflect the change I'd have to rebind but at this point it no longer has a datasource associated so it returns an empty GridView.

I could delete and then repopulate the DataAdapter & rebind, but this seems inefficient. I guess I could just change the visibility of the deleted row....

What is the normal way of handling this? All examples I can find use the SqlDataSource - which is why I went that way to begin with - I thought that was the correct way.

Any help appreciated.

Cheers
Wally
 
Upvote 0 Downvote
If you dont mind posting all of your code you have this far, I don't mind helping you intregrate the items you are having problems with :)
 
Upvote 0 Downvote
Thanks matey.

I've been messing around with this and this is what I've currently got. I know it's not right because it doesn't work.. :)

This is where I'm at at the moment:
Code: 
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Test.aspx.vb" Inherits="Test" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>

            <asp:DataList ID="dlItems" runat="server" RepeatDirection="Horizontal" RepeatLayout="Flow" ItemStyle-Width="200">
                <ItemTemplate>
                    <asp:Label ID="lblItemHeader" runat="server" Text='<%#Container.DataItem("ItemName") %>' />
         
                    <asp:GridView ID="gvInfo" runat="server" GridLines="None" AutoGenerateColumns="True" OnRowDeleting="gvInfo_RowDeleting">
                        <Columns>
                            <asp:CommandField DeleteText="delete" ShowDeleteButton="True" />
                        </Columns>
                    </asp:GridView>

                </ItemTemplate>
            </asp:DataList>

        </div>
    </form>
</body>
</html>
and
Code: 
Imports System.Data
Imports System.Data.SqlClient

Partial Class Test
    Inherits System.Web.UI.Page


   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      If Not Page.IsPostBack Then
         'Start by determining the connection string value
         Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

         'Create a SqlConnection instance
         Using myConnection As New SqlConnection(connString)
            'Specify the SQL query
            Const sql As String = "GetOuterItems"

            'Create a SqlCommand instance
            Dim myCommand As New SqlCommand(sql, myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            'Get back a DataSet
            Dim myDataSet As New DataSet

            'Create a SqlDataAdapter instance
            Dim myAdapter As New SqlDataAdapter(myCommand)
            myAdapter.Fill(myDataSet)

            'Bind the DataSet to the GridView
            dlItems.DataSource = myDataSet
            dlItems.DataBind()

            'Close the connection
            myConnection.Close()
         End Using
      End If
   End Sub




   Protected Sub dlItems_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataListItemEventArgs) Handles dlItems.ItemDataBound
        If Not IsPostBack() Then

            Dim dataItems As Object() = CType(e.Item.DataItem, Data.DataRowView).Row.ItemArray
            Dim tmpItem As DataListItem = e.Item
            Dim datakeys As String() = CType(dataItems(4), String).Split(",")

            Dim tmpGV As GridView = tmpItem.FindControl("gvInfo")
            tmpGV.DataKeyNames = datakeys




            'Start by determining the connection string value
            Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

            'Create a SqlConnection instance
            Using myConnection As New SqlConnection(connString)
                'Specify the SQL query
                Dim sql As String = dataItems(1)

                'Create a SqlCommand instance
                Dim myCommand As New SqlCommand(sql, myConnection)
                myCommand.CommandType = CommandType.StoredProcedure

                'Get back a DataSet
                Dim myDataSet As New DataSet

                'Create a SqlDataAdapter instance
                Dim innerAdapter = New SqlDataAdapter(myCommand)
                innerAdapter.Fill(myDataSet)
                If dataItems(2) Then

                    '
                    'not sure if I should be declaring delete command here
                    innerAdapter.DeleteCommand = New SqlCommand(dataItems(3))
                    innerAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure
                End If

                'Bind the DataSet to the GridView
                tmpGV.DataSource = myDataSet
                tmpGV.DataBind()

                'Close the connection
                myConnection.Close()
            End Using


        End If
   End Sub

   Protected Sub gvInfo_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)


        'can't access "innerAdapter" here to execute the delete statement
        ' - not even sure if that's what I should be doing....

   End Sub


End Class

Any pointers/criticism gratefully received....
 
Upvote 0 Downvote
I could delete and then repopulate the DataAdapter & rebind, but this seems inefficient.
Click to expand...
this is the what happens if you use an [X]DataSource control anyway.

the web is stateless. so with each request it has no idea what you did in the previous request. viewstate hacks this by hashing data into a hidden field.

here is the typical way users modify data.
1. on page load, if not postback, select data and bind to grid
2. click delete
3. execute a delete statement agasinst the database
4. select data and bind to grid

Jason Meckley
Programmer
Specialty Bakers, Inc.
 
Upvote 0 Downvote
I could delete and then repopulate the DataAdapter & rebind, but this seems inefficient.
Click to expand...
this is the what happens if you use an [X]DataSource control anyway.
Click to expand...

I did wonder if that was what was actually being done 'in the background', but I thought maybe I was overthinking it. I guess I should have run profiler to see what was happening on the sql server for a basic (working) example.

So, because I have a number of GridViews nested in a DataList (the DataList's datasource contains the select & delete commands for the nested GridViews), for a delete I need to:
1. Execute the outer select to get the delete statement
2. Execute the inner delete statement
3. Re-execute the inner select statement & rebind to the GridView.

Does that sound right?

That's about what I was going to do originally, but I thought using the SqlDataSource was more efficient - executing the delete statement and just removing it from the GridView without the need to re-select/re-bind. Obviously it was just masking the actual process.

Anyway, in that case I can probably execute the delete statement and just change the visibility of the affected row. I think that will suit me - I'll test it tomorrow (it's 1am here now & I need sleep).

I'll let you know how I go.

Thanks
Wally
 
Upvote 0 Downvote
set the gridview.datakey value to the id of dataitem. assign a delegate to the GridView_RowDeleting Event.
within this event pull the id from the datakey something like
Code: 
int id = (int)MyGridView.DataKeys[e.RowIndex]["Id"];
bool wasDeleted = DeleteRecordFromDatabaseWhereIdIs(id);
e.Cancel = !wasDeleted;
//DeleteRecordFromDatabaseWhereIdIs() should execute a statement similar to 'delete from [table] where[id] = @id'
in the GridView_RowDeleted event bind the grid
Code: 
MyGridView.DataSource = GetDataForGridView();
MyGridView.DataBind();
because you are using gridviews within a datalist you may want to cast the sender as an easy way to access the gridview
Code: 
GridView grid = sender as GridView;
if(grid != null)
{
  //get key, bind, whatever
}
unless you have disabled viewstate there is no need to execute a select for anything before deleting a record. and after the delete you only need to rebind the grid that has changed.

Jason Meckley
Programmer
Specialty Bakers, Inc.
 
Upvote 0 Downvote
Hi.

Well, I had to put this aside for a while due to other work, but I'm back on it again.

I got this working fine in my simplified version, but it took a bit more work to get it going in my actual project which is a more complex. Anyway, it's all working good now - thanks for your help.

One thing however I'm not too sure if I've gone about the right way - passing data from the outer resultset (for the datalist) to the datalistitems which contain the 'inner' resultset/gridviews.

Each row in the outer resultset contains the select/delete commands along with other data used to determine how the inner gridviews are displayed. I was putting each of the outer rows into a "dataItems" object
Code: 
Dim dataItems As Object() = CType(e.Item.DataItem, Data.DataRowView).Row.ItemArray
and referencing them: dataItems(x), as you can see above.

This worked fine for the initial page load, but on postback, e.Item.DataItem was null/nothing so I couldn't get from the outer resultset the command to delete from each gridview, or the select command to repopulate the grid after deletion. I ended up putting this data in HiddenFields within each DataListItem, which I could then reference after postback.

Does that make sense? It's all working fine but this last bit seems like a bit of a hack. Is there a better way I could do this?

Cheers
Wally
 
Upvote 0 Downvote
DataItem contains the object being bound when DataBinding, if you are not within a DataBind() then this property is null.

Jason Meckley
Programmer
Specialty Bakers, Inc.
 
Upvote 0 Downvote
Gidday.

(This should possibly be a new thread, but it does follow on from my previous one)

OK, when I integrate this into my project I've come across a bit of a weird issue. I haven't had a chance yet to break it down into something simpler yet so I can't nail down where the problem is yet.... Just wondering if anybody has come across something similar...

As per above, each of my gridviews is in a datalistitem. I'm getting the gridview.databound event raised twice for a gridview (not sure if it is for all - only tested on one particular one that I noticed so far).

The event is being raised once BEFORE any rowdatabound events, and then once (as expected) afterwards. Why would it be getting raised before any rows bound?

I'm definitely only calling this code once, in the gridviews' OnDataBound attribute. I don't have a "handles xyz.databound" on the sub, and I'm not calling it directly from any other code.

I've stepped through the code from the start and for the life of me can't find anything untoward.

I'm not too hopeful of solving this until I can strip it down to a basic example, but if anybody has come across something like this before and/or has any ideas of why this might be happening, I'll gladly take note.

Cheers
Wally
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
618
Makumbi256
Makumbi256
weberm
  • Locked
  • Question
How do I make a page with multiple AJAX custom controls?
Replies
2
Views
688
weberm
weberm
G12Consult
  • Locked
  • Question
Javascript from Content Pages
Replies
0
Views
620
G12Consult
G12Consult
G12Consult
  • Locked
  • Question
Retrieve data from JSON
Replies
1
Views
649
gmmastros
gmmastros
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
709
AhmedAzizBarbary
AhmedAzizBarbary

Part and Inventory Search

Sponsor

Back
Top