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.

Deletes through Replication

I have an order header we'll call HEADER and detail we'll call DETAIL
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:

> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))