Delete command in GridView

AlexW · July 15th, 2011, 12:33 PM

I have a grid view that pulls data from two tables:

SelectCommand="SELECT AlertedEvents.AlertedEventID, AlertedEvents.EventID, AlertedEvents.UserName, AlertedEvents.RequestedJoin, Events.UserName AS Expr1, Events.GolfClubID, Events.GolfClubName, Events.DateTime, Events.GenderPreference, Events.HandicapFrom, Events.HandicapTo, Events.AgeFrom, Events.AgeTo, Events.MatchType, Events.FeedbackRequired, Events.AdditionalComments, Events.NoOfPlayers FROM AlertedEvents LEFT OUTER JOIN Events ON AlertedEvents.EventID = Events.EventID WHERE AlertedEvents.UserName = @UserName AND AlertedEvents.ApprovedJoin = 'False' ORDER BY Events.DateTime"

But want a delete command to only remove an entry from one table:

DeleteCommand="DELETE FROM [AlertedEvents] WHERE [AlertedEventID] = @AlertedEventID">

- the AlertedEventID being the one contained in the grid view as returned in the select statement. Enabling deletion adds the delete column but this does nothing. Trying to alter the code manually I added the above delete statement, and the parameter:

<DeleteParameters>
<asp:Parameter Name="AlertedEventID" Type="Int32" />
</DeleteParameters>

- but still this does nothing. Do I have to do something special to hook up @AlertedEventID in the delete statement with the one contained in the grid view row?

I tried DELETE FROM [AlertedEvents] which, as expected, removed everything.

Imar · July 15th, 2011, 12:38 PM

I think you know what I am going to ask next.....

Please post the code for your GridView and your SqlDataSource.

I bet you saw that one coming.....

A few other things:

1. Is AlertedEventID the primary key in the database?
2. Did you follow the data source configuration wizard to enable deletion?

Imar

AlexW · July 15th, 2011, 01:34 PM

Certainly did see that coming, grid view:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" GridLines="None" AlternatingRowStyle-BackColor="White" Width="100%"
AllowSorting="True" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<AlternatingRowStyle BackColor="White"></AlternatingRowStyle>
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:HyperLinkField DataNavigateUrlFields="AlertedEventID,EventID"
DataNavigateUrlFormatString="~/MyGolfingNetwork/AlertDetails.aspx?AlertedEventID={0}&EventID={ 1}"
Text="See Details" />
<asp:BoundField DataField="AlertedEventID" HeaderText="AlertedEventID" Visible="false"
InsertVisible="False" ReadOnly="True" SortExpression="AlertedEventID" />
<asp:BoundField DataField="EventID" HeaderText="EventID" Visible="false"
SortExpression="EventID" />
<asp:BoundField DataField="UserName" HeaderText="UserName" Visible="false"
SortExpression="UserName" />
<asp:BoundField DataField="Expr1" HeaderText="Game Host" SortExpression="Host" />
<asp:BoundField DataField="GolfClubID" HeaderText="GolfClubID" Visible="false"
SortExpression="GolfClubID" />
<asp:BoundField DataField="GolfClubName" HeaderText="GolfClubName"
SortExpression="GolfClubName" />
<asp:BoundField DataField="DateTime" HeaderText="DateTime"
SortExpression="DateTime" />
<asp:BoundField DataField="GenderPreference" HeaderText="GenderPreference" Visible="false"
SortExpression="GenderPreference" />
<asp:BoundField DataField="HandicapFrom" HeaderText="HandicapFrom" Visible="false"
SortExpression="HandicapFrom" />
<asp:BoundField DataField="HandicapTo" HeaderText="HandicapTo" Visible="false"
SortExpression="HandicapTo" />
<asp:BoundField DataField="AgeFrom" HeaderText="AgeFrom" Visible="false"
SortExpression="AgeFrom" />
<asp:BoundField DataField="AgeTo" HeaderText="AgeTo" SortExpression="AgeTo" Visible="false" />
<asp:BoundField DataField="MatchType" HeaderText="MatchType" Visible="false"
SortExpression="MatchType" />
<asp:BoundField DataField="FeedbackRequired" HeaderText="FeedbackRequired" Visible="false"
SortExpression="FeedbackRequired" />
<asp:BoundField DataField="AdditionalComments" HeaderText="AdditionalComments"
SortExpression="AdditionalComments" />
<asp:BoundField DataField="NoOfPlayers" HeaderText="NoOfPlayers" Visible="false"
SortExpression="NoOfPlayers" />
<asp:BoundField DataField="RequestedJoin" HeaderText="Requested Join"
SortExpression="RequestedJoin" Visible="false"/>
<asp:CheckBoxField DataField="RequestedJoin" HeaderText="Requested Join" SortExpression="RequestedJoin" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Delete" Text="Delete"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle HorizontalAlign="Center" />
<RowStyle HorizontalAlign="Center" />
</asp:GridView>

****************** Data Source:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT AlertedEvents.AlertedEventID, AlertedEvents.EventID, AlertedEvents.UserName, AlertedEvents.RequestedJoin, Events.UserName AS Expr1, Events.GolfClubID, Events.GolfClubName, Events.DateTime, Events.GenderPreference, Events.HandicapFrom, Events.HandicapTo, Events.AgeFrom, Events.AgeTo, Events.MatchType, Events.FeedbackRequired, Events.AdditionalComments, Events.NoOfPlayers FROM AlertedEvents LEFT OUTER JOIN Events ON AlertedEvents.EventID = Events.EventID WHERE AlertedEvents.UserName = @UserName AND AlertedEvents.ApprovedJoin = 'False' ORDER BY Events.DateTime"
DeleteCommand="DELETE FROM [AlertedEvents] WHERE [AlertedEventID] = @AlertedEventID">
<SelectParameters>
<asp:ControlParameter ControlID="lblUserName" Name="UserName" PropertyName="Text"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="AlertedEventID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>

If I convert the delete button to a template field the check to enable deletion in the quick menu of the grid view disappers hence I have a delete link at the start and end of the grid view.

Imar · July 15th, 2011, 01:40 PM

Quote:

Certainly did see that coming

Just kidding. For some reason, I am participating in a number of post today and yesterday where getting the right code seems to be the hardest part of the discussion.

You need to assign the DataKeys property to the GridView so it understands what record to delete. E.g.:

<asp:GridView....... DataKeys="AlertedEventID" ....>

The fact that VWD didn't add that for you might indicate it's not a primary key in the database.....

Hope this helps,

Imar

AlexW · July 15th, 2011, 01:50 PM

Added DataKeys="AlertedEventID" as an attribute in the GridView but still nothing happens when I hit delete...

Imar · July 15th, 2011, 01:56 PM

Is it the primary key in your database?

Try this:

1. Drag that table from the Database Explorer onto a *new* page so you end up with a GridView and a SqlDataSource.
2. Enable all options such as deleting, paging and so on on the GridView
3. View the page in the browser.

Does deleting work in that case?

Imar

AlexW · July 15th, 2011, 02:30 PM

Thanks Imar, I noticed the difference - I needed DataKeyNames="AlertedEventID".

Imar · July 15th, 2011, 03:34 PM

Pffff, DataKeys versus DataKeyNames, Items.Add(string, string) versus Items.Add(ListItem)... I guess I shouldn't be doing 617 items at the same time while writing posts here... ;-)

Glad you got it working....

Imar

AlexW · July 16th, 2011, 03:15 PM

Thanks Imar, I'm not sure of the difference but that seemed to get it working

Imar · July 16th, 2011, 04:50 PM

DataKeys is a collection on the GridView that exposes the actual keys for the objects assigned to the GridView. So, for example, when you have three rows in a GridView for three data items with an ID of 1, 2 and 3, the DataKeys collection contains 1, 2 and 3. The DataKeyNames property is used to determine from which property (the ID in this example) to retrieve a value. This value is then stored in View State so it can be exposed through the DataKeys after a postback.

Does that help? Or am I only confusing matters? ;-)

Imae