Data Presentation Controls

Re: GridView Update Issues

tlsterling — Tue, 26 Aug 2008 22:22:03 GMT

sbudlong,

I'm not quite following your question. If you're using stored procedures within your data-source and you're experiencing an exception that says, "Procedure or function [name here] has too many arguments specified" - try including all your GridView's BoundField's (use the DataField attribute value) as parameters within your stored procedure.

Hope that helps.

Re: GridView Update Issues

sbudlong — Tue, 26 Aug 2008 21:59:25 GMT

Tracy, I wish I had come across your post a couple of days ago. Life would have been much easier. But what does this mean? "You can go ahead and include their column names as input parameters within your stored procedure (@CURRENT_STATUS_DESCRIPTION) and just do nothing with them." Does this mean that I should use the database table column names in the stored procedure parameters, in the UPDATE parameters on the page, and as the form parameters on the page? I'll try that and see what happens. Thank you very much for confirming what I already sensed was true.

Re: GridView Update Issues

DotNetButterfly — Mon, 03 Dec 2007 04:43:36 GMT

Thanks so much Tracy...you saved my day..the ReadOnly attribute is such a hidden mess!

Re: GridView Update Issues

tlsterling — Thu, 09 Aug 2007 19:07:57 GMT

Thanks for your helpful reply. I, too, initially thought perhaps the problem lie within the table joins I was performing in the select query that was populating my GridView.

However, after frolicking further in the land of SqlDataSources and GridViews, I soon realized it was primarily the indiosynchratic differences between defining one's GridView select/update functions by referencing stored procedures versus embedding SQL statements directly within Visual Studio's SqlDataSource configuration wizard. I'll describe the 'GOTCHAS' I encountered below in the hopes of alleviating a fellow developer's trauma while exploring the wide world of editable GridView's.

Behavior #1: No errors being generated and no exceptions being thrown, but the GridView's update function just doesn't do anything. I encountered this problem when the condition within my Update stored procedure was not being met [WHERE MAIN_ID = @MAIN_ID]. Unfortunately the reason(s) the condition was failing weren't obvious.

Reason #1: As stupid as I feel admitting this, I'm going to do it for the sake of others who could be making a similar error. I wasn't including the MAIN_ID field within my GridView's select query. VS did not generate any type of error in spite of the fact that I was declaring a parameter named @MAIN_ID within my Update function that was not included as part of my GridView's select query, but maybe that's so they can allow for extraneous GridView column additions. At any rate, my 'where' condition failed in this case (obviously!) since it likely had a value of 'NULL' for the @MAIN_ID input parameter value.
Reason #2: If you're referencing a stored procedure for your GridView's select query, you're likely missing the necessary DataKeyNames attribute value within your GridView element. At a minimum, this attribute needs to hold the name of the field(s) you're using within the where clause of your update statement (in my case, I need DataKeyNames="MAIN_ID"). From what I can tell, VS will auto-populate this attribute if you're embedding the SQL directly within its SqlDataSource wizard with the names of any primary key columns it encounters, but no such auto-population will occur, nor will any errors/exceptions be generated when you're referencing your own stored procedure.

Behavior #2: A 'Procedure or function [your procedure's name here] has too many arguments specified.' exception gets thrown when you attempt to update a record within the Gridview.

Another dissimilar behavior characteristic between using embedding SQL versus a referenced stored procedure is VS's ability to automatically determine the fields that should be included as parameters within the GridView's update statement. When using embedded SQL, the update parameters will be automatically limited to those <asp:parameter> elements included within your SqlDataSource's <UpdateParameters> element regardless of the number of BoundFields you have listed within your GridView. However, when referencing a stored procedure every BoundField that does not contain a ReadOnly attribute value of 'True' (and BTW it's default value is 'False') will be included as a parameter and sent to your stored procedure REGARDLESS of what you have listed as <UpdateParameters>. This seems highly un-intuitive to me, too, but it's an easy fix once you know that's what's going on. You can either change each of the BoundFields that you don't want included as update parameters to have a ReadOnly attribute value of 'true' or you can go ahead an include their column names as input parameters within your stored procedure (@CURRENT_STATUS_DESCRIPTION) and just do nothing with them.

As a sidenote, here are a couple of incorrect/outdated solutions I encountered on other forums and wasted time pursuing:

Solution #1: The <NULL> = <NULL> comparison issue. One forum claimed that if you were including nullable columns within your GridView and attempted to update a column with a current value of NULL, the GridView's update procedure would attempt a <NULL> = <NULL> comparison, which would return false and halt the execution. I found no such behavior in my .NET 2.0 Framework and SQL Server 2000 configuration.
Solution #2: Anything to do with the value contained within SqlDataSource's ConflictDetection attribute. After wasting time on this one, I wound up using the default value (easily achievable by just omitting the attribute altogether) and everything's working just fine.
Solution #3: Anything to do with the value contained within SqlDataSource's OldValuesParameterFormatString attribute. [See solution #2].

I hope the vivid and lengthy description of my GridView trial and turbulations aid other developers who often find themselves unable (or unwilling) to read the ADO.NET 2.0 book from cover to cover and opt for a slightly more painful and ad-hoc methodology.

Re: GridView Update Issues

griff_1000 — Thu, 09 Aug 2007 10:02:28 GMT

I'd put your update in a stored procedure too, then you can put it in a try...catch block if you're using SQL Server 2005 and see if there are any errors - there are alternative techniques for other versions of SqlServer. Sounds like it could be foreign key issue.

By the way, your select stored procedure would be more efficient and simpler to read if you used joins instead of subselects, for example:

    select 	distinct m.MAIN_ID, m.CUSTOMER_NAME, m.B_AND_P, m.RPF_RFQ, m.DIVISION, 
    	m.TITLE, m.END_USER, m.CONTRACT_TYPE_ID, 
    	m.PROGRAM_MANAGER, m.CONTRACT_REP, m.PRICER, m.SUBCONTRACT_REP,
    	m.PROJECT_CONTROLLER, m.TASK_VALUE, m.RFQ_EXPECTED_DATE, m.RFQ_DUE_DATE, 
    	m.TARGET_DATE, m.DATE_DUE_TO_CUSTOMER, m.SUBMITTED_DATE, m.CURRENT_STATUS_ID, 
   	m.FINAL_STATUS_ID, m.FINAL_STATUS_DATE,
   	ct.CONTRACT_TYPE_DESCRIPTION,
   	fs.FINAL_STATUS_DESCRIPTION,
   	cs.CURRENT_STATUS_DESCRIPTION
   from 	MAIN m
       inner join CONTRACT_TYPE ct on ct.CONTRACT_TYPE_ID = m.CONTRACT_TYPE_ID
       inner join FINAL_STATUS fs on fs.FINAL_STATUS_ID = m.FINAL_STATUS_ID
       inner join CURRENT_STATUS cs on cs.CURRENT_STATUS_ID = m.CURRENT_STATUS_ID

You may need to use left outer joins instead of inner joins if there will be null values in any of the joined tables.

GridView Update Issues

tlsterling — Thu, 09 Aug 2007 02:30:44 GMT

Here goes;

I'm trying to perform a fairly simple task (or so it would seem...). I have a SQLDataSource on my aspx page and choose it as the datasource for my GridView. Here is my stored procedure that populates my datasource:

1    CREATE  PROCEDURE dbo.proc_get_all_records_matching_criteria
2    
3    AS
4    
5    select 	distinct m.MAIN_ID, m.CUSTOMER_NAME, m.B_AND_P, m.RPF_RFQ, m.DIVISION, 
6    	m.TITLE, m.END_USER, m.CONTRACT_TYPE_ID, 
7    	m.PROGRAM_MANAGER, m.CONTRACT_REP, m.PRICER, m.SUBCONTRACT_REP,
8    	m.PROJECT_CONTROLLER, m.TASK_VALUE, m.RFQ_EXPECTED_DATE, m.RFQ_DUE_DATE, 
9    	m.TARGET_DATE, m.DATE_DUE_TO_CUSTOMER, m.SUBMITTED_DATE, m.CURRENT_STATUS_ID, 
10   	m.FINAL_STATUS_ID, m.FINAL_STATUS_DATE,
11   	(select ct.CONTRACT_TYPE_DESCRIPTION
12   	 from 	CONTRACT_TYPE ct
13   	 where 	ct.CONTRACT_TYPE_ID = m.CONTRACT_TYPE_ID) CONTRACT_TYPE_DESCRIPTION,
14   	(select fs.FINAL_STATUS_DESCRIPTION
15   	 from 	FINAL_STATUS fs
16   	 where 	fs.FINAL_STATUS_ID = m.FINAL_STATUS_ID) FINAL_STATUS_DESCRIPTION,
17   	(select cs.CURRENT_STATUS_DESCRIPTION
18   	 from 	CURRENT_STATUS cs
19   	 where 	cs.CURRENT_STATUS_ID = m.CURRENT_STATUS_ID) CURRENT_STATUS_DESCRIPTION
20   
21   from 	MAIN m
22   GO

As you can see, some of the fields I'm retrieving are from other foreign-key related tables via subqueries. Now, as expected, when this GridView gets displayed and I check the 'Enable Editing' box, those fields are not editable since their ReadOnly property is automatically set to false; so far so good.

Here is my Update SQL statement for the same datasource:

1    UPDATE MAIN SET CUSTOMER_NAME = @CUSTOMER_NAME, 
2    B_AND_P = @B_AND_P, 
3    RPF_RFQ = @RPF_RFQ, 
4    DIVISION = @DIVISION, 
5    TITLE = @TITLE, 
6    END_USER = @END_USER, 
7    PROGRAM_MANAGER = @PROGRAM_MANAGER, 
8    CONTRACT_REP = @CONTRACT_REP, 
9    PRICER = @PRICER, 
10   SUBCONTRACT_REP = @SUBCONTRACT_REP, 
11   PROJECT_CONTROLLER = @PROJECT_CONTROLLER, 
12   TASK_VALUE = convert(money, @TASK_VALUE), 
13   RFQ_EXPECTED_DATE = @RFQ_EXPECTED_DATE, 
14   RFQ_DUE_DATE = @RFQ_DUE_DATE, 
15   TARGET_DATE = @TARGET_DATE, 
16   DATE_DUE_TO_CUSTOMER = @DATE_DUE_TO_CUSTOMER, 
17   SUBMITTED_DATE = @SUBMITTED_DATE, 
18   FINAL_STATUS_DATE = @FINAL_STATUS_DATE 
19   WHERE MAIN_ID = @MAIN_ID

 And here is all my .aspx code (for good measure):

1    <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="ProposalLogResults.aspx.cs" 
	Inherits="ProposalLogResults" Title="Contracts Proposal Log Results" %>
2    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
4        DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" CellPadding="4" ForeColor="#333333"
5         GridLines="None" OnRowUpdated="RowUpdated" OnRowUpdating="RowUpdating" DataKeyNames="MAIN_ID">
6            <Columns>
7                <asp:BoundField DataField="MAIN_ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
8                    SortExpression="MAIN_ID" />
9                <asp:BoundField DataField="CUSTOMER_NAME" HeaderText="CUSTOMER NAME" SortExpression="CUSTOMER_NAME" />
10               <asp:BoundField DataField="B_AND_P" HeaderText="B&P #" SortExpression="B_AND_P" />
11               <asp:BoundField DataField="RPF_RFQ" HeaderText="RPF/RFQ" SortExpression="RPF_RFQ" />
12               <asp:BoundField DataField="DIVISION" HeaderText="DIVISION" SortExpression="DIVISION" />
13               <asp:BoundField DataField="TITLE" HeaderText="TITLE" SortExpression="TITLE" />
14               <asp:BoundField DataField="END_USER" HeaderText="END USER" SortExpression="END_USER" />
15               <asp:BoundField DataField="PROGRAM_MANAGER" HeaderText="PROGRAM MANAGER" SortExpression="PROGRAM_MANAGER" />
16               <asp:BoundField DataField="CONTRACT_REP" HeaderText="CONTRACT REP" SortExpression="CONTRACT_REP" />
17               <asp:BoundField DataField="PRICER" HeaderText="PRICER" SortExpression="PRICER" />
18               <asp:BoundField DataField="SUBCONTRACT_REP" HeaderText="SUBK REP" SortExpression="SUBCONTRACT_REP" />
19               <asp:BoundField DataField="PROJECT_CONTROLLER" HeaderText="PROJECT CONTROLLER" SortExpression="PROJECT_CONTROLLER" />
20               <asp:BoundField DataField="TASK_VALUE" HeaderText="VALUE" SortExpression="TASK_VALUE" />
21               <asp:BoundField DataField="RFQ_EXPECTED_DATE" HeaderText="RFQ EXPECTED DATE" SortExpression="RFQ_EXPECTED_DATE" />
22               <asp:BoundField DataField="RFQ_DUE_DATE" HeaderText="RFQ DUE DATE" SortExpression="RFQ_DUE_DATE" />
23               <asp:BoundField DataField="TARGET_DATE" HeaderText="TARGET DATE" SortExpression="TARGET_DATE" />
24               <asp:BoundField DataField="DATE_DUE_TO_CUSTOMER" HeaderText="DATE DUE TO CUSTOMER"
25                   SortExpression="DATE_DUE_TO_CUSTOMER" />
26               <asp:BoundField DataField="SUBMITTED_DATE" HeaderText="SUBMITTED DATE" SortExpression="SUBMITTED_DATE" />
27               <asp:BoundField DataField="FINAL_STATUS_DATE" HeaderText="FINAL STATUS DATE" SortExpression="FINAL_STATUS_DATE" />
28               <asp:BoundField DataField="CONTRACT_TYPE_DESCRIPTION" HeaderText="CONTRACT TYPE"
29                   ReadOnly="True" SortExpression="CONTRACT_TYPE_DESCRIPTION" />
30               <asp:BoundField DataField="FINAL_STATUS_DESCRIPTION" HeaderText="FINAL STATUS"
31                   ReadOnly="True" SortExpression="FINAL_STATUS_DESCRIPTION" />
32               <asp:BoundField DataField="CURRENT_STATUS_DESCRIPTION" HeaderText="CURRENT STATUS"
33                   ReadOnly="True" SortExpression="CURRENT_STATUS_DESCRIPTION" />
34           </Columns>
35           <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
36           <RowStyle BackColor="#EFF3FB" />
37           <EditRowStyle BackColor="#2461BF" />
38           <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
39           <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
40           <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
41           <AlternatingRowStyle BackColor="White" />
42       </asp:GridView>
43       <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UserConnectionString %>"
44           SelectCommand="proc_get_all_records_matching_criteria" SelectCommandType="StoredProcedure" 
45           UpdateCommand="UPDATE MAIN SET CUSTOMER_NAME = @CUSTOMER_NAME, 
	B_AND_P = @B_AND_P, RPF_RFQ = @RPF_RFQ, DIVISION = @DIVISION, TITLE = @TITLE, END_USER = @END_USER, 
	PROGRAM_MANAGER = @PROGRAM_MANAGER, CONTRACT_REP = @CONTRACT_REP, PRICER = @PRICER, 
	SUBCONTRACT_REP = @SUBCONTRACT_REP, PROJECT_CONTROLLER = @PROJECT_CONTROLLER, 
	TASK_VALUE = convert(money, @TASK_VALUE), RFQ_EXPECTED_DATE = @RFQ_EXPECTED_DATE, 
	RFQ_DUE_DATE = @RFQ_DUE_DATE, TARGET_DATE = @TARGET_DATE, DATE_DUE_TO_CUSTOMER = @DATE_DUE_TO_CUSTOMER, 
	SUBMITTED_DATE = @SUBMITTED_DATE, FINAL_STATUS_DATE = @FINAL_STATUS_DATE WHERE MAIN_ID = @MAIN_ID"
46           OldValuesParameterFormatString="{0}" OnUpdated="sqlupdated" ConflictDetection="CompareAllValues" OnUpdating="SQLUpdating">
47           <UpdateParameters>
48               <asp:Parameter Name="CUSTOMER_NAME" />
49               <asp:Parameter Name="B_AND_P" />
50               <asp:Parameter Name="RPF_RFQ" />
51               <asp:Parameter Name="DIVISION" />
52               <asp:Parameter Name="TITLE" />
53               <asp:Parameter Name="END_USER" />
54               <asp:Parameter Name="PROGRAM_MANAGER" />
55               <asp:Parameter Name="CONTRACT_REP" />
56               <asp:Parameter Name="PRICER" />
57               <asp:Parameter Name="SUBCONTRACT_REP" />
58               <asp:Parameter Name="PROJECT_CONTROLLER" />
59               <asp:Parameter Name="TASK_VALUE" />
60               <asp:Parameter Name="RFQ_EXPECTED_DATE" />
61               <asp:Parameter Name="RFQ_DUE_DATE" />
62               <asp:Parameter Name="TARGET_DATE" />
63               <asp:Parameter Name="DATE_DUE_TO_CUSTOMER" />
64               <asp:Parameter Name="SUBMITTED_DATE" />
65               <asp:Parameter Name="FINAL_STATUS_DATE" />
66               <asp:Parameter Name="MAIN_ID" />
67           </UpdateParameters>
68       </asp:SqlDataSource>
69   </asp:Content>

Everything appears to work perfectly, when I hit a row's edit link; all the appropriate fields become textboxes and I can edit to my heart's content.

When I hit update, however, nothing happens, no errors, no exceptions; my original data reappears unchanged.

I've created handlers for GridView's OnRowUpdated and OnRowUpdating events and placed breakpoints in both that get hit.

I've also created handlers for SqlDataSource's OnUpdated and OnUpdating events which both also get hit.

SqlDataSource's.OnUpdated indicates a 0 for its e.affectedrows, but no exceptions are generated.

One unusual thing I did discover is that SqlDtaSource's.OnUpdating handler is indicating a value of 'Both' for it's

e.Command.UpdatedRowSource.ToString(), which I understand it bad, but I cannot quite wrap my head around MSDN's verbiage on why.

As I continued on my search for the problem, I created a new independent GridView and SqlDataSource with all the same characteristics as my first;

columns allowing null values, primary-key columns, varchar data types, date datatypes, ints, and everything behaved beautifully for a while.

Then I tried adding a sub-query, foreign-key field just like the CURRENT_STATUS_DESCRIPTION within my original SqlDataSource and suddenly it adopted

the exact same behavior as that of my first GridView.

So, in a nutshell, it seems as though the SqlDataSource update ability is quietly disabled when it's dealing with datasets that have fields

supplied via subqueries, but why?

I'm not including those fields within my update parameters, so why wouldn't it just treat them the same as a primary key field and ignore them as

part of the update. And more importantly why is this behavior so silent, generating no exceptions of any kind?

Any insight/help would be greatly appreciated!!