Solved

procedure or function has too many arguments specified

Posted on 2006-11-15
11
269 Views
Last Modified: 2008-01-09
here's my code:
<UpdateParameters>
        .....UpdateCommand="spWebUpdate" UpdateCommandType="StoredProcedure">


        <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.
0
Comment
Question by:ramrodcar
  • 6
  • 4
11 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17950352
Can you use SQL Profiler to trace what is actually being sent to the SQL Server?
0
 
LVL 10

Expert Comment

by:ibost
ID: 17950541
In your stored proc you declare a variable:
@Mod

but in the update part of the sproc you use this:
Model=@Model

0
 
LVL 29

Expert Comment

by:Nightman
ID: 17950567
It's failing before that - (although you are right about this - it will error out once it get's executed)
0
 

Author Comment

by:ramrodcar
ID: 17950721
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.
0
 

Author Comment

by:ramrodcar
ID: 17950749
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:Nightman
ID: 17950790
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?
0
 

Author Comment

by:ramrodcar
ID: 17950840
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:
0
 

Author Comment

by:ramrodcar
ID: 17950917
<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.
0
 

Author Comment

by:ramrodcar
ID: 17950965
btw the true name of the sp is spwebupdate
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17951084
So why would you have extra parameters passed through - I don't see them defined anywhere in your code. What I can tell you is that if the @Zip-1 and @ZIP-2 columns are calculated columns from the source procedure they will be read-only, which means that they will be NULL on post-back.

What are @intUSMTCDistCode and @DistTerritory_ID - they are also being passed through (but aren't defined) and @TSIC seems to be passed as @SIC.

As a quick solution you could simply add these parameters to the stored procedure until you figure out why the extra params are being passed
0
 

Author Comment

by:ramrodcar
ID: 17951198
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
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now