Procedure or function has too many arguments specified

[Mike]

This one has me baffled. The proc has one parameter:

CREATE PROCEDURE [dbo].[qDeleteAgencyDetails]
@AgencyID int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Agency WHERE AgencyID = @AgencyID
END

I use a dropdownlist to select an option which is displayed in a Formview,
which has an associated SqlDataSource with SELECT/INSERT/UPDATE and DELETE
commands specified. The control parameters for the commands are identical:

<UpdateParameters>
...
<asp:ControlParameter
ControlID="ddlAgencyList"
Name="AgencyID"
PropertyName="SelectedValue"
Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter
ControlID="ddlAgencyList"
Name="AgencyID"
PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:ControlParameter
ControlID="ddlAgencyList"
Name="AgencyID"
PropertyName="SelectedValue"
Type="Int32" />
<DeleteParameters>

The SELELCT, INSERT and UPDATE work as expected, but the DELETE throws the
exception (in the subject). I've got no code-behind interfering. The whole
thing is wired up declaratively at the moment. The stored proc runs fine
through SSMS. Other pages that follow exactly the same format as this one
(although they deal with different, but similarly structured tables) work
fine in all respects.

There are 20 bonus points for the first person to tell what the obvious
thing I have over looked is

Thanks

Mike

 

[dirk]

The first thing that I noticed is that you haven't used parentheses
around your parameter definition. I am not sure if that makes any
difference but I always use them.

So I suggest declaring your storec proc like that:
CREATE PROCEDURE [dbo].[qDeleteAgencyDetails]
(
@AgencyID int
)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Agency WHERE AgencyID = @AgencyID
END

Maybe that helps.

Good luck,

Dirk.

 

[Mike]

The first thing that I noticed is that you haven't used parentheses
around your parameter definition. I am not sure if that makes any
difference but I always use them.

So I suggest declaring your storec proc like that:
CREATE PROCEDURE [dbo].[qDeleteAgencyDetails]
(
@AgencyID int
)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Agency WHERE AgencyID = @AgencyID
END

Thanks for your response. I've never surrounded a parameter list with
parentheses, so I can't see how this would raise an error.

I still have no idea what the problem is, and all the googling I've done
suggests I'm far from alone. There seems to be a lot of unanswered
questions on this error on a lot of newsgroups/fora/boards. However, I got
round the problem by changing the command type from stored procedure to
text, then copied the DELETE statement from the proc to the aspx page. It
works fine, but it's a kludge.

Mike

 

[Mike]

The first thing that I noticed is that you haven't used parentheses
around your parameter definition. I am not sure if that makes any
difference but I always use them.

So I suggest declaring your storec proc like that:
CREATE PROCEDURE [dbo].[qDeleteAgencyDetails]
(
@AgencyID int
)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Agency WHERE AgencyID = @AgencyID
END

Thanks for your response. I've never surrounded a parameter list with
parentheses, so I can't see how this would raise an error.

I still have no idea what the problem is, and all the googling I've done
suggests I'm far from alone. There seems to be a lot of unanswered
questions on this error on a lot of newsgroups/fora/boards. However, I
got round the problem by changing the command type from stored procedure
to text, then copied the DELETE statement from the proc to the aspx page.
It works fine, but it's a kludge.

I've decided to try my luck with this Q in adonet.

Mike