SqlDataSource insert error - too many arguments

Question

I want to insert a row into an Infragistics grid. It has 2 columns, ID and Name. Both are bound. But when I insert I only want to use the Name column as a parameter. But when I use both, the insertion works. I have the ID as a parameter but I don't use it because it is an identity column. When i only use 'Name', I get the error, too many arguments for the stored procedure.

This is the markup:

<ig:WebHierarchicalDataGrid runat="server" height="600px" width="875px" 
    ClientIDMode="Static" AutoGenerateBands="False" 
    AutoGenerateColumns="False" DataKeyFields="ID"         
    DataMember="SqlDataSource9_DefaultView" StyleSetName="Windows7" 
    ID="wdgPrivileges"
    DataSourceID="WebHierarchicalDataSource7" Key="SqlDataSource9_DefaultView">
  <Columns>
     <ig:BoundDataField DataFieldName="ID" Key="ID" Hidden="true">
        <Header Text="ID" />
        <header text="ID" />
     </ig:BoundDataField>
     <ig:BoundDataField DataFieldName="Name" Key="Name" Width="95%">
        <Header Text="Name" />
        <header text="Name" />
    </ig:BoundDataField>                   
  </Columns>
</ig:WebHierarchicalDataGrid>

<asp:SqlDataSource ID="SqlDataSource13" runat="server" 
    ConnectionString="<%$ ConnectionStrings %>" 
    SelectCommand="GetPrivilege" SelectCommandType="StoredProcedure" 
    UpdateCommand="SetPrivilege" UpdateCommandType="StoredProcedure"
    InsertCommand="InsPrivilege" InsertCommandType="StoredProcedure">       
    <UpdateParameters>
        <asp:Parameter Name="ID" Type="Int32" />         
        <asp:Parameter Name="Name" Type="String" />            
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="Name" Type="String" />                     
    </InsertParameters>
</asp:SqlDataSource>

My stored procedure:

PROCEDURE [dbo].[InsPrivilege]
    @Name varchar(50)
AS
Begin
    INSERT INTO Privilege ([Name], LastUpdate)
    VALUES (@Name, GetDate())
end

I need the ID to be bound for the update SQL command.

Do I have to use it as a parameter in the insert also and just not use it?

UPDATE

<ig:WebHierarchicalDataSource ID="WebHierarchicalDataSource7" runat="server">
   <DataViews>
   <ig:DataView ID="SqlDataSource9_DefaultView" DataMember="DefaultView" 
                    DataSourceID="SqlDataSource13" />           
   </DataViews>       
</ig:WebHierarchicalDataSource>

UPDATE This is the Update and Select stored procedure info...

<asp:SqlDataSource ID="SqlDataSource13" runat="server" 
        ConnectionString="<%$ ConnectionStrings %>" 
        SelectCommand="GetPrivilege" SelectCommandType="StoredProcedure" 
        UpdateCommand="SetPrivilege" UpdateCommandType="StoredProcedure"
        InsertCommand="dmInsPrivilege" InsertCommandType="StoredProcedure">       
        <UpdateParameters>
            <asp:Parameter Name="ID" Type="Int32" />         
            <asp:Parameter Name="Name" Type="String" />            
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="ID" Type="Int32" DefaultValue="0" />
            <asp:Parameter Name="Name" Type="String" />                     
        </InsertParameters>      
</asp:SqlDataSource>

Select Stored Procedure:

PROCEDURE [dbo].[GetPrivilege]
AS
Begin   
    SELECT ID, [Name]
    FROM Privilege with (nolock)    
end

Update Stored Procedure:

PROCEDURE [dbo].[SetPrivilege]
    @ID int,
    @Name varchar(50)
AS
Begin
    UPDATE Privilege 
    SET [Name] = @Name, LastUpdate = GetDate()
    WHERE ID = @ID  
end

Error Message: [SqlException]: Procedure or function InsPrivilege has too many arguments specified.

Show us the full exception message, and show us the UpdPrivileges stored procedure. — John Saunders
– John Saunders, Commented Jun 20, 2015 at 3:09
I think you give us the code of SqlDataSource13, But your grid uses WebHierarchicalDataSource7 ;). — shA.t
– shA.t, Commented Jun 21, 2015 at 9:42
The WebHierarchicalDataSource7 is defined with the DataSourceID of SqlDataSource13. It is above. — Gloria Santin
– Gloria Santin, Commented Jun 22, 2015 at 14:33
Added the Update and Select stored procedures and error message. — Gloria Santin
– Gloria Santin, Commented Jun 22, 2015 at 14:45
I read that using asp:SqlDataSource requires all bound columns to be part of the commands. Because my ID column is a bound column is it required that I use it in the Insert Command, even though I don't need it? — Gloria Santin
– Gloria Santin, Commented Jun 22, 2015 at 14:49

Gloria Santin · Accepted Answer · 2015-06-22 15:15:52Z

1

I found out the problem...it is not SqlDataSource but the Infragistics Control that requires all bound fields in queries. So, because my Id field is bound, I have to use it as a parameter in my insert command even though I don't need it; which is kind of weird. But for anyone else with is problem...I solved it by adding the ID parameter in the insert statement and setting the default value to zero.

answered Jun 22, 2015 at 15:15

Gloria Santin

2,1167 gold badges63 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDataSource insert error - too many arguments

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related