OldValuesParameterFormatString SqlDataSource Update

OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated

I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem.

Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True" SortExpression="productName" /> <asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" /> <asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" /> <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" /> <asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" /> <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" /> <asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" /> <asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1%>" DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @.old_productName AND [productURL] = @.old_productURL" InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrder)" ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName%>" SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]" UpdateCommand="UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)" OldValuesParameterFormatString="original_{0}"> <DeleteParameters> <asp:Parameter Name="old_productName" Type="String" /> <asp:Parameter Name="old_productURL" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL" PropertyName="SelectedValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> </InsertParameters> </asp:SqlDataSource>

I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: 

Protected Sub bUpdate_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles bUpdate.Click'if the user has not entered key fields!If missingKeyFields()Then Exit Sub End If Dim count, soAs Integer Dim uDomain, uidAs String'parse out the user id and domain.Try If HttpContext.Current.User.Identity.Name =""Then uid ="unknow" uDomain ="unknown"Else uid = HttpContext.Current.User.Identity.Name uid = uid.Substring(uid.IndexOf("\") + 1) uDomain = uid.Substring(0, uid.IndexOf("\") - 1)End If Catch exAs Exception uid ="unknow" uDomain ="unknown"End Try'fill in the parameters '@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrderMe.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue =Me.tbProductName.TextMe.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue =Me.tbProductURL.TextMe.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue =Me.tbProductSupportURL.TextMe.SqlDataSource1.UpdateParameters.Item("description").DefaultValue =Me.tbProductDescription.TextMe.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = NowMe.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uidMe.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomainMe.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull =True If Integer.TryParse(Me.tbSortOrder.Text, so)Then Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = soElse Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue =""End If'insert the record. count =Me.SqlDataSource1.Update()Me.lCommandInfo.Text = count.ToString +" record was updated."End Sub
 
The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update.

In an attempt to trace down the problem I added this code:

Protected Sub SqlDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Updating Message.Text = e.Command.CommandText Message.Text +="<br>"For lpAs Integer = 0To e.Command.Parameters.Count - 1 Message.Text += e.Command.Parameters(lp).ParameterName +"=" + e.Command.Parameters(lp).Value Message.Text +="<br>"Next End Sub

The message label displays the following text:

UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)@.description=interesting!@.modified=3/15/2007 4:07:31 PM@.userid=unknow@.useridDomain=unknown@.sortOrder=@.productName=ok22@.productURL=http@.productSupportURL=http@.original_productName=ok@.original_productURL=ok

Notice how the "@.original_productURL" has the same value as the "@.original_productName"? This should not be the case. The @.original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @.original_productURL parameter. Since .NET handles the @.original_{0} parameters I don't know why the field isn't being properly updated.

Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem?

Thanks,
Johnny

way back in school i was taught update is the combination of select ,delete & insert....since u r changing the primarykey........select the row in temp variables,make the changes in the varioable,delete original from database & insert the modified row.....

this can be done by stored procedure or a datareader object easily.

|||

That does sound like a work around for my current situation, but I would hate to re-invent the wheel by creating temp variables that mimic the functionality of the OldValues parameter array.

Any other thoughts?

Johnnny

|||if u find another way let me know|||

OK here's the easiest work around I could find.

1) I removed the control binding for the parameters "original_productName" and "original_productURL". This was set to "GridView1.SelectedValue".
2) I manually set the parameters in my button click subroutine:
Me.SqlDataSource1.UpdateParameters.Item("original_productName").DefaultValue =Me.GridView1.SelectedDataKey.Item(0)
Me.SqlDataSource1.UpdateParameters.Item("original_productURL").DefaultValue =Me.GridView1.SelectedDataKey.Item(1)

I hope this helps others that run into issues with using more then 1 field as a primary key.

Now onto my complaint. While I'm willing to be called a newbie at ASP.NET and will bow to anyone who can show me the proper way to use OLDValues, this problem appears to be a bug in the GridViews control or SqlDataSource control. If I had the source code for these controls I would debug them for Microsoft. To summorize the issue...

1) Create a table with 2 fields as the primary key.
2) Add an SqlDataSource control for the table to an ASP.NET page. Be sure to add SQL code for the Insert, Update, Delete methods with parameters that correspond to the OldValuesParameterFormatString.
3) Add a GridView to the ASP.NET page and set its datasource to the SqlDataSource control.
4) In the Insert, Update, and Delete queries of the SqlDatasource control, bind the old value parameters to the GridView's SelectedValue.
5) Add a label to the ASP.NET page.
6) Add a Button to the ASP.NET page.
7) In the Button click procedure add code to update the record and execute the update method of the SqlDataSource. i.e. Sqldatasource1.updateParameters.item(0).defaultValue = "new value". updatedCount = Sqldatasource1.Update().
8) Display the updated count in the label.

When you test the page you'll find that 0 records are updated. This problem seesm to stem from the fact that only the first column of the GridView is used as the selectedValue. Meaning both key fields for the database table have been set to the first column's value of the GridView. Its almost enough to make me start a blog! lol Good luck people :)

Johnny

|||

what is the tbProductName...

is it the id of the some control.