Empty Dictionary Error on Delete with SqlDataSource - ASP.net ListView

Question

I'm trying to make a simple ListView with optimistic concurrency. It uses the VS-automatically-generated Delete, Insert, Update statements (except 1 change to insert: see code). Insert and Edit work fine and commit to the database. When trying to delete, I get this error:

You have specified that your delete command compares all values on SqlDataSource 'MySourceHere', but the dictionary passed in for values is empty. Pass in a valid dictionary for delete or change your mode to OverwriteChanges.

I've tried just changing "CompareAllValues" to "OverwriteChanges" to postpone concurrency work for later, but that "breaks" Update and Delete commands, which just seem to refresh the ListView with the old values. The only threads I could find that resolved this issue involved changing to "OverwriteChanges" or were people who didn't have a "DataKeyNames" declared. Relevent markup is below (I hope).

Any thoughts?

<asp:SqlDataSource ID="ManageUsersSource" runat="server" 
    ConflictDetection="CompareAllValues" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        DeleteCommand="DELETE FROM [DUSER] WHERE [userid] = ? AND (([username] = ?) OR ([username] IS NULL AND ? IS NULL)) AND (([userpass] = ?) OR ([userpass] IS NULL AND ? IS NULL))" 
    InsertCommand="INSERT INTO [DUSER] ([username], [userpass]) VALUES (?, ?)" 
    OldValuesParameterFormatString="original_{0}" 
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
    SelectCommand="SELECT * FROM [DUSER] ORDER BY [userid] ASC" 
    UpdateCommand="UPDATE [DUSER] SET [username] = ?, [userpass] = ? WHERE [userid] = ? AND (([username] = ?) OR ([username] IS NULL AND ? IS NULL)) AND (([userpass] = ?) OR ([userpass] IS NULL AND ? IS NULL))">
    <DeleteParameters>
        <asp:Parameter Name="original_userid" Type="Int16" />
        <asp:Parameter Name="original_username" Type="String" />
        <asp:Parameter Name="original_username" Type="String" />
        <asp:Parameter Name="original_userpass" Type="String" />
        <asp:Parameter Name="original_userpass" Type="String" />
    </DeleteParameters>
    <InsertParameters>
    <%--This is removed since [userid] is an automatically generated index.--%>
        <%--<asp:Parameter Name="userid" Type="Int16" />--%>
        <asp:Parameter Name="username" Type="String" />
        <asp:Parameter Name="userpass" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="username" Type="String" />
        <asp:Parameter Name="userpass" Type="String" />
        <asp:Parameter Name="original_userid" Type="Int16" />
        <asp:Parameter Name="original_username" Type="String" />
        <asp:Parameter Name="original_username" Type="String" />
        <asp:Parameter Name="original_userpass" Type="String" />
        <asp:Parameter Name="original_userpass" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>
<asp:ListView ID="ListView1" runat="server" DataKeyNames="userid" 
    DataSourceID="ManageUsersSource" InsertItemPosition="LastItem">
    <AlternatingItemTemplate>
        <tr style="background-color:#FFF8DC;">
            <td>
                <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                    Text="Delete" />
                <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
            </td>
            <td>
                <asp:Label ID="useridLabel" runat="server" Text='<%# Eval("userid") %>' />
            </td>
            <td>
                <asp:Label ID="usernameLabel" runat="server" Text='<%# Eval("username") %>' />
            </td>
            <td>
                <asp:Label ID="userpassLabel" runat="server" Text='<%# Eval("userpass") %>' />
            </td>
        </tr>
    </AlternatingItemTemplate>
    <EditItemTemplate>
        <tr style="background-color:#008A8C;color: #FFFFFF;">
            <td>
                <asp:Button ID="UpdateButton" runat="server" CommandName="Update" 
                    Text="Update" />
                <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                    Text="Cancel" />
            </td>
            <td>
                <asp:Label ID="useridLabel1" runat="server" Text='<%# Eval("userid") %>' />
            </td>
            <td>
                <asp:TextBox ID="usernameTextBox" runat="server" 
                    Text='<%# Bind("username") %>' />
            </td>
            <td>
                <asp:TextBox ID="userpassTextBox" runat="server" 
                    Text='<%# Bind("userpass") %>' />
            </td>
        </tr>
    </EditItemTemplate>
    <EmptyDataTemplate>
        <table runat="server" 
            style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
            <tr>
                <td>
                    No data was returned.</td>
            </tr>
        </table>
    </EmptyDataTemplate>
    <InsertItemTemplate>
        <tr style="">
            <td>
                <asp:Button ID="InsertButton" runat="server" CommandName="Insert" 
                    Text="Insert" />
                <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" 
                    Text="Clear" />
            </td>
            <td>
                &nbsp;</td>
            <td>
                <asp:TextBox ID="usernameTextBox" runat="server" 
                    Text='<%# Bind("username") %>' />
            </td>
            <td>
                <asp:TextBox ID="userpassTextBox" runat="server" 
                    Text='<%# Bind("userpass") %>' />
            </td>
        </tr>
    </InsertItemTemplate>
    <ItemTemplate>
        <tr style="background-color:#DCDCDC;color: #000000;">
            <td>
                <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                    Text="Delete" />
                <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
            </td>
            <td>
                <asp:Label ID="useridLabel" runat="server" Text='<%# Eval("userid") %>' />
            </td>
            <td>
                <asp:Label ID="usernameLabel" runat="server" Text='<%# Eval("username") %>' />
            </td>
            <td>
                <asp:Label ID="userpassLabel" runat="server" Text='<%# Eval("userpass") %>' />
            </td>
        </tr>
    </ItemTemplate>
    <LayoutTemplate>
        <table runat="server">
            <tr runat="server">
                <td runat="server">
                    <table ID="itemPlaceholderContainer" runat="server" border="1" 
                        style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;font-family: Verdana, Arial, Helvetica, sans-serif;">
                        <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                            <th runat="server">
                            </th>
                            <th runat="server">
                                userid</th>
                            <th runat="server">
                                username</th>
                            <th runat="server">
                                userpass</th>
                        </tr>
                        <tr ID="itemPlaceholder" runat="server">
                        </tr>
                    </table>
                </td>
            </tr>
            <tr runat="server">
                <td runat="server" 
                    style="text-align: center;background-color: #CCCCCC;font-family: Verdana, Arial, Helvetica, sans-serif;color: #000000;">
                    <asp:DataPager ID="DataPager1" runat="server">
                        <Fields>
                            <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
                                ShowLastPageButton="True" />
                        </Fields>
                    </asp:DataPager>
                </td>
            </tr>
        </table>
    </LayoutTemplate>
    <SelectedItemTemplate>
        <tr style="background-color:#008A8C;font-weight: bold;color: #FFFFFF;">
            <td>
                <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" 
                    Text="Delete" />
                <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />
            </td>
            <td>
                <asp:Label ID="useridLabel" runat="server" Text='<%# Eval("userid") %>' />
            </td>
            <td>
                <asp:Label ID="usernameLabel" runat="server" Text='<%# Eval("username") %>' />
            </td>
            <td>
                <asp:Label ID="userpassLabel" runat="server" Text='<%# Eval("userpass") %>' />
            </td>
        </tr>
    </SelectedItemTemplate>
</asp:ListView>

Did you come across this? http://forums.asp.net/t/1275244.aspx/1 — gbs, Jun 05 '12 at 19:52
Removing the tag and contents resulted in the same error. Explicitly replacing the '?'s in my Delete Statement with the parameters (in the same order) also results in the same error. — CEMBTW, Jun 05 '12 at 20:18

score 4 · Accepted Answer · answered Jun 06 '12 at 15:56

Changing the non-Primary Key fields to use "Bind" instead of "Eval" in my ItemTemplate/AlternatingItemTemplate (just like an Edit Template) was apparently needed to properly pass along the old values.

This was a case of using automatically generated markup without fully understanding it (I still don't, really.)

score 0 · Answer 2 · answered Jun 05 '12 at 20:26

You could try simplifying your Update and Delete Commands. Since userid (your DataKeyName) is automatically generated and, therefore, unique, you do not need the extra parameters.

So, your UpdateCommand would be

"UPDATE [DUSER] SET [username] = ?, [userpass] = ? WHERE [userid] = ?"

with the following parameters:-

<UpdateParameters>
    <asp:Parameter Name="userid" Type="Int16" />
    <asp:Parameter Name="username" Type="String" />
    <asp:Parameter Name="userpass" Type="String" />
</UpdateParameters>

Your DeleteCommand would be

"DELETE FROM [DUSER] WHERE [userid] = ?"

with the following parameters:-

<DeleteParameters>
    <asp:Parameter Name="userid" Type="Int16" />
</DeleteParameters>

Thanks for the suggestion. I double-checked these changes, and Delete operations have the same error. Updates send an error that CommandParameter[3] and [4] are invalid, as well. Also, how does the simpler statements deal with Nulls? I assumed that the generated statements had that junk for a reason. — CEMBTW, Jun 05 '12 at 21:10

Paul C. Shirley · Answer 3 · 2012-06-06T03:53:21.587

Sorry, I forgot to change the ?s and that you want optimistic concurrency.

"UPDATE [DUSER] SET [username] = @username, [userpass] = @userpass WHERE [userid] = @userid And [username] = @original_username And [userpass] = @original_userpass"

"DELETE FROM [DUSER] WHERE [userid] = @userid And [username] = @original_username And [userpass] = @original_userpass"

You will, of course, need to check to see if the row was updated/deleted.

Your InserCommand should also be:-

"INSERT INTO [DUSER] ([username], [userpass]) VALUES (@username, @userpass)"

Assuming username and userpass are set to not allow nulls, which they should be in this case, then the Commands will fail if either parameter is null.

To avoid this, add an <asp:RequiredFieldValidator> to each of the usernameTextBox and userpassTextBox controls to provide client-side validation.

You should also change the IDs of the TextBox controls to match the parameter names.

If you WERE having to deal with null values then you would need the other parts of the generated queries where null comparison is begin used, replacing the ?s with the appropriate parameter.

You can add this as update to your previous answer as you know :) — Wahid Bitar, Jun 06 '12 at 10:43
This code is good, but my "empty dictionary" turned out to be caused elsewhere. (See below) Thanks for the suggestions about validation. I'll get more rep & upvote this. — CEMBTW, Jun 06 '12 at 15:51

Empty Dictionary Error on Delete with SqlDataSource - ASP.net ListView

You have specified that your delete command compares all values on SqlDataSource 'MySourceHere', but the dictionary passed in for values is empty. Pass in a valid dictionary for delete or change your mode to OverwriteChanges.

3 Answers3