Empty dictionary error when deleting using SqlDataSource - ASP.NET ListView

Question

I am trying to create a simple ListView with optimistic concurrency. It uses VS-automatically created commands "Delete", "Paste", "Update" (except for 1 change to be inserted: see code). Insertion and editing work fine and freeze the database. When I try to delete, I get this error:

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

I tried just changing the “CompareAllValues” to “OverwriteChanges” to delay concurrency later, but this breaks the Update and Delete commands, which seem to update the ListView with the old values. The only topics I could find that solved this problem were about switching to OverwriteChanges or there were people who didn't have DataKeyNames announced. Relevant markup below (hopefully).

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> 

Answer 1

Changing the non-primary key fields to use " Binding " instead of " Eval " on my ItemTemplate / AlternatingItemTemplate page (like the editing template) seems to be necessary to properly pass the old values.

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

Answer 2

You can try to simplify the Update and Delete commands. Since userid (your DataKeyName) is automatically generated and therefore unique, you do not need additional parameters.

So your UpdateCommand will 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 team will

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

with the following parameters: -

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

Answer 3

Sorry, I forgot to change the "s" and that you want an 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, of course, will need to check if the row is updated / deleted.

Your InserCommand should also be: -

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

Assuming that username and userpass are set so that they do not allow the NULL values ​​that should be in this case, then Commands will fail if any parameter is null.

To avoid this, add <asp:RequiredFieldValidator> for each userernameTextBox and userpassTextBox control to provide client-side validation.

You must also change the identifiers of the TextBox controls to match the parameter names.

If you need to deal with null values, you will need other parts of the generated queries, where will the null comparison begin, replacing? s to the corresponding parameter.