Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
Post your Gridview and SQL Proceedure code.|||SqlDataSource:
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TPCConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:TPCConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters><asp:ParameterName="ID"/>
</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>Stored Procedure:
CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS
UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO
But also fails if I specify ID parameter in SqlDataSpurce--here is the error:
Procedure or function updExceptionList has too many arguments specified.
SqlDataSource:
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:xxxConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters><asp:ParameterName="ID"/>
</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>Stored Procedure:
CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS
UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO
But also fails if I specify ID parameter in SqlDataSpurce--here is the error:
Procedure or function updExceptionList has too many arguments specified.
Change your UpadteParametrs their Control Id's are wronge.
Are u using some DropDownlists inside a Gridview?
|||In what way are they wrong? They are for control Gridview2.
I actually solved this problem by entering the entirety of the stored procedure in the SqlDataSource configuration, which is the most unideal solution I could make work. I do not want any sql at all in my application but it seems that I'm forced to put it there.
|||I got the same error.
As it turned out, the ConflictDetection on my datasource was set to "CompareAllValues" which forces the datasource to supplies all the columns to my stored procdure. Hence, the error because the stored procedure only take one parameter.
My fix, was just change the ConflictDetection to "OverwriteChanges". Then it worked.
NOTE: I did NOT have to write any code to add new parameter or set the parameter's value for the delete command at all.
Regards,
Minh
|||correction on the "NOTE".
I did have to add parameter and value for the stored procdure in the RowDeleting event.
But make sure you don't add the parameters in your designer.
protectedvoid GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e){
foreach (DictionaryEntry entryin e.Keys){
this.SqlDataSource1.DeleteParameters.Add(entry.Key.ToString(), entry.Value.ToString());}
}
|||Minh,
Thank you for looking at the issue. I revisited this page and found that the ConflictDetection parameter was set to "OverwriteChanges" so that doesn't seem to be the issue. I am finding many other gridview / parameter problems, although I've had some better success since this post. I would like to understand why you had to delete all the parameters in code, does the designer not function properly? I'm not really interested in writing code for my next update which has like 20 parameters.
|||
Hi sestyd,
I've had a similar problem before, where the update command is sending more parameters than I have defined in the Sqldatasource. (Assuming you have this connected to a grid view), what it seems to be doing is sending any parameter that is defined in the grid view that is not specified as read only (as well as the data keys).
I pretty much just either made the parameters read only in the grid view (if that was viable) or defined the parameters in the stored procedure, then just ignored them.
BTW here is some code that I wrote that will display all of the parameters and their values in a label on the web page for an update function and stop the function from executing, this helped me work out what was going on.
[VB Code]
Protected Sub MyDataSource_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles MyDataSource.Updating lblTest.Text =""For iAs Integer = 0To e.Command.Parameters.Count - 1Step 1 lblTest.Text &= e.Command.Parameters.Item(i).ParameterName.ToString &" :: " & e.Command.Parameters.Item(i).Value &"<br>"Next e.Cancel =TrueEnd Sub
[/VB Code]
HTH
No comments:
Post a Comment