Gridview delete/edit not working when using select parameter
- by Brian Carroll
new to ASP.NET.
I created a sqldatasource and set up basic select query (SELECT * FROM Accounts) using the wizard. I then had the sqldatasource wizard create the INSERT, EDIT and DELETE queries. Connected this datasource to a gridview with EDITING and DELETING enabled. Everything works fine. The SELECT query returns all records and I can edit/delete them.
Now I need to send a parameter to the SELECT command to filter the records to those with the user's id (pulled from Membership.GetUser). When I add this parameter, the SELECT command works fine, but the EDIT/DELETE buttons in the gridview no longer work.
No error is generated. The page refreshes but the records were not updated in the database. I don't understand what is wrong.
CODE:
<%
Dim u As MembershipUser
Dim userid As String
u = Membership.GetUser(User.Identity.Name)
userid = u.ProviderUserKey.ToString
SqlDataSource1.SelectParameters("UserId").DefaultValue = userid
%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="UserId" HeaderText="UserId"
SortExpression="UserId" />
<asp:BoundField DataField="AccountName" HeaderText="AccountName"
SortExpression="AccountName" />
<asp:BoundField DataField="DateAdded" HeaderText="DateAdded"
SortExpression="DateAdded" />
<asp:BoundField DataField="LastModified" HeaderText="LastModified"
SortExpression="LastModified" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CheckingConnectionString %>"
DeleteCommand="DELETE FROM [Accounts] WHERE [ID] = @ID"
InsertCommand="INSERT INTO [Accounts] ([UserId], [AccountName], [DateAdded], [LastModified]) VALUES (@UserId, @AccountName, @DateAdded, @LastModified)"
SelectCommand="SELECT * FROM [Accounts] WHERE [UserId] = @UserId"
UpdateCommand="UPDATE [Accounts] SET [UserId] = @UserId, [AccountName] = @AccountName, [DateAdded] = @DateAdded, [LastModified] = @LastModified WHERE [ID] = @ID">
<DeleteParameters>
<asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="UserId" Type="String" />
<asp:Parameter Name="AccountName" Type="String" />
<asp:Parameter Name="DateAdded" Type="DateTime" />
<asp:Parameter Name="LastModified" Type="DateTime" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="UserId" Type="String" />
<asp:Parameter Name="AccountName" Type="String" />
<asp:Parameter Name="DateAdded" Type="DateTime" />
<asp:Parameter Name="LastModified" Type="DateTime" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:Parameter Name="UserId"/>
</SelectParameters>
</asp:SqlDataSource>
