Procedure or function has too many arguments specified

[Mike]

I posted this is m.p.d.f.aspnet last night, but got no useful response. I
have also googled this group (and a lot of others), and have not found a
satisfactory explanation.

I have a stored proc with 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 SELECT, 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.

I used the wizard to configure the DataSource, and selected the appropriate
procs. I manually applied the UPDATE and DELETE ControlParameters, copying
and pasting the value given by the wizard for the SELECT ControlParameter.
(Why DOES the wizard duplicate the controls in the parameter drop-down
list?!?) The DELETE 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.

I have changed the Delete CommandType to Text, and gave the CommandText
value as "DELETE FROM Agency WHERE AgencyID = @AgencyID". The page works
fine now, so while I guess I have got round my problem, I still don't know
what the problem was.

Is there some bug in the way that VS2005 or VWD configures SqlDatasources?
It obviously aint right, otherwise it wouldn't list controls twice in the
parameter configuration dop-down.

Mike

 

[W.G. Ryan [MVP]]

Mike, I'd run a trace on Sql Server and run it. See what params are actually
being passed in, this should give you a clue as to what the problem is.

 

[Mike]

Thanks for the response. I'm running Express, but I'm expecting an
evalutation copy of the full product to turn up any day. Hopefully that has
the tools for what I need to do.

The maddening thing is that I've since created a number of other pages in
exactly the same way, and they all work correctly. I tried renaming the
offending proc, deleting the datasource and then creating a new one
attaching the renamed proc. No change though. I deleted the proc, then
recreated it. No joy.

I suppose I could try deleting the page, all related procs and then start
again, but I really would like to know why this one is acting up.

Mike

Mike, I'd run a trace on Sql Server and run it. See what params are
actually being passed in, this should give you a clue as to what the
problem is.

I posted this is m.p.d.f.aspnet last night, but got no useful response. I
have also googled this group (and a lot of others), and have not found a
satisfactory explanation.

I have a stored proc with 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 SELECT, 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.

I used the wizard to configure the DataSource, and selected the
appropriate procs. I manually applied the UPDATE and DELETE
ControlParameters, copying and pasting the value given by the wizard for
the SELECT ControlParameter. (Why DOES the wizard duplicate the controls
in the parameter drop-down list?!?) The DELETE 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.

I have changed the Delete CommandType to Text, and gave the CommandText
value as "DELETE FROM Agency WHERE AgencyID = @AgencyID". The page works
fine now, so while I guess I have got round my problem, I still don't
know what the problem was.

Is there some bug in the way that VS2005 or VWD configures
SqlDatasources? It obviously aint right, otherwise it wouldn't list
controls twice in the parameter configuration dop-down.

Mike

 

[W.G. Ryan [MVP]]

It sounds like something isn't being cleared from the parameters collection
for the delete command but I can't be sure. Did you loop through the
SqlErrors collection?

Thanks for the response. I'm running Express, but I'm expecting an
evalutation copy of the full product to turn up any day. Hopefully that
has the tools for what I need to do.

The maddening thing is that I've since created a number of other pages in
exactly the same way, and they all work correctly. I tried renaming the
offending proc, deleting the datasource and then creating a new one
attaching the renamed proc. No change though. I deleted the proc, then
recreated it. No joy.

I suppose I could try deleting the page, all related procs and then start
again, but I really would like to know why this one is acting up.

Mike

Mike, I'd run a trace on Sql Server and run it. See what params are
actually being passed in, this should give you a clue as to what the
problem is.

I posted this is m.p.d.f.aspnet last night, but got no useful response.
I have also googled this group (and a lot of others), and have not found
a satisfactory explanation.

I have a stored proc with 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 SELECT, 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.

I used the wizard to configure the DataSource, and selected the
appropriate procs. I manually applied the UPDATE and DELETE
ControlParameters, copying and pasting the value given by the wizard for
the SELECT ControlParameter. (Why DOES the wizard duplicate the controls
in the parameter drop-down list?!?) The DELETE 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.

I have changed the Delete CommandType to Text, and gave the CommandText
value as "DELETE FROM Agency WHERE AgencyID = @AgencyID". The page
works fine now, so while I guess I have got round my problem, I still
don't know what the problem was.

Is there some bug in the way that VS2005 or VWD configures
SqlDatasources? It obviously aint right, otherwise it wouldn't list
controls twice in the parameter configuration dop-down.

Mike