Automatic Delete, declare scalar variable error

[Sergey]

I have a RadGrid tied to a SqlDataSource, and I'm attempting to do the automatic delete method. However, I'm receiving the error that you must declare the scalar variable @id. I've looked over the code, and compared to the examples, and I just don't see where the error is.

This is for .NET FrameWork 3.5 and with Telerik 2009Q3 release, VS2010 B2. It's also maybe relevant that I'm using the RadAjaxManager to update the grid. 

The error from FireBug console: 41|error|500|Must declare the scalar variable "@id".|

SqlDataSource

<asp:SqlDataSource runat="server" ID="areanoticeds" ConnectionString="<%$ ConnectionStrings:RD %>" CancelSelectOnNullParameter="false" OldValuesParameterFormatString="original_{0}"
      SelectCommand="SELECT * FROM [RCAreaNotices]"
      UpdateCommand="UPDATE [RCAreaNotices] SET [area]=@area, [notice]=@notice WHERE [id]=@id"
      InsertCommand="INSERT INTO [RCAreaNotices] ([area],[notice]) VALUES (@area,@notice)"
      DeleteCommand="DELETE FROM [RCAreaNotices] WHERE [id]=@id">
      <UpdateParameters>
        <asp:Parameter Name="id" DbType="Int16" />
        <asp:Parameter Name="area" DbType="String" Size="3" />
        <asp:Parameter Name="notice" DbType="String" Size="200" />
        <asp:Parameter Name="original_id" DbType="Int16" />
        <asp:Parameter Name="original_area" DbType="String" Size="3" />
        <asp:Parameter Name="original_notice" DbType="String" Size="200" />
      </UpdateParameters>
      <InsertParameters>
        <asp:Parameter Name="area" DbType="String" Size="3" />
        <asp:Parameter Name="description" DbType="String" Size="200" />
      </InsertParameters>
      <DeleteParameters>
        <asp:Parameter Name="id" DbType="Int16" />
      </DeleteParameters>
      </asp:SqlDataSource>

RadGrid control

<telerik:RadGrid runat="server" ID="areanoticegrid" DataSourceID="areanoticeds" AllowAutomaticDeletes="true" AllowAutomaticInserts="true" AllowAutomaticUpdates="true"
       BorderStyle="None" AllowPaging="true" AllowSorting="true">
       <MasterTableView AutoGenerateColumns="false" Width="100%" Caption="Area Notices" CommandItemDisplay="TopAndBottom" DataKeyNames="id" DataSourceID="areanoticeds">
       <NoRecordsTemplate>No area notices in database.</NoRecordsTemplate>
       <Columns>
        <telerik:GridBoundColumn UniqueName="id" DataField="id"  />
        <telerik:GridBoundColumn UniqueName="area" HeaderText="Area" DataField="area" />
        <telerik:GridBoundColumn UniqueName="notice" HeaderText="Notice" DataField="notice" />
        <telerik:GridEditCommandColumn UniqueName="AreaNoticeEdit" HeaderText="Edit Item" ButtonType="ImageButton" EditImageUrl="img/edit.png" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="70px" />
        <telerik:GridButtonColumn UniqueName="AreaNoticeDelete" CommandName="Delete" Text="Delete" HeaderText="Delete?" ButtonType="ImageButton" ImageUrl="img/trash.png" />
       </Columns>
       </MasterTableView>
       <ClientSettings>
            <ClientEvents OnRowDblClick="RowDblClick" />
        </ClientSettings>
       </telerik:RadGrid>

Any thoughts?

Cheers.

[Princy]

Hi,

Try  setting  the DeleteParameter  to original_id as this is the primary key value and is according to the OldValuesParameterFormatString  you have defined.

ASPX:

<asp:SqlDataSource runat="server" ID="areanoticeds" ConnectionString="<%$ ConnectionStrings:RD %>" CancelSelectOnNullParameter="false" OldValuesParameterFormatString="original_{0}"
     DeleteCommand="DELETE FROM [RCAreaNotices] WHERE [id]=@original_id">

<DeleteParameters>
                <asp:Parameter Name="original_id" Type="Int32" />
</DeleteParameters>

Thanks,

Princy

[Sergey]

Yep, changing the Delete parameter and the Delete query fixed it.

I'm definitely confused about the functionality of OldValueParameterFormatString then, is that more clearly explained somewhere in docs (haven't seen it yet)? Also, why is there such a big difference from say the Update statements, which work correctly in their current form (which seems not unsimilar to the delete queries)?

Thanks again for the insight. 

[Princy]

Hi,


Here is an explanation of the use of OldValuesParameterFormatString property:

"In the SqlDataSource control's SQL commands, you use a naming convention to match parameter placeholders to the old values passed into the command. You establish the format of the placeholder name by setting the SqlDataSource control's OldValuesParameterFormatString property. Set theOldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field. For example, if you set theOldValuesParameterFormatString property to "old_{0}", the names of the original-value parameters will resolve to the field name prefixed with "@old_". Consider an update operation that involves a field named LastModifiedDate. The current value for the field is passed in the Values dictionary and the original value for the field is passed in the OldValues dictionary. A parameter named @LastModifiedDate is created to pass the current value and a parameter named@old_LastModifiedDate is created to pass the original value. You can then include both parameters in an SQL statement to differentiate between the current and original values for the field."

"Although it is common for key fields to be read-only, there are valid scenarios where you will want to be able to update fields that are also used to match the data row to update. For example, if you set ReadOnly=false on a field in the GridView's Columns collection that is also set in DataKeyNames, GridView will pass the old value for the field in the Keys dictionary, whereas it will pass the new value for the field in the Values dictionary. In order to differentiate between these two values, you will need to name the parameters differently in the SQL statement, for example:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:Pubs %>"
  SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors]"
  UpdateCommand="UPDATE [authors] SET [au_id] = @au_id, [au_lname] = @au_lname, [au_fname] = @au_fname, [state] = @state WHERE [au_id] = @original_au_id"
  DeleteCommand="DELETE FROM [authors] WHERE [au_id] = @original_au_id"/>
  OldValuesParameterFormatString="original_{0}"

In this example, the parameter name @original_au_id is used to refer to the original value for the key field, and @au_id is used to refer to the new value. TheOldValuesParameterFormatString property of SqlDataSource is also set to a valid .NET Framwork format string to indicate how parameters in the Keys dictionary should be re-named. "


Thanks,
Princy