ramrodcar
asked on
procedure or function has too many arguments specified
here's my code:
<UpdateParameters>
.....UpdateCommand="spWebU pdate" UpdateCommandType="StoredP rocedure">
<asp:Parameter Name="Amount" Type="int64" />
<asp:Parameter Name="Code" type="string" />
<asp:Parameter Name="DistCode" Type="Int64" />
<asp:Parameter Name="M" Type="string" />
<asp:Parameter Name="Mod" type="string"/>
<asp:Parameter Name="TSIC" type="string"/>
<asp:Parameter Name="SOLD" type="string"/>
<asp:Parameter Name="USNumber" Type="string" />
<asp:Parameter Name="UserCode" type="string"/>
<asp:Parameter Name="UserName" type="String"/>
<asp:Parameter Name="Zip1" type="string"/>
<asp:Parameter Name="Zip2" Type="string" />
</UpdateParameters>
CREATE PROCEDURE dbo.spWebUpdate
@Amount bigint,
@Code nvarchar(8),
@DistCode bigint,
@M nvarchar(5),
@Mod nvarchar(75),
@TSIC nvarchar(8),
@SOLD nvarchar(4),
@USNumber nvarchar(20),
@UserCode nvarchar(12),
@UserName nvarchar(75),
@Zip1 nvarchar(10),
@Zip2 nvarchar(10)
as
--set nocount on
begin
update dbo.ust set Amount=@Amount, Code=@Code, DistCode=@DistCode,
M=@M, Model=@Model, TSIC=@TSIC, SOLD=@SOLD,
UserCode=@UserCode, UserName=@UserName, [zip-1]=@ZIP1, [ZIP-2]=@ZIP2
WHERE USNumber=@usNUMBER
end
GO
i think it has something to do with the conversion of string to nvarchar, i've tried it using varchar (and changing the sp as well) too.
<UpdateParameters>
.....UpdateCommand="spWebU
<asp:Parameter Name="Amount" Type="int64" />
<asp:Parameter Name="Code" type="string" />
<asp:Parameter Name="DistCode" Type="Int64" />
<asp:Parameter Name="M" Type="string" />
<asp:Parameter Name="Mod" type="string"/>
<asp:Parameter Name="TSIC" type="string"/>
<asp:Parameter Name="SOLD" type="string"/>
<asp:Parameter Name="USNumber" Type="string" />
<asp:Parameter Name="UserCode" type="string"/>
<asp:Parameter Name="UserName" type="String"/>
<asp:Parameter Name="Zip1" type="string"/>
<asp:Parameter Name="Zip2" Type="string" />
</UpdateParameters>
CREATE PROCEDURE dbo.spWebUpdate
@Amount bigint,
@Code nvarchar(8),
@DistCode bigint,
@M nvarchar(5),
@Mod nvarchar(75),
@TSIC nvarchar(8),
@SOLD nvarchar(4),
@USNumber nvarchar(20),
@UserCode nvarchar(12),
@UserName nvarchar(75),
@Zip1 nvarchar(10),
@Zip2 nvarchar(10)
as
--set nocount on
begin
update dbo.ust set Amount=@Amount, Code=@Code, DistCode=@DistCode,
M=@M, Model=@Model, TSIC=@TSIC, SOLD=@SOLD,
UserCode=@UserCode, UserName=@UserName, [zip-1]=@ZIP1, [ZIP-2]=@ZIP2
WHERE USNumber=@usNUMBER
end
GO
i think it has something to do with the conversion of string to nvarchar, i've tried it using varchar (and changing the sp as well) too.
Can you use SQL Profiler to trace what is actually being sent to the SQL Server?
In your stored proc you declare a variable:
@Mod
but in the update part of the sproc you use this:
Model=@Model
@Mod
but in the update part of the sproc you use this:
Model=@Model
It's failing before that - (although you are right about this - it will error out once it get's executed)
ASKER
i'm getting this:
exec spWebUpdate @Amount = 1500, @Code = N'4222', @DistCode = 12431, @M = N'10', @Model = N'XX 22SDF', @TSIC = N'443', @SOLD = N'2006', @UserCode = N'2223', @UserName = N' test test test', @Zip1 = NULL, @Zip2 = NULL, @Zip-1 = N'07866', @Zip-2 = N'18343', @DistCode = N'27', @USNumber = N'2332a'
i haven't used profiler much, all the parameters with the N after the = sign had the value in quotes in red. what concerns me is that i don't have a zip-1 and zip-2 parameter, only zip1 / zip2, ZIP-1 AND ZIP-2 are the underlying table names being updated.
exec spWebUpdate @Amount = 1500, @Code = N'4222', @DistCode = 12431, @M = N'10', @Model = N'XX 22SDF', @TSIC = N'443', @SOLD = N'2006', @UserCode = N'2223', @UserName = N' test test test', @Zip1 = NULL, @Zip2 = NULL, @Zip-1 = N'07866', @Zip-2 = N'18343', @DistCode = N'27', @USNumber = N'2332a'
i haven't used profiler much, all the parameters with the N after the = sign had the value in quotes in red. what concerns me is that i don't have a zip-1 and zip-2 parameter, only zip1 / zip2, ZIP-1 AND ZIP-2 are the underlying table names being updated.
ASKER
also it's not a mis-spelling of Mod for Model, i looked at it and i fat fingered the copy/paste, in the asp parameter name, and the stored procedure (both spots) it says model.
Don't worry about the red - it simply indicates that they are strings.
@DistCode is being passed twice.
@Model does not exist in your sp parameter declarations.
@Mod is not being passed at all.
@Zip-1 does not exist in your ASP code - where does this come from?
@Zip-2 does not exist in your ASP code - where does this come from?
@DistCode is being passed twice.
@Model does not exist in your sp parameter declarations.
@Mod is not being passed at all.
@Zip-1 does not exist in your ASP code - where does this come from?
@Zip-2 does not exist in your ASP code - where does this come from?
ASKER
ok i don't know what is wrong with me,
zip-1 and zip-2 are the columns in the underlying table being updated
the second @distcode is a calculated field (who's complete name isn't "distcode", it's "DistTerritoryID" which i then started to change the name and forgot to take it out (don't want to reveal too much). so the second @distCode should be @DistTerritoryID, so it's not being passed twice.
There is no @Mod, it's @Model everywhere, i copied incorrectly.
let me recopy everything correctly below:
zip-1 and zip-2 are the columns in the underlying table being updated
the second @distcode is a calculated field (who's complete name isn't "distcode", it's "DistTerritoryID" which i then started to change the name and forgot to take it out (don't want to reveal too much). so the second @distCode should be @DistTerritoryID, so it's not being passed twice.
There is no @Mod, it's @Model everywhere, i copied incorrectly.
let me recopy everything correctly below:
ASKER
<UpdateParameters>
<asp:Parameter Name="Amount" Type="Int64" />
<asp:Parameter Name="Code" type="String" />
<asp:Parameter Name="DistCode" Type="Int64" />
<asp:Parameter Name="M" Type="String" />
<asp:Parameter Name="Model" type="String"/>
<asp:Parameter Name="TSIC" type="String"/>
<asp:Parameter Name="SOLD" type="String"/>
<asp:Parameter Name="USNumber" Type="String" />
<asp:Parameter Name="UserCode" type="String"/>
<asp:Parameter Name="UserName" type="String"/>
<asp:Parameter Name="Zip1" type="String"/>
<asp:Parameter Name="Zip2" Type="String" />
</UpdateParameters>
CREATE PROCEDURE dbo.spWebUpdateUSMTC
@Amount bigint,
@Code nvarchar(8),
@DistCode bigint,
@M nvarchar(5),
@Model nvarchar(75),
@TSIC nvarchar(8),
@SOLD nvarchar(4),
@USNumber nvarchar(20),
@UserCode nvarchar(12),
@UserName nvarchar(75),
@Zip1 nvarchar(10),
@Zip2 nvarchar(10)
as
--set nocount on
begin
update dbo.usmtc set Amount=@Amount, Code=@Code, DistCode=@DistCode,
M=@M, Model=@Model, TSIC=@TSIC, SOLD=@SOLD,
UserCode=@UserCode, UserName=@UserName, [zip-1]=@ZIP1, [ZIP-2]=@ZIP2
WHERE USNumber=@usNUMBER
end
GO
exec spWebUpdate @Amount = 1500, @Code = N'4222', @DistCode = 12431, @M = N'10', @Model = N'XX 22SDF', @SIC = N'33271', @SOLD = N'2006', @UserCode = N'2223', @UserName = N'test test test', @Zip1 = NULL, @Zip2 = NULL, @Zip-1 = N'07866', @Zip-2 = N'18343', @intUSMTCDistCode = N'312600', @DistTerritory_ID = N'17', @USNumber = N'2332a'
what is suprising is that i'm using a datagrid for this, i'm am running a query to fill the grid and this is the update events for it, it is pulling zip-1 and zip-2 (the column names, not param) from the table and filling the grid with them, but when i hit edit and then update, i get the above said error and the trace shows them as NULL. this works from query analyzer correctly.
<asp:Parameter Name="Amount" Type="Int64" />
<asp:Parameter Name="Code" type="String" />
<asp:Parameter Name="DistCode" Type="Int64" />
<asp:Parameter Name="M" Type="String" />
<asp:Parameter Name="Model" type="String"/>
<asp:Parameter Name="TSIC" type="String"/>
<asp:Parameter Name="SOLD" type="String"/>
<asp:Parameter Name="USNumber" Type="String" />
<asp:Parameter Name="UserCode" type="String"/>
<asp:Parameter Name="UserName" type="String"/>
<asp:Parameter Name="Zip1" type="String"/>
<asp:Parameter Name="Zip2" Type="String" />
</UpdateParameters>
CREATE PROCEDURE dbo.spWebUpdateUSMTC
@Amount bigint,
@Code nvarchar(8),
@DistCode bigint,
@M nvarchar(5),
@Model nvarchar(75),
@TSIC nvarchar(8),
@SOLD nvarchar(4),
@USNumber nvarchar(20),
@UserCode nvarchar(12),
@UserName nvarchar(75),
@Zip1 nvarchar(10),
@Zip2 nvarchar(10)
as
--set nocount on
begin
update dbo.usmtc set Amount=@Amount, Code=@Code, DistCode=@DistCode,
M=@M, Model=@Model, TSIC=@TSIC, SOLD=@SOLD,
UserCode=@UserCode, UserName=@UserName, [zip-1]=@ZIP1, [ZIP-2]=@ZIP2
WHERE USNumber=@usNUMBER
end
GO
exec spWebUpdate @Amount = 1500, @Code = N'4222', @DistCode = 12431, @M = N'10', @Model = N'XX 22SDF', @SIC = N'33271', @SOLD = N'2006', @UserCode = N'2223', @UserName = N'test test test', @Zip1 = NULL, @Zip2 = NULL, @Zip-1 = N'07866', @Zip-2 = N'18343', @intUSMTCDistCode = N'312600', @DistTerritory_ID = N'17', @USNumber = N'2332a'
what is suprising is that i'm using a datagrid for this, i'm am running a query to fill the grid and this is the update events for it, it is pulling zip-1 and zip-2 (the column names, not param) from the table and filling the grid with them, but when i hit edit and then update, i get the above said error and the trace shows them as NULL. this works from query analyzer correctly.
ASKER
btw the true name of the sp is spwebupdate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes those extra columns are calculated from the source query, the source query returns some calculated fields, and these fields are not in the SP and are not being passed as parameters into this SP so i have no clue why they are showing up in the trace,
it works when i took out the derived values, but i have no clue why it doesn't see they are calulated and not try and send them into the SP
it works when i took out the derived values, but i have no clue why it doesn't see they are calulated and not try and send them into the SP