Asked by:
A solution for "Procedure or function has too many arguments specified" when you using SqlDataSource with Stored Procedure

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 = @retirementidif 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.
- Configure your datasource as you would normally using StoredProcedure the DeleteCommandType.
- From the Properties pane, click "..." next to the DeleteQuery (Query) property to open up the Command and Parameter Editor.
- Refresh your Parameters list and remove any unwanted ones such as RETURN_VALUE. Configure your params as you would normally.
- Modify the text in the DELETE command textbox from [SP_MyDeleteStoredProcedure] to [EXEC SP_MyDeleteStoredProcedure @Param1, @ParamN]. ***
- Close the Command and Parameter Editor window.
- 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_DateTimeDebes Considerar el nombre de tú objeto SqlDataAdapter (mi caso SqlDataAdapter1)
Saludos
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 SubFriday, 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"];
intocmd.Parameters.Add("@ID", SqlDbType.VarChar);
and the rest after the loop statementcmd.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);}}}HiHi 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