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 · Accepted Answer · 2011-12-03 21:10:30Z

2

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."

answered Dec 3, 2011 at 21:10

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick Over a year ago

I tried adding the datakeynames DataKeyNames="prop_id, acnt_id", however still not achieving the result I want. When i pass the values to datakeynames, how does this get passed as parameters to the sdsPropertyList_Deleting command?

SelAromDotNet Over a year ago

this answer was the fix for me, I see your solution was due to a different issue, but want to remind people to always include all the datakeys if you want them included as parameters, thanks for this answer!

ashutosh raina · Accepted Answer · 2011-12-03 20:38:46Z

0

e.g. if the id was in a listbox or dropdown

<DeleteParameters>
               <asp:ControlParameter ControlID="controlname" Name="id" PropertyName="SelectedValue" Type="Int32" />
                    </DeleteParameters>

i have used the above successfully for deleting . this could work for textboxes or labels. If you are handling the event , why not just take the entire delete process to the even handler ? Specify the entire sql setup including connection , command execution there . This method has also worked for me .

answered Dec 3, 2011 at 20:38

ashutosh raina

9,36412 gold badges49 silver badges80 bronze badges

Comments

Rick · Accepted Answer · 2011-12-04 10:09:47Z

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.

<asp:SqlDataSource ID="sdsPropertyList"
    runat="server"
    ProviderName="<%$ appSettings:ProviderName %>"
    ConnectionString="<%$ appSettings:ConnectionString %>"
    SelectCommand="selPropertyByAcntID"
    SelectCommandType="StoredProcedure"
    OnSelecting="sdsPropertyList_Selecting"
    DeleteCommand="delPropertyByPropID" 
    DeleteCommandType="StoredProcedure"
    OnDeleted="sdsPropertyList_Deleted" >
    <SelectParameters>
        <asp:Parameter Name="acnt_id" Type="Int32" />
    </SelectParameters>
    <DeleteParameters>
        <asp:Parameter Name="acnt_id" Type="Int32" />
        <asp:Parameter Name="prop_id" Type="Int32" />
    </DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="gvProperty" runat="server" DataSourceID="sdsPropertyList" 
    AutoGenerateColumns="false" CssClass="gvPropList" DataKeyNames="acnt_id, prop_id">
    <Columns>
        <asp:BoundField HeaderText="acnt_id" InsertVisible="true" DataField="acnt_id" ReadOnly="true" Visible="False" />
        <asp:BoundField HeaderText="prop_id" InsertVisible="true" DataField="prop_id" ReadOnly="true" Visible="False" />
        <asp:BoundField HeaderText="Property" DataField="prop_title">
        <ItemStyle CssClass="gvPropTitle" />
        </asp:BoundField>
        <asp:BoundField HeaderText="Units" DataField="unitCount" >
        <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:BoundField HeaderText="Active" DataField="prop_active">
        <ItemStyle CssClass="gvPropActive" />
        </asp:BoundField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="lbtnEdit" runat="server" CommandName="EditRecord" Text="Edit"></asp:LinkButton>
                <asp:LinkButton ID="lbtnDelete" runat="server" CommandName="Delete" Text="Delete"></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="AddRecord" Text="Add"></asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <HeaderStyle CssClass="headerPropList"/>
    <RowStyle CssClass="gvPropRow" />
</asp:GridView>

Collectives™ on Stack Overflow

SQLDataSource DeleteCommand to get parameters

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related