SQLDataSource DeleteCommand to get parameters

Question

I am having some problems passing parameters to a DELETE command, and cant seem to get a good understanding on how it works.

<asp:SqlDataSource ID="sdsPropertyList"
    runat="server"
    ProviderName="<%$ appSettings:ProviderName %>"
    ConnectionString="<%$ appSettings:ConnectionString %>"
    SelectCommand="selPropertyByAcntID"
    SelectCommandType="StoredProcedure"
    OnSelecting="sdsPropertyList_Selecting"
    OnSelected="sdsPropertyList_Selected" 
    DeleteCommand="delPropertyByPropID" 
    DeleteCommandType="StoredProcedure"
    OnDeleting="sdsPropertyList_Deleting"
    OnDeleted="sdsPropertyList_Deleted">
    <SelectParameters>
        <asp:Parameter Name="in_acntID" Type="Int32" DefaultValue="0" />
    </SelectParameters>
    <DeleteParameters>
        <asp:Parameter Name="in_acntID" Type="Int32" DefaultValue="0" />
        <asp:Parameter Name="in_propID" Type="Int32" DefaultValue="0" />
    </DeleteParameters>
</asp:SqlDataSource>

<asp:GridView ID="gvProperty" runat="server" DataSourceID="sdsPropertyList" 
    AutoGenerateColumns="false" CssClass="gvPropList">
    <Columns>
        <asp:BoundField HeaderText="ID" InsertVisible="true" DataField="prop_id" ReadOnly="true" Visible="False" />
        <asp:BoundField HeaderText="Property" DataField="prop_title" 
            ItemStyle-CssClass="gvPropTitle" >
        <ItemStyle CssClass="gvPropTitle" />
        </asp:BoundField>
        <asp:BoundField HeaderText="Units" DataField="unitCount" 
            ItemStyle-CssClass="gvUnitCount" >
        <ItemStyle CssClass="gvUnitCount" />
        </asp:BoundField>
        <asp:BoundField DataField="prop_lastmodified" HeaderText="Last Modified" 
            ItemStyle-CssClass="gvDate" DataFormatString="{0:M/dd/yyyy hh:mm tt}" >
        <ItemStyle CssClass="gvDate" />
        </asp:BoundField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="lbtnEdit" runat="server" CommandName="EditRecord" Text="Edit" CommandArgument='<%# Eval("prop_id") %>'></asp:LinkButton>
                <asp:LinkButton ID="lbtnDelete" runat="server" CommandName="Delete" Text="Delete" CommandArgument='<%# Eval("prop_id") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="AddRecord" Text="Add" CommandArgument='<%# Eval("prop_id") %>'></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <HeaderStyle CssClass="headerPropList"/>
    <RowStyle CssClass="gvPropRow" />
</asp:GridView>

protected void sdsPropertyList_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        int userID = Convert.ToInt32(Page.User.Identity.Name);
        if (userID != 0)
            e.Command.Parameters["in_acntID"].Value = userID;
    }

protected void sdsPropertyList_Deleting(object sender, SqlDataSourceCommandEventArgs e)
    {
        int userID = Convert.ToInt32(Page.User.Identity.Name);
        if (userID != 0)
        {
            e.Command.Parameters["in_acntID"].Value = userID;
        }
    }

The SELECT statement is straightforward which requires one input parameter of userID. However, the DELETE statement, requires 2 parameter inputs. in_acntID = userID in_propID = the boundfield datafield prop_id

What am I doing wrong? and should the CommandName and CommandArgument be passed at the ItemTemplate level if I have it defined at the SqlDataSource level?

I want the delete button to achieve the following:

delete records from table(s) in DB
remove the row from the gridview

UPDATE

After some additional research, I've found that, the NAME for parameters and HeaderText for Boundfield must be the same so that the values within your gridview can be used by the SQL commands of the datasource.

With the exception of the initial select commant, i have removed all the code behind references.

All is working corrently now.

competent_tech competent_tech · Accepted Answer · 2011-12-03T21:10:30

According to the MSDN documentation, you need to specify the DataKeyNames on the gridView:

"Use the DataKeyNames property to specify the field or fields that represent the primary key of the data source. You must set the DataKeyNames property in order for the automatic update and delete features of the GridView control to work. The values of these key fields are passed to the data source control in order to specify the row to update or delete."

SQLDataSource DeleteCommand to get parameters

3 Answers