Unable to update table from grid (Procedure or function spExcelGridTestUpdateTable has too many arguments specified)

Question

On a "Save" command, I'm trying to update multiple edited rows on an Excel like grid, the columns of which look like this:

<Columns>
<telerik:GridNumericColumn DataField="CarID" DataType="System.Int32" HeaderText="ID" 
    SortExpression="CarID" UniqueName="CarID" AutoPostBackOnFilter="true" CurrentFilterFunction="EqualTo" HeaderStyle-Width="100" ItemStyle-Width="100" FilterControlWidth="60" ReadOnly="false"/> 
<telerik:GridBoundColumn DataField="CarMake" DataType="System.String" HeaderText="Car Make" 
    SortExpression="CarMake" UniqueName="CarMake" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderStyle-Width="120" ItemStyle-Width="120" FilterControlWidth="80" /> 
<telerik:GridBoundColumn DataField="CarModel" DataType="System.String" HeaderText="Car Model" 
    SortExpression="CarModel" UniqueName="CarModel" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderStyle-Width="120" ItemStyle-Width="120" FilterControlWidth="80" /> 
<telerik:GridBoundColumn DataField="CarTrim" DataType="System.String" HeaderText="Car Trim" 
    SortExpression="CarTrim" UniqueName="CarTrim" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" HeaderStyle-Width="200" ItemStyle-Width="200" FilterControlWidth="160"/> 
<telerik:GridNumericColumn DataField="CarYear" DataType="System.Int32" HeaderText="Car Year" 
    SortExpression="CarYear" UniqueName="CarYear" AutoPostBackOnFilter="true" CurrentFilterFunction="EqualTo" HeaderStyle-Width="100" ItemStyle-Width="100" FilterControlWidth="60" />

Here's my "Save" command:

Case "Save" 
For Each editedItem As GridEditableItem In RadGridViewExcelGridTest.EditItems 
    Dim newValues As Hashtable = New Hashtable 
    'The GridTableView will fill the values from all editable columns in the hash 
    e.Item.OwnerTableView.ExtractValuesFromItem(newValues, editedItem) 
    SqlDataSourceExcelGridTest.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure 
    SqlDataSourceExcelGridTest.UpdateCommand = "spExcelGridTestUpdateTable" 
    SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarID", DbType.Int32)) 
    SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarMake", DbType.String)) 
    SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarModel", DbType.String)) 
    SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarTrim", DbType.String)) 
    SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarYear", DbType.Int32)) 
    SqlDataSourceExcelGridTest.Update() 
    editedItem.Edit = False 
Next

And my stored procedure looks like this:

ALTER PROCEDURE [dbo].[spExcelGridTestUpdateTable] 
-- Add the parameters for the stored procedure here 
@CarID int, 
@CarMake varchar(100), 
@CarModel varchar(100), 
@CarTrim varchar (100), 
@CarYear int 

AS 
BEGIN 

-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
SET NOCOUNT ON; 

    UPDATE [dbo].[TestTable_Cars]
        SET CarMake=@CarMake, CarModel=@CarModel, CarTrim=@CarTrim, CarYear=@CarYear 
        WHERE CarID=@CarID 
END

The error that I'm getting is Procedure or function spExcelGridTestUpdateTable has too many arguments specified. Most folks getting this error seem to simply have a typo when it comes to one of the parameter names, but I've checked and everything seems to line up. If I execute the SP from SQLServer and provide the parameters (or pass NULL params), the SP runs fine.

you may try changing your data types for your parameters from your grid Add(New Parameter("CarID", DbType.String). Is it a Sql Server DB? — briskovich, Oct 03 '13 at 21:37
@briskovich - Changing the data type to string does not appear to have any effect. Yes, it is Sql Server db. — Karl, Oct 04 '13 at 18:06

score 1 · Answer 1 · answered Oct 03 '13 at 21:42

1

Try adding

SqlDataSourceExcelGridTest.UpdateParameters.Clear()

before

SqlDataSourceExcelGridTest.UpdateParameters.Add(New Parameter("CarID", DbType.Int32))

answered Oct 03 '13 at 21:42

Yuriy Galanter

38,833
15
69
136

Adding the line prevents the error, so thanks for that! But the table doesn't get updated. I've tried a Rebind() statement inside the loop, outside, and commented out - all produce the same result. Any ideas? – Karl Oct 04 '13 at 17:58
When you run it in SSMS with exactly the same parameters as passed from the grid - does the update happens? You can catch exact call to the SP with exact params using SQL Profiler – Yuriy Galanter Oct 04 '13 at 18:03
1

I just noticed, that you're adding parameters, but not assigning them any values. E.g. when you do `.Add(New Parameter("CarID", DbType.Int32))` where do you get actual CarID to pass it in? – Yuriy Galanter Oct 05 '13 at 15:12
When I run a trace, I find I get 100 of this command (there are 100 records in the database): exec spExcelGridTestUpdateTable @CarID=NULL,@CarMake=NULL,@CarModel=NULL,@CarTrim=NULL,@CarYear=NULL So instead of updating the one row with the one change (test case), I'm getting 100 rows set to NULL. But, no records end up as NULL. Furthermore, my SP doesn't update CarID - but the command being executed appears to be trying to do so anyway. For testing, I set all columns to allow NULL. But the results are the same. I am way more confused now. – Karl Oct 05 '13 at 15:19
As I mentioned - you're not assigning any values to your parameters - hence NULLs are being passed. – Yuriy Galanter Oct 05 '13 at 15:24
I thought when using SqlDataSource, UpdateParameter values are assigned auto-magically - so long as the names for the data fields/grid columns are used correctly. Is that not so? – Karl Oct 05 '13 at 15:24
Not if you're adding them manually like you do. Perhaps instead of "Clear/Add" you should examine what's there already in the first place? And I get a feeling you don't need to do this in the loop. Ref http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updateparameters.aspx – Yuriy Galanter Oct 05 '13 at 15:46
If I comment out Clear, Add & Update - the SP doesn't show up in the trace. If I leave Update, I get the error "Procedure or function 'spExcelGridTestUpdateTable' expects parameter '@CarID', which was not supplied", and the trace shows "exec spExcelGridTestUpdateTable", but doesn't include any of the parameters/data fields, and the table doesn't get updated. – Karl Oct 05 '13 at 16:51

Unable to update table from grid (Procedure or function spExcelGridTestUpdateTable has too many arguments specified)

1 Answers1