Irinel,

Thanks a bunch!!!! This fixed my problem.

I hope that you are having a great day,

Les

It's Works, thanks! :->

 

Nacho.

From Chile.

Hello folks

 

I posted a fix for this issue: http://forums.asp.net/1217763/ShowPost.aspx

 

Hope it helps

Greetings my fellow devs,

It's a little comforting to see that I'm not the only one who's been pulling his hair out because of this.
Anyways, I thought I'd share my discoveries... 

I've had the exact same problem.  I tried all of the solutions above, and none of them worked.  I did what the ASP.NET guy recommended, and it worked, but I got a new error: "Original_Name" cannot be null... tried every solution above, with no luck.

I got to wondering though, do the "original" values play some kind of role in all this?  Does the datasource even store the original values?  So I got curious what would happen if I created a SQLDataSource instead of ObjectDataSource...  So I created a SqlDataSource from scratch, and it did not include any "Original_" parameters in its Update statement.

1. So I basically created a new update query in my DataSet, and simply deleted all the "Original_" parameters, leaving the WHERE statement as: "WHERE Id = @Id".  Build.

2. I then re-created the ObjectDataSource and instead of letting it point to the default Update statement, pointed to my new one. (Which I name, "UpdateByID")

3. Make sure there's no "Original_" parameters, also make sure you delete the OldValuesParameterFormatString property from the ObjectDataSource.

Now it works!  Sad workaround, but hey, it works for me.  Does this help anyone?

<josh /> 

In following this thread, I did not see any clear answer about the issue of having primary key fields display in the GridView.  I have a GridView tied to an ObjectDataSource object using BoundFields that is displaying (and updating) rows from a table with two primary key fields.  I do not want either of these two fields to be visible to the user and I certainly don't want them edit-able.  Setting their read-only parameter to true or their visible parameter to false causes an error during the update because it appears that the values of any column set to read only or not visible does not get returned to the stored procedure with the other parameters.  Note that I have identified the primary key fields in the GridViews DataKeys property and they STILL are edit-able in edit mode!  The DataKeys did prevent edits to the DataKeys fields when I used the AutoGenerateColumns option before switching to BoundFields.

My current work around is to turn the fields that I don't want to display/edit into TemplateFields and then turn both the Item and Edit templates into Labels with the visible property set to False (and the Header for the column left blank).  This prevents the fields from either displaying or being edited, but still results in a couple of thin, empty columns on the end of my grid which looks kinda weird, but is better than the other alternatives.

What is the Microsoft official word about having to display (and it appears edit) primary key fields (and other behind-the-scenes fields) in GridView when using BoundFields with a ObjectDataSource?  This does seem like a bug and a significant problem with the GridView.

Well after a few months of using the GridView and ObjectDataSources I think I have finally got my arms around them.  It seems that in order to maintain view state the fields that are being used for two-way binding have to be output to the page as part of the GridView.   This means that visible=false will not work because the state is not saved across postbacks as nothing is output for that field.  The values in the DataKeys are saved as part of the round trip and do not even need to have columns in the GridView but I only want useful Keys in the DataKeys.  To work around this I have been using the HiddenFields (A Lot).  Hidden fields will save there values across the postback, However, they will also create an empty column.  To rid myself of the empty column I include the hidden field with any of the other columns.  I generally make my first column a templated column and then include all the hidden fields there.

Here is a sample where I am using a DetailsView with the same technique:

<asp:DetailsView ID="detailsView" runat="server" DataSourceID="sourceDetails"
 AutoGenerateRows="False" DataKeyNames="eventID">
 <Fields>
  <asp:TemplateField HeaderText="date" SortExpression="date">
    <EditItemTemplate>
    <asp:HiddenField ID="HiddenField5" runat="server" Value='<%# Eval("eventID") %>'></asp:HiddenField>
  <asp:HiddenField ID="HiddenField1" runat="server" Value='<%# Bind("assetID") %>'></asp:HiddenField>
  <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("date") %>'></asp:TextBox>
  </EditItemTemplate>
<ItemTemplate>
  <asp:Label ID="Label1" runat="server" Text='<%# Bind("date") %>'></asp:Label>
  </ItemTemplate>
  </asp:TemplateField>
 <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" />
  <asp:CommandField ShowEditButton="True" />
 </Fields>
 </asp:DetailsView>

Here I only needed to inclued the HiddenFields as part of the EditTemplate as this is what will be used for binding the Update.  This works really well when I use optimistic concurrency with TableAdapters and the ConflictDetection="CompareAllValues" option on the ObjectDataSource.  Now I don't have to show fields that I don't need to have the user edit.

Tyler

Thanks, Tyler!  The HiddenFields sound like a good approach... I will give it a try.

My next (and bigger) issue is the question of how best to pass data from a database to a GridView and back) from a middle tier that is located in a separate project.  In our case, the business logic middle tier is located in a DLL that the web interface calls, but in other scenarios I could see the middle tier being implemented as a web service.

In virtually all the examples I have seen/read, they assume the project is essentially two-tier and the database access objects are all located in the web site project itself (often right on the specific page).  Furthermore, examples almost always use the various configuration wizards and rarely show how to connect data sources dynamically at runtime.  I have tried to pass a DataSet from the middle tier DDL and link it directly to the GridView using DataSource/DataBind.  This causes the data to display correctly in the grid, but nothing happens when I click the edit or delete links (screen just refreshes with the same data, no error message and no edit fields). I am assuming that I have to write some custom code in the various select/update events handlers, but I am at a loss as to what.

I have also tried to pass the DataSet to an ObjectDataSource object located on the same page as the GridView and then link the ODS to the GridView.  The problem is that I can't figure out how to link the DataSet into the ODS using the weird TypeName parameter.

Anybody have any experince / suggestions on this?   Are there any good examples, books, or websites on dealing with DataSets and GridView in a true three-tier web application?

Hi Sasco,

I think you could take a few approaches here.  The first thing to note is that the ObjectDataSource uses the TypeName as the object to call its methods on such as the Select or Update method.  It creates and destroys this object, however, this can be overridden and you can provide your own object if you would like.  I have even used the actual Page object to allow the ObjectDataSource to just call my codebehind in cases where I am caching the changes to the GridView before a final commit.

Anyways, in the case where you want to insert Business Logic you can wrap your DAL in a separate object and then use that object as the interface for the ObjectDataSource.  This will allow you to make changes and run code between the ObjectDataSource and the DAL.  If you have your business logic in another layer you might also want to create new methods on the DAL itself and call those new methods instead.  I have done this with TableAdapters where instead of calling the generated Insert method I create another insert method like IdentityInsert and then run some of my own code to manage the new Identity value and call the original Insert method for the insert.

 

You can find some examples linked from Scott Gu’s blog http://weblogs.asp.net/scottgu/archive/2006/03/30/441465.aspx and he also mentions that some new examples will becoming soon here http://weblogs.asp.net/scottgu/archive/2006/04/18/443300.aspx.

 

Tyler

I am not sure how these other suggestions worked for you. However, I have faced the exact same problem.

My fix for the problem, after some research, was to click on the ObjectDataSource Control on my asp.net page and look at the properties pane. Contained in the properties pane is a property called. "OldValuesParameterFormatString". The value is set by default to "original_{0}".

To correct my update/delete issues I changed this value to "{0}" so it would just use my field name I had in the table. However, if there is some reason that you need to store the old values after the update has committed then you will need to toss them into an array, hidden field, or some other means of storage.

You can read more about this property here

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdatasource.oldvaluesparameterformatstring.aspx

The root of the problem has to do with the TableAdapter and its configuration wizard within the DataSet.xsd.

By default, Visual Studio will enable the "Refresh the data table" option on the "Advanced Options" dialog accessible via a button in the lower left corner of the "Enter a SQL Statement" page of the TableAdapter Configuration Wizard.  If the "Refresh the data table" checkbox is checked, it will append a SELECT statement to the end of the UPDATE and INSERT statements to "retrieve identity column values, default values, and other values calculated by the database" of the row that was just updated.  To do this, it needs the "<PKField>" in addition to the "original_<PKField>" to be sent from the DataGrid control .  When the column property for the primary key is set to ReadOnly = True, the DataGrid will not include the "<PKField>" in its Parameters collection during the Update method call.  (note: <PKField> is the field name of your primary key as in "ID", or "original_ID")

When ever we Update from the DataGrid, the "original_<PKField>" will be sent.  This is good.  It's the additional requirement of sending the "<PKField>" for the additional SELECT statement at the end of the Update query that is messing up our DataGrid's functionality.

In other words, if the DataGrid is bound to the methods in the TableAdapter via an ObjectDataSource, and if the primary key column of the DataGrid is set ReadOnly=True, the DataGrid control will not pass the primary key to the Update and Insert methods of the TableAdapter even though those methods require it.  Instead, it will only include the "original_<PKField>" which it will use to locate the correct record to update.

Disabling the "Refresh the data table" checkbox on the "Advanced Options" dialog during configuration of the TableAdapter makes the most sense to me and has the least impact on design and functionality while still allowing the IDE to generate most of the code.

Some people have suggested changing the OldValuesParameterFormatString of the ObjectDataSource from "original_{0}" to simply "{0}", but this then causes the "original_<PKFieldName>" (i.e. "original_ID"), which is still there in the parameters collection of the ObjectDataSource, to never be initialized (i.e. null will be passed in the Parameters collection during the Update and Insert) by the GridView, resulting in the "Value cannot be null" error if your field cannot be null.  Some people benifited from the OldValuesParameterFormatString fix, but I believe it was because their fields could be null.

Ctut

My stored procedure (generated by the DataAdaptor Wizard):

ALTER PROCEDURE

dbo.NewUpdateCommand

(

@tblname

sysname,

@id int

@instructor nvarchar(MAX),

@Original_id int

)

AS

SET NOCOUNT OFF;

EXEC

('UPDATE [' + @tblname + '] SET [id] = ' + @id + ', [instructor] = ''' + @instructor + ''' WHERE (([id] = ' + @Original_id + '))')

EXEC

('SELECT id, instructor FROM [' + @tblname + '] WHERE (id = ' + @id + ')')

 

I removed all the @id, problem is solved:

ALTER PROCEDURE

dbo.NewUpdateCommand

(

@tblname

sysname,

@instructor

nvarchar(MAX),

@Original_id

int

)

AS

SET NOCOUNT OFF;

EXEC

('UPDATE [' + @tblname + '] SET [instructor] = ''' + @instructor + ''' WHERE (([id] = ' + @Original_id + '))')

EXEC

('SELECT id, instructor FROM [' + @tblname + '] WHERE (id = ' + @Original_id + ')')

 

 

I've posted in my blog the steps to create Update method  in DataAdaptor with parameters to satisfy editing of GridView with ObjectDataSource.

Since so many people seem to have such a huge problem with this I've posted a blog post about the reproduction of this problem. Hopefully we'll see some sort of fix from MS sooner rather than later.

http://weblogs.asp.net/bradygaster/archive/2006/09/26/How-to-Bloody-Your-Forehead.aspx 

Brady,

Have you submit your post to https://connect.microsoft.com/VisualStudio/feedback/?
By the way, I wanted to add comment to your blog post , but "Anonymous comments are disabled" and I didn't find how to login. 

Hi,

 I was also becoming frustrated with this issue and was not willing to implement a workaround.   Ultimately I added the Primary Key to the DataKeyNames property of my GridView and it worked flawlessly. The only hitch that I ran accross was that I had to rebuild the Update function in my dataset, then reassociate it with my ObjectDataSource to prevent an error stating that the function could not be found.  Hope this helps someone else as well.