CODEWITHSUNDEEP
asp.netsqldatasource
Gloria Santin
Gloria Santin

Reputation: 2136

SqlDataSource insert error - too many arguments

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.

Upvotes: 1

Views: 449

Answers (1)

Gloria Santin
Gloria Santin

Reputation: 2136

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.

Upvotes: 1

Related Questions