locked
A solution for "Procedure or function has too many arguments specified" when you using SqlDataSource with Stored Procedure RRS feed

  • Question

  • User413138057 posted

    some people sometime have problems in this scenario, but not all the time, so why?

    usually, when the SP's ParamList have the same params as the datacontrol(GridView etc.)'s databinding Fields(Plus DataKeyNames), they'll work perfectly. if not, this error message would show up.

    for more info, please refer to:

    1. How a Data Source Control Creates Parameters for Data-bound Fields:

    http://msdn.microsoft.com/en-us/library/ms228051.aspx

    2. Using Parameters with Data Source Controls:

    http://msdn.microsoft.com/en-us/library/xt50s8kz.aspx

    3. How to troubleshoot 'Procedure or function has too many arguments specified' in asp.net 2.0:

    http://www.whitworth.org/Blog/PermaLink,guid,ee69ddf8-3096-4818-abdb-0542d2fc191e.aspx

    -------------------------------------------

    here's a solution, only using "update" for example purpose, same as insert/delete:

    in the <asp:SqlDataSource ...> section:

    1. please make sure your code works fine with Non-SP situation, that means: UpdateCommandType="Text" UpdateCommand="UPDATE [TableName] SET [Param1]=@Param1, ...", will work fine.

    2. make sure you have the same paramList declared in <UpdateParameters> section as your SP's paramList.

    for example:

                    <UpdateParameters>
                        <asp:Parameter Name="ID" Type="Int32" />
                        <asp:Parameter Name="Price" Type="Decimal" />
                        <asp:Parameter Name="SalePrice" Type="Decimal" />
                    </UpdateParameters>

    but they don't have to have the same paramList as your datacontrol bound fields( it can have more fields such as "description field", but you only want to update part of them, not all of them)

    Add an event handle for your SqlDataSource's Updating event:

    for example:

    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

     protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
     {
      DbParameterCollection CmdParams = e.Command.Parameters;
      ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

      Hashtable ht = new Hashtable();
      foreach (Parameter UpdParam in UpdParams)
          ht.Add(UpdParam.Name, true);

      for (int i = 0; i < CmdParams.Count; i++)
      {
          if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
              CmdParams.Remove(CmdParams[i--]);
      }

     }

    They'll do the normalizing function for your passing-in parameters.

    Hope this helpful ...

    Sunday, June 18, 2006 8:15 PM

All replies

  • User413138057 posted

    Revised #1:

    1. Syntax for SP declared in <asp:SqlDataSource> section:

    <asp:SqlDataSource ID="SqlDataSource1" ...

    UpdateCommand="SP_Name" UpdateCommandType="StoredProcedure" OnUpdating="SqlDataSource1_Updating">

    2. When you use your own code to prepare params rather than by datacontrol(gridview etc.), make sure don't define them twice, for example:

                    <UpdateParameters> 
                        <asp:Parameter Name="ID" Type="Int32" />
                        <asp:Parameter Name="Price" Type="Decimal" />
                        <asp:Parameter Name="SalePrice" Type="Decimal" />
                    </UpdateParameters>

    Params declared in this section will automatically be defined by ASP.NET, so don't define it in your code again, like:

      SqlDataSource1.UpdateParameters.Add(new Parameter("ID", TypeCode.Int32)); 

    <--this will cause define twice ! be careful. you don't need this statement.

      SqlDataSource1.UpdateParameters["ID"].DefaultValue = "1";

    <--you can assign your param with a default value in your code, usually in the ing-events. you only need this assignment statement.

    Monday, June 19, 2006 8:28 PM
  • User413138057 posted

    3. using your own code to prepare params (e.Command.Parameters):

    When you're in the ing-events, you can also access the e.Command.Parameters, they're the parameters the SqlDataSource will finally pass in to your SP.

    1. using the trace code below to make sure what e.Command.Parameters you have right now:

    <%@ Page Trace="true" ... %>

    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
     {
      DbParameterCollection CmdParams = e.Command.Parameters;

      foreach (DbParameter cp in CmdParams)
       Trace.Warn(cp.ParameterName, cp.Value.ToString());

       e.Cancel = true;
     }

    2. if you find out you're missing some params for your SP, or have more params than you need, then you can just add/remove them with:

    Add/Insert Method:

    SqlParameter myParam = new SqlParameter();
    myParam.SqlDbType =  SqlDbType.Int;
    myParam.Direction= ParameterDirection.Input;
    myParam.ParameterName="@ID";
    myParam.Value=1;

    e.Command.Parameters.Add(myParam);
    // --- or ---
    //e.Command.Parameters.Insert(iPos, myParam);

    Remove Method:

    e.Command.Parameters.RemoveAt("@ID");

    That's all, No more mysteries.

    Monday, June 19, 2006 11:56 PM
  • User-1333561775 posted

    Hi,  I am facing the same problem, I tried out so many times, without sp its working fine but with sp its creating the problem. Please check my code below, Am I missing anything?  

     

    <asp:GridView ID="grdCalendar" runat="server" AllowPaging="True" AllowSorting="True"

    DataSourceID="Appointment" Style="" Width="100%" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" DataKeyNames="AppointmentID" EmptyDataText = "No Appointment For Today" OnRowCommand="grdCalendar_RowCommand" >

    <Columns>

    <asp:BoundField DataField="AppointmentID" HeaderText="ID" Visible="False" />

    <asp:TemplateField HeaderText="Title">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Title") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="Detail">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Detail") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Detail") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="Date">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Date","{0:d}") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Date","{0:d}") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="From">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("From") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label4" runat="server" Text='<%# Bind("From") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="To">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("To") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label5" runat="server" Text='<%# Bind("To") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="Priority">

    <EditItemTemplate>

    <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("Priority") %>'></asp:TextBox>

    </EditItemTemplate>

    <ItemTemplate>

    <asp:Label ID="Label6" runat="server" Text='<%# Bind("Priority") %>'></asp:Label>

    </ItemTemplate>

    </asp:TemplateField>

    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

    </Columns>

    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

    <EditRowStyle BackColor="#999999" />

    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

    </asp:GridView>

    <asp:SqlDataSource ID="Appointment" runat="server" ConnectionString="<%$ ConnectionStrings:ConString %>"

    DeleteCommand="spDeleteAppointment" DeleteCommandType="StoredProcedure" InsertCommand="spInsertAppointment"

    InsertCommandType="StoredProcedure" SelectCommand="spAppointmentByDate" SelectCommandType="StoredProcedure"

    UpdateCommand="spUpdateAppointment" UpdateCommandType="StoredProcedure">

    <DeleteParameters>

    <asp:Parameter Name="AppID" Type="String" />

    <asp:Parameter Name="EmpID" Type="String" />

    </DeleteParameters>

    <UpdateParameters>

    <asp:Parameter Name="AppID" Type="String" />

    <asp:Parameter Name="EmpID" Type="String" />

    <asp:Parameter Name="Title" Type="String" />

    <asp:Parameter Name="Detail" Type="String" />

    <asp:Parameter Name="Date" Type="DateTime" />

    <asp:Parameter Name="Type" Type="String" />

    <asp:Parameter Name="To" Type="String" />

    <asp:Parameter Name="From" Type="String" />

    <asp:Parameter Name="Priority" Type="String" />

    </UpdateParameters>

    <SelectParameters>

    <asp:ControlParameter ControlID="Calendar1" DefaultValue="" Name="date"

    PropertyName="SelectedDate" Type="DateTime" />

    </SelectParameters>

     

    Thanks

    Tuesday, September 25, 2007 2:26 AM
  • User-1255064360 posted

    It looks like you don't have your Insert Parameters declared in the markup. You declare the Select, Update, and Delete parameters but not the Insert params.

    Tuesday, December 4, 2007 4:08 PM
  • User156085368 posted

    Uh Yea, I'm having this problem also, and I havent  been able to resolve it. Could you please take a look at my code and see if you can see the problem.

     

             <asp:SqlDataSource ID="sdsRetirementInfo"
                runat="server" OnUpdating=sdsRetirementInfo_Updating
                ConnectionString="<%$ ConnectionStrings:RetirementConnectionString %>"
                SelectCommand="SELECT
                                RetirementInfo.retirementid, RetirementInfo.trf,
                                RetirementInfo.ssn, RetirementInfo.firstname, RetirementInfo.lastname,
                                RetirementInfo.retirementtype,
                                RetirementInfo.schoolid, RetirementInfo.counselorid,
                                [Type of Retirement].[Type of Retirement] AS Type_of_Retirement,
                                Schools.[School Code] + ' - ' + Schools.[School Unit] AS School,
                                Counselors.CounselorName
                              FROM RetirementInfo
                                LEFT OUTER JOIN [Type of Retirement] ON RetirementInfo.RetirementType = [Type of Retirement].[Type of Retirement ID]
                                LEFT OUTER JOIN Schools ON RetirementInfo.schoolid = Schools.[School ID]
                                LEFT OUTER JOIN Counselors ON RetirementInfo.counselorid = Counselors.Counselorid"
                DeleteCommand="DELETE FROM RetirementInfo WHERE (retirementid = @retirementid)"
                UpdateCommand="sp_UpdateRetirementInfo" UpdateCommandType="StoredProcedure">
                <UpdateParameters>
                    <asp:Parameter Name="retirementid" Type="Int32" />
                    <asp:Parameter Name="trf" Type="String"/>
                    <asp:Parameter Name="ssn" Type="String"/>
                    <asp:Parameter Name="firstname" Type="String"/>
                    <asp:Parameter Name="lastname" Type="String" />
                    <asp:Parameter Name="Retirementtype" Type="Int32" />
                    <asp:Parameter Name="schoolid" Type="Int32"/>
                    <asp:Parameter Name="counselorid" Type="Int32"/>
                </UpdateParameters>
                <DeleteParameters>
                    <asp:Parameter Name="retirementid"  Type="Int32" />
                </DeleteParameters>
            </asp:SqlDataSource>

                                <asp:GridView ID="gvRetirementInfo" runat="server" AutoGenerateColumns="False"
                                    DataSourceID="sdsRetirementInfo" DataKeyNames="retirementid"
                                    AllowPaging="True" BackColor="White" BorderColor="#999999"
                                    BorderStyle="None" BorderWidth="1px"
                                    CellPadding="3" GridLines="Vertical"
                                    OnPageIndexChanged="gvRetirementInfo_PageIndexChanged"
                                    AllowSorting="True" Font-Size="X-Small" Font-Names="Verdana"
                                    OnSelectedIndexChanged="gvRetirementInfo_SelectedIndexChanged"
                                    OnRowCancelingEdit="gvRetirementInfo_RowCancelingEdit"
                                    OnRowDeleting="gvRetirementInfo_RowDeleting"
                                    OnRowEditing="gvRetirementInfo_RowEditing">
                                   <AlternatingRowStyle CssClass="AlternatingRowStyle" BackColor="Gainsboro" />
                                    <RowStyle CssClass="RowStyle" BackColor="#EEEEEE" ForeColor="Black" />
                                    <HeaderStyle CssClass="HeaderStyle" BackColor="#000084" Font-Bold="True"
                                        ForeColor="White" />
                                    <SelectedRowStyle CssClass="SelectedRowStyle" BackColor="#008A8C"
                                        Font-Bold="True" ForeColor="White"  />
                                    <Columns>
                                        <asp:TemplateField ShowHeader="False">
                                            <EditItemTemplate>
                                                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
                                                    Text="Update"></asp:LinkButton>
                                                <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
                                                    Text="Cancel"></asp:LinkButton>
                                            </EditItemTemplate>
                                            <ItemTemplate>
                                                <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit"
                                                    Text="Edit"></asp:LinkButton>
                                                <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Select"
                                                    Text="Select"></asp:LinkButton>
                                                <asp:LinkButton ID="LinkButton3" runat="server" CausesValidation="False" CommandName="Delete"
                                                    OnClientClick="return confirm('Are you sure you want to delete this record?');"
                                                    Text="Delete"></asp:LinkButton>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="retirementid">
                                            <EditItemTemplate>
                                                <asp:Label ID="Label1" runat="server" Text='<%# Eval("retirementid") %>'></asp:Label>
                                            </EditItemTemplate>
                                            <ControlStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemTemplate>
                                                <asp:Label ID="Label5" runat="server" Text='<%# Bind("retirementid") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="TRF ID" SortExpression="trf">
                                            <EditItemTemplate>
                                                <asp:TextBox ID="EditTRF" runat="server"
                                                    Text='<%# Bind("[trf]") %>' Font-Size=X-Small
                                                    Font-Names="verdana" Width=48px></asp:TextBox>
                                                <asp:RequiredFieldValidator runat=server
                                                    id=rfvEditTRF ControlToValidate=EditTRF
                                                    ErrorMessage="TRF Number is required field"
                                                    Text="* Required">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ItemTemplate>
                                                <asp:Label ID="Label1" runat="server"
                                                    Text='<%# Bind("[trf]") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="SSN" SortExpression="ssn">
                                            <EditItemTemplate>
                                                <asp:TextBox ID="EditSSN" runat="server" Text='<%# Bind("ssn") %>' MaxLength="4"></asp:TextBox>
                                                <asp:RequiredFieldValidator ID="rfvEditSSN" runat=server
                                                     ControlToValidate="EditSSN"
                                                     ErrorMessage="You must enter last for digits of SSN"
                                                     Text="* Required field">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ControlStyle Font-Names="verdana" Font-Size="X-Small" Width="48px" />
                                            <ItemStyle Wrap="False" />
                                            <ItemTemplate>
                                                <asp:Label ID="Label6" runat="server" Text='<%# Bind("ssn") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="First Name" SortExpression="FirstName">
                                            <EditItemTemplate>
                                                <asp:TextBox ID="EditFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
                                                <asp:RequiredFieldValidator ID=rfvEditFirstName runat=server
                                                    ControlToValidate=EditFirstName
                                                    ErrorMessage="You must enter a first name for the member"
                                                    Text="* Required">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ControlStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemTemplate>
                                                <asp:Label ID="Label7" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
                                            <EditItemTemplate>
                                                <asp:TextBox ID="EditLastName" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
                                                <asp:RequiredFieldValidator ID=rfvEditLastName runat=server
                                                    ControlToValidate=EditLastName
                                                    ErrorMessage="You must enter a last name for the member"
                                                    Text="* Required">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ControlStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemStyle Font-Names="verdana" Font-Size="X-Small" />
                                            <ItemTemplate>
                                                <asp:Label ID="Label8" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                         <asp:TemplateField HeaderText="Type of Retirement"
                                            SortExpression="retirementtype">
                                            <EditItemTemplate>
                                                <asp:DropDownList ID="ddlEditretirementtype" runat="server"
                                                    DataSourceID="SqlDataSource4" DataTextField="Type_of_Retirement"
                                                    DataValueField="retirementtype" Font-Names="Verdana"
                                                    Font-Size="X-Small" SelectedValue='<%# Bind("retirementtype") %>'
                                                    AppendDataBoundItems="True"></asp:DropDownList>
                                                <asp:RequiredFieldValidator ID=rfvddlEditretirementtype runat=server
                                                    ControlToValidate=ddlEditretirementtype
                                                    ErrorMessage="You must select a retirement type for the member"
                                                    Text="* Required">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ControlStyle Font-Names="Verdana" Font-Size="X-Small" />
                                            <ItemStyle Font-Names="Verdana" Font-Size="X-Small" />
                                            <HeaderStyle Font-Names="Verdana" Font-Size="X-Small" />
                                            <ItemTemplate>
                                                <asp:Label ID="Label2" runat="server"
                                                    Text='<%# Eval("Type_of_Retirement") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="School" SortExpression="Schoolid">
                                            <EditItemTemplate>
                                                <asp:DropDownList ID="ddleditschool" DataSourceID="sqlDSSchool"
                                                    DataTextField="school" runat="server" Font-Size=X-Small
                                                    Font-Names="verdana" Width=276px
                                                    DataValueField="schoolid"
                                                    SelectedValue='<%# Bind("Schoolid") %>'>
                                                </asp:DropDownList>
                                            </EditItemTemplate>
                                            <ItemTemplate>
                                                <asp:Label ID="Label3" runat="server"
                                                    Text='<%# Bind("School") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                        <asp:TemplateField HeaderText="Counselor" SortExpression="CounselorName">
                                            <EditItemTemplate>
                                                <asp:DropDownList ID="ddlEditCounselor" runat="server"
                                                    DataTextField="CounselorName" DataValueField="counselorid"
                                                    Font-Size=X-Small Font-Names="verdana" Width=115px
                                                    SelectedValue='<%# Bind("Counselorid") %>'
                                                    DataSourceID="SqlDSCounselors"></asp:DropDownList>
                                                <asp:RequiredFieldValidator runat=server
                                                    id=rfvddlEditCounselor ControlToValidate=ddlEditCounselor
                                                     ErrorMessage="You must select a counselor for the member"
                                                     Text="* Required">
                                                </asp:RequiredFieldValidator>
                                            </EditItemTemplate>
                                            <ItemTemplate>
                                                <asp:Label ID="Label4" runat="server" Text='<%# Bind("CounselorName") %>'></asp:Label>
                                            </ItemTemplate>
                                        </asp:TemplateField>
                                    </Columns>
                                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                                </asp:GridView>

    Stored Procedure
    ALTER PROCEDURE [dbo].[sp_UpdateRetirementInfo]
     @retirementid int,
     @trf nvarchar(7),
     @ssn nvarchar(9),
     @firstname nvarchar(75),
     @lastname nvarchar(75),
     @schoolid int,
     @counselorid int,
     @retirementtype int
    AS
    SET NOCOUNT OFF;

    update RetirementInfo set trf = @trf, ssn = @ssn, firstname = @firstname ,
     lastname = @lastname, schoolid = @schoolid, counselorid = @counselorid,
     retirementtype = @retirementtype
    where retirementid = @retirementid

    if not exists(Select retirementid from retirementstatus where retirementid = @retirementid)
     and not exists(Select retirementid from deceased where retirementid = @retirementid)
    begin
     if @retirementtype = 6
      begin
       INSERT INTO Deceased(retirementid, dte) values (@retirementid, getdate())
      end
     else
      if @retirementtype > 0
       begin
        INSERT INTO RetirementStatus(retirementid, counselorassigned) values (@retirementid, getdate())
       end
    end

     

    Friday, January 18, 2008 11:41 AM
  • User687135667 posted

    Hello everybody...

     I was trying and trying to solve this issue and finally i found the answer..

     In my case, i only change the "Bind" method for "Eval" in designe mode.

    Look and let me know if its works

    Wednesday, May 21, 2008 5:35 PM
  • User-1285090017 posted

    Your great.....its working fine....

     

    Thanks [Yes]

    Monday, June 2, 2008 2:15 AM
  • User1843096716 posted

    When I try to use the routine:

     

    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

     protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
     {
      DbParameterCollection CmdParams = e.Command.Parameters;
      ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

      Hashtable ht = new Hashtable();
      foreach (Parameter UpdParam in UpdParams)
          ht.Add(UpdParam.Name, true);

      for (int i = 0; i < CmdParams.Count; i++)
      {
          if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
              CmdParams.Remove(CmdParams[i--]);
      }

     }

    I get the following error:

     Error    2            No overload for 'ambassadorDetails_ItemInserting' matches delegate 'System.Web.UI.WebControls.DetailsViewInsertEventHandler'            C:\WeDocs\FUND\AmbassadorEditUpdateNew.aspx            71            7            C:\WeDocs\FUND\

     This is how my detailsview is defined:

    <asp:DetailsView id="AmbassadorDetails" runat="server"

    AutoGenerateRows="False" AllowPaging="True" AutoGenerateDeleteButton="True"

    AutoGenerateEditButton="True" AutoGenerateInsertButton="True"

    DataKeyNames="USAmbassadorID" DataSourceID="AmbassadorDataSource"

    OnItemUpdated="ambassadorDetails_ItemUpdated"

    OnItemDeleted="ambassadorDetails_ItemDeleted"

    OnItemInserted="ambassadorDetails_ItemInserted"

    OnItemInserting="ambassadorDetails_ItemInserting">

    ...

    ...

    <HeaderTemplate>

    <%#Eval("USAmbassadorLName") == null ? "Adding New Ambassador" :

    Eval("USAmbassadorLName")%>

    </HeaderTemplate>

    </asp:DetailsView>

    Tuesday, June 17, 2008 10:22 AM
  • User1706965153 posted

    [;) Yeah thats work for me( Bind to Eval)

    Saturday, August 9, 2008 5:08 PM
  • User-1486895313 posted

    What do you mean by "Bind to Eval"?  I am having the same problem.  Stored procedure of function has too many arguments.

    Tuesday, December 9, 2008 12:37 PM
  • User-1985586738 posted

     Holy crap!! Talk about a serious conception F-up!

     In my case, when using Trace in the sqlDataSource's Deleting event, the listed parameters are the one Delete parameter which is OK, but also fields I've defined as DataKeyNames.

    Why in the world would parameters other than the ones clearly specified in the sqlDataSource's respective command be included?? This is causing me some serious headaches, not to mention lose of confidence in this product... hope MS fixes this soon... probably not since apparently this has been issue since 2006.

     Here's a simple way you can [kinda] continue to use your StoredProcedures with the sqlDataSource:

    I'm using the Delete command for this example, but this should work with Insert and Update as well.

    1. Configure your datasource as you would normally using StoredProcedure the DeleteCommandType.
    2. From the Properties pane, click "..." next to the DeleteQuery (Query) property to open up the Command and Parameter Editor.
    3. Refresh your Parameters list and remove any unwanted ones such as RETURN_VALUE. Configure your params as you would normally.
    4. Modify the text in the DELETE command textbox from [SP_MyDeleteStoredProcedure]  to [EXEC SP_MyDeleteStoredProcedure @Param1, @ParamN]. ***
    5. Close the Command and Parameter Editor window.
    6. Finally, back in the Properties pane, change the DeleteCommandType from [StoredProcedure] to [Text].

    *** Note: The parameters in the Parameters list do not need the @, leave the names as they appear. However in the Command text, use the same name, but add @.

     

    This is what my sqlDatasource looks like:

    <asp:SqlDataSource ID="dsCustomerOrders" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    SelectCommand="CustomerOrders_GetByCustomerId" SelectCommandType="StoredProcedure"
    DeleteCommand="EXEC CustomerOrders_DeleteOrder @CustomerOrderId" DeleteCommandType="Text">
    <SelectParameters>
    <asp:ControlParameter ControlID="gvCustomers" Name="CustomerId" PropertyName="SelectedDataKey.Value" Type="Int32" />
    </SelectParameters>
    <DeleteParameters>
    <asp:Parameter DefaultValue="" Name="CustomerOrderId" Type="Int32" />
    </DeleteParameters>

    </asp:SqlDataSource>
     
    Using this technique, you do not need to change the way your fields are bound to your controls, or anything else.
    Hope this helped those of you who are as frustrated as me ;o)

     

    Wednesday, December 10, 2008 10:47 AM
  • User1117012589 posted

    Dos escenarios posibles: 

    Primero: Puedes que estés creando lo necesario a través de código puro.

    Segundo: Hayas creado los objetos necesarios a través de a lgún wizard. En este escenario, normalmente da el mensaje de error que estamos considerando.

    Bién, los objetos Adapter está creado y sus parametros ya han sido creado en su Collection correspondiente.

    Es decir, En SqlDataAdapter1 -> SelectCommand -> Parameters -> Collection

    Entonces, los obejtos parámetros ya están creados y definidos sus tipos, etc.

    Ahora, debes pasarle el valor correspondiente:

    SqlSelectCommand1.Parameters("@fecha_ingreso_inicio").Value() = valor_de_tipo_DateTime

    Debes Considerar el nombre de tú objeto SqlDataAdapter (mi caso SqlDataAdapter1)

    Saludos

    jacobmellado@gmail.com

     

    Monday, February 9, 2009 10:58 AM
  • User575660002 posted

    Probably the cleanest way of getting round the problem is to remove the offending parameters in the Deleting event handler. e.g. the following will require no changes to the Stored Proc and no changes to your data source. Hope this helps, just came up with it now while trying to get around the problem

      Protected Sub sqlTaskList_Deleting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs) Handles sqlTaskList.Deleting
        e.Command.Parameters.RemoveAt("@ParentID")
      End Sub

    Friday, May 15, 2009 3:42 AM
  • User-936878854 posted

    this differes from case to case. When I had the same problem I divide the code:


    cmd.Parameters.Add("@IDP", SqlDbType.VarChar).Value = Session["id"];


    into

    cmd.Parameters.Add("@ID", SqlDbType.VarChar);


    and the rest after the loop statement

    cmd.Parameters["@ID"].Value = Session["id"];


    whereas when I had error put the first code before the loop...


    Wednesday, August 5, 2009 12:21 PM
  • User-1418294201 posted

    It appears that the SQLDataSource (at least in the case of the Delete command and I assume the Update command as well) will include in its auto-generated parameters list, all of the columns which comprise the primary key value for the table -- regardless of whether they are defined in or needed by your SP. Make sure that your SP contains parameters for all of the primary key fields in your database table.

    Thursday, October 8, 2009 2:55 PM
  • User813441264 posted

    Hi, I am havin the same problem but cannot find the  bind ..eval ! can you please guide?

     

    thanks 

    Monday, March 8, 2010 11:05 AM
  • User-1285090017 posted

    protected void GridViewApproved_RowUpdated1(object sender, GridViewUpdatedEventArgs e)
        {
            if (e.Exception != null)
            {
                if (e.Exception.Message != null)
                {
                    
                    e.ExceptionHandled = true;
                    int Status = RedirectString - 1;  //This indicates Status and image id (0 to 4)
                    Response.Redirect("dash_board.aspx?value=" + Status);
                }
            }
        }Hi 

    Hi Raja,

    Sometimes even no problem in code, we get exception, I too faced same problem, the below code solved my problem, see wheter it will do for you...

    If your using Gridview, just do as below code... it worked for me... try it... If it works....please don't ask how? why?  I have no answer...

    protected void GridViewApproved_RowUpdated(object sender, GridViewUpdatedEventArgs e)

        {

            if (e.Exception != null)

            {

                if (e.Exception.Message != null)

                {               

                    e.ExceptionHandled = true;                

                }

            }

        }

    Monday, March 8, 2010 12:08 PM
  • User-1418294201 posted

    Hi hwraja,

    I have never been able to get SQLDatasources, Gridview controls and Stored Procedures to work without some intervention. If you want to use a stored procedure to process insets, updates and deletes, then you will need to intercept and handle the Gridview events yourself. The following code snippet demonstrates intercepting and processing the Gridview RowDeleting Event to execute a stored procedure that deletes row(s) from a database table:

        protected void gridView_Example_RowDeleting(object sender, System.Web.UI.WebControls.GridViewDeleteEventArgs e)
        {
    	  // Create a connection object
            SqlConnection conn = new SqlConnection(“YOUR_APPLICATION_CONNECTSTRING”);
    	  // Create a command object tp execute the stored procedure
            SqlCommand cmd = new SqlCommand("StoredProcedure_For_Deleting_Rows", conn);
    	  // Set the command type
            cmd.CommandType = CommandType.StoredProcedure;
    	  // Create the parameters that the stored procedure expects
            cmd.Parameters.Add("@param_1", SqlDbType.NVarChar).Value = gridView_Example.Rows[e.RowIndex].Cells[1].Text.ToString();
            cmd.Parameters.Add("@param_2", SqlDbType.Int).Value = gridView_Example.Rows[e.RowIndex].Cells[9].Text.ToString();
    	  // Create a parameter for the stored procedure return value
            cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    	  // Create a boolean variable to capture the stored procedure return value
            bool aOK = false;
    	  // Execute the stored procedure within a try/catch block
            try
            {
    		// Open the connection
                conn.Open();
    		// Execute the stored procedure
                cmd.ExecuteNonQuery();
    		// Capture the return parameter value
                aOK = Convert.ToBoolean(cmd.Parameters["@returnValue"].Value.ToString() == "0");
            }
            catch (Exception ex)
            {
                // Do some error processing
            }
            finally
            {
    		// Clean up
                if (conn.State == ConnectionState.Open) { conn.Close(); }
                conn.Dispose();
                cmd.Dispose();
            }
            if (aOK)
            {
                // Do some post-execution stuff if needed
            }
    	  // Cancel the gridview event since we are handling it
            e.Cancel = true;
        }
    

     

     Your other alternative is to use SQL statements within the SQLDatasource and create the parameter to source column matching using the Visual Studio IDE at design time in order to achieve something similar to the following:

    <asp:SqlDataSource ID="Example_SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                                DeleteCommand="DELETE FROM [SomeTable] WHERE Column_1=@param_1" SelectCommand="SELECT [Column_1], [Column_2], [Column_3] FROM [SomeTable] ORDER BY [Column_1]"
                                
                                UpdateCommand="UPDATE [SomeTable] SET Column_2 =@param_2, Column_3=@param_3 WHERE Column_1=@param_1" 
                                OnDataBinding="Page_PreRender">
                                <DeleteParameters>
                                    <asp:Parameter Name="param_1" />
                                </DeleteParameters>
                                <UpdateParameters>
                                    <asp:Parameter Name="param_2" />
                                    <asp:Parameter Name="param_3" />
                                    <asp:Parameter Name="param_1" />
                                </UpdateParameters>
                            </asp:SqlDataSource>
    
    <asp:GridView ID="gridViewExample" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                                                    BorderWidth="0px" CellPadding="0" DataSourceID="Example_SqlDataSource"
                                                    GridLines="None" DataKeyNames="Column_1">
                                                    <Columns>
                                                        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                                                        <asp:BoundField DataField="Column_1" HeaderText="Column One" ReadOnly="True" 
                                                            SortExpression="Column_1" />
                                                        <asp:BoundField DataField="Column_2" HeaderText="Column Two" SortExpression="Column_2" />
                                                        <asp:CheckBoxField DataField="Column_3" HeaderText="Column_3" 
                                                            SortExpression="Column_3" />
                                                    </Columns>
                                                    <FooterStyle BackColor="#CCCCCC" />
                                                    <SelectedRowStyle Font-Bold="True" />
                                                    <HeaderStyle Font-Bold="True" />
                                                    <AlternatingRowStyle BackColor="#CCCCCC" />
                                                </asp:GridView>
    

    -- OR Maybe? --

    This is something I discovered and posted to this problem back in October of 2009:

    It appears that the SQLDataSource (at least in the case of the Delete command and I assume the Update command as well) will include in its auto-generated parameters list, all of the columns which comprise the primary key value for the table -- regardless of whether they are defined in or needed by your SP. Make sure that your SP contains parameters for all of the primary key fields in your database table.

    This is what I have had to do to get my particular problems solved. I do not know if you face similar obstacles or not. I hope this helps you.

    Monday, March 8, 2010 3:43 PM
  • User813441264 posted

    Thanks Guys,

     

    Last part of Longrider's answer pointed  in the right direction for me.

     

    What i found was: First take into account all Primary Keys/ or in case of Gridview "datakeynames" for stored procedure because it will be sending these values to the DB automatically. Secondly your parameter names should match the names of columns. rest these will be picked up automatically  

     

    cheers,

    Wednesday, March 10, 2010 8:45 AM
  • User-788128333 posted

    Many Many thanks avinash i alomst wasted 2 hrs to fix this. 

    Wednesday, March 24, 2010 5:27 PM
  • User-1259781382 posted

    Hi......

    I have problem regarding calling stored procedure.......

    Everytime i make a call to stored procedure in database...it generates exception "Procedure or function has too many arguments specified".....it works somewhere but in sum cases it generates exception.

    Here is my code....


    public DataSet getPropDetails(string strPropId)
        {
            //throw new NotImplementedException();
            DataSet ds = new DataSet();
            try
            {
                dVar.command.Connection = this.openConnection();
                dVar.command.CommandText = "spGetPropertyDetails";
                dVar.command.CommandType = CommandType.StoredProcedure;
                //dVar.command.Parameters.Add("@propertyId", SqlDbType.NVarChar, 50).Value = strPropId;
                SqlParameter param1 = new SqlParameter("@propertyId", SqlDbType.VarChar, 50);
                param1.Value = strPropId;
                dVar.command.Parameters.Add(param1);
                dVar.da.SelectCommand = dVar.command;
                dVar.da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dVar.command.Dispose();
                dVar.da.Dispose();
                dVar.connection.Close();
                dVar.connection.Dispose();
            }
            return ds;
        }

    public DataSet getPropDetails(string strPropId)

        {

            //throw new NotImplementedException();

            DataSet ds = new DataSet();

            try

            {

                dVar.command.Connection = this.openConnection();

                dVar.command.CommandText = "spGetPropertyDetails";

                dVar.command.CommandType = CommandType.StoredProcedure;


                SqlParameter param1 = new SqlParameter("@propertyId", SqlDbType.VarChar, 50);

                param1.Value = strPropId;

                dVar.command.Parameters.Add(param1);


                dVar.da.SelectCommand = dVar.command;

                dVar.da.Fill(ds);

            }

            catch (Exception ex)

            {

                throw ex;

            }

            finally

            {

                dVar.command.Dispose();

                dVar.da.Dispose();

                dVar.connection.Close();

                dVar.connection.Dispose();

            }

            return ds;

        }


    //here is my SQL stored procedure:


    ALTER PROCEDURE [dbo].[spGetPropertyDetails] 

    -- Add the parameters for the stored procedure here

    @propertyId varchar(50)

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;


        -- Insert statements for procedure here

    SELECT tblPropertyInfo.propOthDetails, tblPropertyInfo.propPastOwn, tblPropertyInfo.propBudget, tblPropertyInfo.propCarpetArea, tblPropertyInfo.propVideoLnk,  tblPropertyInfo.propBhkType, tblPropertyAme.propAmeName, tblPropertyVastu.propVastuName, tblPropertyArea.propAreaName, tblPropertyInfo.propertyId, tblPropertyInfo.propAmeId, tblPropertyInfo.propVastuId, tblPropertyInfo.propAreaIdFROM tblPropertyInfo INNER JOIN

     tblPropertyAme ON tblPropertyInfo.propAmeId = tblPropertyAme.propAmeId INNER JOIN

      tblPropertyVastu ON tblPropertyInfo.propVastuId = tblPropertyVastu.propVastuId INNER JOIN

    tblPropertyArea ON tblPropertyInfo.propAreaId = tblPropertyArea.propAreaId

    WHERE (tblPropertyInfo.propertyId = @propertyId);

    END


    What wrong m i doin ?????....waiting for ur reply....

    Regards

    Thursday, April 8, 2010 4:48 AM
  • User-1418294201 posted

    Take a look at the posts:

    10-08-2009, 2:55 PM and 03-08-2010 3:43 PM 

    Hope it helps you.

    Thursday, April 8, 2010 9:56 AM