Question About Using OldValuesParameterFormatString
Apr 13, 2007
I read on the MSDN about how to use the OldValuesParameterFormatString propery to access original parameter values in the sql statements of the SqlDataSource. I used this in the following code:
<asp:SqlDataSource ID="PlannedFeaturesData" runat="server"
ProviderName='System.Data.SqlClient'
ConnectionString="<%$ ConnectionStrings : SiteData %>"
OldValuesParameterFormatString="old_{0}"
SelectCommand="SELECT * FROM ProjectPlannedFeatures WHERE ProjectID=@ProjectID ORDER BY SortID"
DeleteCommand="DELETE ProjectPlannedFeatures WHERE SortID=@SortID AND ProjectID=@ProjectID"
UpdateCommand="UPDATE ProjectPlannedFeatures SET
SortID=@SortID,
Text=@Text,
Status=@Status
WHERE SortID=@old_SortID AND ProjectID=@ProjectID"
InsertCommand="INSERT INTO ProjectPlannedFeatures
(SortID, ProjectID, Text, Status)
VALUES
(@SortID,@ProjectID, @Text, @Status)">
</asp:SqlDataSource>From what I can tell I've implemented this properly, but I get the following error after click on the Update button on a GridView bound to this data source:
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@old_SortID".
Any ideas?
View 6 Replies
Mar 15, 2007
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 sender As Object, ByVal e As System.EventArgs) Handles bUpdate.Click
'if the user has not entered key fields!
If missingKeyFields() Then
Exit Sub
End If
Dim count, so As Integer
Dim uDomain, uid As 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 ex As Exception
uid = "unknow"
uDomain = "unknown"
End Try
'fill in the parameters
'@productName, @productURL, @productSupportURL, @description, @modified, @userid, @useridDomain, @sortOrder
Me.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue = Me.tbProductName.Text
Me.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue = Me.tbProductURL.Text
Me.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue = Me.tbProductSupportURL.Text
Me.SqlDataSource1.UpdateParameters.Item("description").DefaultValue = Me.tbProductDescription.Text
Me.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = Now
Me.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uid
Me.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomain
Me.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull = True
If Integer.TryParse(Me.tbSortOrder.Text, so) Then
Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = so
Else
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 sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
Message.Text = e.Command.CommandText
Message.Text += "<br>"
For lp As Integer = 0 To 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
View 5 Replies
View Related