Deleting a row using a Stored Procedure from a GridView

I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:

CREATE PROCEDURE usp_DeleteBox/* ******************************************* Delete a record using the Passed ID.********************************************** */(@.pIDas int =Null)ASDELETE FROM [Boxes]WHERE ID = @.pID

When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).

When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.

Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!

Do you have any other columns in the DataKeyNames field? Any DeleteParameters on the DataSource? Can you show the GridView and DataSource code ?

|||

Thanks for your quick reply! I have no other colums in the DataKeyNames field. Here is the code you mentioned:

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource_Boxes" Font-Names="Arial" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="BoxNumber" HeaderText="BoxNumber" SortExpression="BoxNumber" /> <asp:BoundField DataField="PalletNumber" HeaderText="PalletNumber" SortExpression="PalletNumber" /> <asp:BoundField DataField="DestName" HeaderText="DestName" SortExpression="DestName" /> <asp:BoundField DataField="DestCode" HeaderText="DestCode" SortExpression="DestCode" /> <asp:CommandField ButtonType="Button" ShowDeleteButton="True" /> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView>

 <asp:SqlDataSource ID="SqlDataSource_Boxes" runat="server" ConnectionString="<%$ ConnectionStrings:BoxTrackSite.My.MySettings.ShippingConnStr%>" SelectCommand="SELECT dbo.Boxes.ID, dbo.Boxes.BoxNumber, dbo.Pallets.PalletNumber, dbo.Destinations.DestName, dbo.Destinations.DestCode FROM dbo.Boxes INNER JOIN dbo.Pallets ON dbo.Boxes.PalletID = dbo.Pallets.ID INNER JOIN dbo.Destinations ON dbo.Boxes.DestID = dbo.Destinations.ID" DeleteCommand="usp_DeleteBox" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="pID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>

I know the SqlDataSource has a DeleteParameter (I don't know how that got there). If I removed that, it does nothing. Any further suggestions? Thanks again for your help.|||

I found a solution, but it wasn't as clean as I would have liked. Following the details inthis post, I found that I was indeed passing two parameters "pID" & "ID" when the SqlDataSource.Deleting event fired. So, as the other people mentioned, I changed my SP variable to be the same name as the identity column in my database ("ID") - and this worked.

It's frustrating that this is the behaviour. The controls should support methods for doing things as I was trying to do it originally. Some developers do not have the "luxury" (or permission) to modify existing SP's.