Edit / Delete table options

Question

I have a table that has Edit and Delete links in each row. I am supposed to be able to click either Edit or Delete and it would do it. The edit link works but the delete has this error:

Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified.

My tables data source is sqlDataSource1.

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:PayrollSystem_DBConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:PayrollSystem_DBConnectionString.ProviderName %>"           
        SelectCommand="SELECT [UserID], [UserName], [UserPassword], [SecurityLevel] FROM [tblUserLogin]">
    </asp:SqlDataSource>

</div>
    <div align="center">
    <asp:Label ID="Label1" runat="server" Text="Manage Users"></asp:Label>
<p>
    <asp:Label ID="Label2" runat="server" Text="User Name:"></asp:Label>
    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
</p>
<p>
    <asp:Label ID="Label3" runat="server" Text="Password:"></asp:Label>
    <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
</p>
        <p>
            <asp:Label ID="Label4" runat="server" Text="Security Level:"></asp:Label>
            <asp:DropDownList ID="drpdwnlstSecurityLevel" runat="server" 
                onselectedindexchanged="drpdwnlstSecurityLevel_SelectedIndexChanged">
                <asp:ListItem>A</asp:ListItem>
                <asp:ListItem>U</asp:ListItem>
            </asp:DropDownList>
</p>
        <p>
            <asp:Button ID="btnAddUser" runat="server" onclick="btnAddUser_Click1" 
    Text="Add User" /> 


</p>
        <p>
            <asp:Label ID="lblError" runat="server"></asp:Label>
</p>

    </div>
<p>
    &nbsp;</p>
            <div align="center">
<asp:GridView ID="tblUserLogin" runat="server" AutoGenerateColumns="False" 
    DataSourceID="AccessDataSource1">
    <Columns>
        <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" 
            SortExpression="UserID"></asp:BoundField>
        <asp:BoundField DataField="UserName" HeaderText="UserName" 
            SortExpression="UserName"></asp:BoundField>
        <asp:BoundField DataField="UserPassword" HeaderText="UserPassword" 
            SortExpression="UserPassword"></asp:BoundField>
        <asp:BoundField DataField="SecurityLevel" HeaderText="SecurityLevel" 
            SortExpression="SecurityLevel"></asp:BoundField>
        <asp:CommandField ShowEditButton="True"></asp:CommandField>
        <asp:CommandField ShowDeleteButton="True"></asp:CommandField>




    </Columns>
</asp:GridView>
                </form>
</body>

Answer 1

The table doesn't have a primary key, that's why you could not autogenerate the update and delete statments.

It's probably something like this:

<asp:SqlDataSource
    id="AccessDataSource1"
    runat="server"
    DataSourceMode="DataSet"
    ConnectionString="<%$ ConnectionStrings:PayrollSystem_DBConnectionString %>"  
    SelectCommand="SELECT [UserID], [UserName], [UserPassword], [SecurityLevel] FROM tblUserLogin"
    DeleteCommand="DELETE FROM [tblUserLogin] WHERE UserID=@UserID">
 <DeleteParameters>
    <asp:Parameter Name="UserID" Type="Int32" />
 </DeleteParameters>

</asp:SqlDataSource>

You have to add a DeleteCommand to your SqlDataSource. Of course you have to change the ConnectionString and the tableNames. In your DataGridView you have a CommandField to show the DeleteButton. That's why your SqlDataSource requires a DeleteCommand.

If you want to do it by the wizard, you can configure the SqlDataSource by clicking on the little arrow on top right. Under advanced options you can generate insert, update and delete commands, because you probably need them as well.

There is a nice tutorial about SqlDataSource