asked on

Procedure or function sp_Insert_Equipment has too many arguments specified.

Hi all,

im getting this error when trying to insert a record using my asp.net page, code below
i tried debugging by adding an item_inserting event, but i get an error page always and dont know how to stop the command being submitted so i can see the code

sp_Insert_Equipment

USE [ITAPP]
GO
/****** Object:  StoredProcedure [dbo].[sp_Insert_Equipment]    Script Date: 09/06/2012 12:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Insert_Equipment]
@UserID int, 
@MachineName nvarchar(255),
@DecomissionDate nvarchar(255),
@Supplier nvarchar(255), 
@Description nvarchar(255), 
@OrderNo nvarchar(255), 
@AssetTag nvarchar(255), 
@CPU nvarchar(255), 
@RAM nvarchar(255), 
@Dept nvarchar(255), 
@AssystName nvarchar(255), 
@ShippedDate date, 
@SuppliedDate date, 
@Location nvarchar(255),
@SerialNo nvarchar(255), 
@AssetNo nvarchar(255), 
@OS nvarchar(255), 
@IP nvarchar(255), 
@AssetType nvarchar(255), 
@Confidentiality nvarchar(255), 
@Integrity nvarchar(255), 
@Availability nvarchar(255), 
@Total nvarchar(255), 
@Importance nvarchar(255), 
@Threat nvarchar(255), 
@Vulnerability nvarchar(255), 
@Likelihood nvarchar(255), 
@Risk nvarchar(255), 
@Rank nvarchar(255), 
@NewRank nvarchar(255), 
@AssystLive nvarchar(255), 
@Decomissioned nvarchar(255), 
@TrueCrypt nvarchar(255), 
@TrueCryptPassword nvarchar(255)
AS
INSERT INTO [tblEquipment] ([UserID], [MachineName], [DecomissionDate], [Supplier], [Description], [OrderNo], [AssetTag], [CPU], [RAM], [Dept], [AssystName], [ShippedDate], [SuppliedDate], [Location], [SerialNo], [AssetNo], [OS], [IP], [AssetType], [Confidentiality], [Integrity], [Availability], [Total], [Importance], [Threat], [Vulnerability], [Likelihood], [Risk], [Rank], [NewRank], [AssystLive], [Decomissioned], [TrueCrypt], [TrueCryptPassword]) VALUES (@UserID, @MachineName, @DecomissionDate, @Supplier, @Description, @OrderNo, @AssetTag, @CPU, @RAM, @Dept, @AssystName, @ShippedDate, @SuppliedDate, @Location, @SerialNo, @AssetNo, @OS, @IP, @AssetType, @Confidentiality, @Integrity, @Availability, @Total, @Importance, @Threat, @Vulnerability, @Likelihood, @Risk, @Rank, @NewRank, @AssystLive, @Decomissioned, @TrueCrypt, @TrueCryptPassword)

Open in new window

asp.net

    <asp:SqlDataSource ID="dsEqupiment" runat="server" OnUpdating="dsEqupiment_Updating" OnInserting="dsEqupiment_Inserting"
        ConnectionString="<%$ ConnectionStrings:ITAPPConnectionString %>" 
        DeleteCommand="sp_Delete_Equipment" 
        InsertCommand="sp_Insert_Equipment" 
        SelectCommand="sp_Select_Equipment" 
        UpdateCommand="sp_Update_Equipment" DeleteCommandType="StoredProcedure" 
        InsertCommandType="StoredProcedure" SelectCommandType="StoredProcedure" 
        UpdateCommandType="StoredProcedure">
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="UserID" Type="Int32" />
            <asp:Parameter Name="MachineName" Type="String" />
            <asp:Parameter Name="DecomissionDate" Type="String" />
            <asp:Parameter Name="Supplier" Type="String" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="OrderNo" Type="String" />
            <asp:Parameter Name="AssetTag" Type="String" />
            <asp:Parameter Name="CPU" Type="String" />
            <asp:Parameter Name="RAM" Type="String" />
            <asp:Parameter Name="Dept" Type="String" />
            <asp:Parameter Name="AssystName" Type="String" />
            <asp:Parameter DbType="Date" Name="ShippedDate" />
            <asp:Parameter DbType="Date" Name="SuppliedDate" />
            <asp:Parameter Name="Location" Type="String" />
            <asp:Parameter Name="SerialNo" Type="String" />
            <asp:Parameter Name="AssetNo" Type="String" />
            <asp:Parameter Name="OS" Type="String" />
            <asp:Parameter Name="IP" Type="String" />
            <asp:Parameter Name="AssetType" Type="String" />
            <asp:Parameter Name="Confidentiality" Type="String" />
            <asp:Parameter Name="Integrity" Type="String" />
            <asp:Parameter Name="Availability" Type="String" />
            <asp:Parameter Name="Total" Type="String" />
            <asp:Parameter Name="Importance" Type="String" />
            <asp:Parameter Name="Threat" Type="String" />
            <asp:Parameter Name="Vulnerability" Type="String" />
            <asp:Parameter Name="Likelihood" Type="String" />
            <asp:Parameter Name="Risk" Type="String" />
            <asp:Parameter Name="Rank" Type="String" />
            <asp:Parameter Name="NewRank" Type="String" />   
            <asp:Parameter Name="AssystLive" Type="String" />
            <asp:Parameter Name="Decomissioned" Type="Int32" />
            <asp:Parameter Name="TrueCrypt" Type="Int32" />
            <asp:Parameter Name="TrueCryptPassword" Type="String" />
        </InsertParameters>

Open in new window

to keep code posting mininal
all the above parameters are bound to a text field apart from rank and newrank, will this cause these issues?

also do i have to have data for all params in a stored procedure because some wont be filled in all the time...

Thanks

ToddBeaulieu

Hello,

Your argument lists seem to match up from what I can tell.

I wonder if it a NULL issue? If these are nullable, you should specify that in the argument list of the sproc itself. Perhaps the framework is NOT passing parameters that are null and thus the lists don't align?

Give it a shot. Just add "= null" (or "= ' ", "= 'N/A' ", etc.) to each param that you want a default value for if not specified.

ALTER PROCEDURE [dbo].[sp_Insert_Equipment]
@UserID int,
@MachineName nvarchar(255) = null,
@DecomissionDate nvarchar(255),
@Supplier nvarchar(255),

awilderbeast

ASKER

i did the below and got the same error

@UserID int = null, 
@MachineName nvarchar(255) = '',
@DecomissionDate nvarchar(255) = '',
@Supplier nvarchar(255) = '', 
@Description nvarchar(255) = '', 
@OrderNo nvarchar(255) = '', 
@AssetTag nvarchar(255) = '',
@CPU nvarchar(255) = '', 
@RAM nvarchar(255) = '', 
@Dept nvarchar(255) = '', 
@AssystName nvarchar(255) = '', 
@ShippedDate date = null,
@SuppliedDate date =null,
@Location nvarchar(255) = '',
@SerialNo nvarchar(255) = '', 
@AssetNo nvarchar(255) = '', 
@OS nvarchar(255) = '', 
@IP nvarchar(255) = '', 
@AssetType nvarchar(255) = '', 
@Confidentiality nvarchar(255) = '', 
@Integrity nvarchar(255) = '', 
@Availability nvarchar(255) = '', 
@Total nvarchar(255) = '',
@Importance nvarchar(255) = '', 
@Threat nvarchar(255) = '', 
@Vulnerability nvarchar(255) = '', 
@Likelihood nvarchar(255) = '',
@Risk nvarchar(255) = '',
@Rank nvarchar(255) = '',
@NewRank nvarchar(255) = '',
@AssystLive nvarchar(255) = '',
@Decomissioned nvarchar(255) = '',
@TrueCrypt nvarchar(255) = '',
@TrueCryptPassword nvarchar(255) = ''

Open in new window

Alan Warren

Can you execute the stored procedure in Sql Server Management Studio (SSMS)

exec sp_Insert_Equipment 123, 'some machine', NULL,NULL,NULL, -- etc for as many input params as you have defined.

Alan

awilderbeast

ASKER

exec sp_Insert_Equipment 205, 'TEST', NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

Open in new window

1 row inserted verified its status, its there, so its something in the aspx? but what??

Thanks

ToddBeaulieu

Another guess here.

I compared the naming between your sproc and the xml bindings. They seem to match.

Could you verify that your field bindings all match exactly to the xml bindings?

http://stackoverflow.com/questions/9688790/procedure-or-function-has-too-many-arguments-specified-error-when-running-inse

awilderbeast

ASKER

theyre are all setup as below samples

<asp:TextBox ID="txtAssetNo" runat="server" Text='<%# Bind("AssetNo") %>' CssClass="edit_box"></asp:TextBox>

 <asp:TextBox ID="txtSuppliedDate" runat="server" Text='<%# Bind("SuppliedDate", "{0:dd/MM/yyyy}") %>' CssClass="edit_box"></asp:TextBox>

Open in new window

is this incorrect?

ASKER CERTIFIED SOLUTION

ToddBeaulieu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awilderbeast

ASKER

AHAHHHHH!

I found it!
i copy pasta'd my update item template, which had ID in it! which oviously is auto inserted into an insert record, hence "too many arguments"

Thanks for your help!