delete row from gridview sql

Question

I want to be able to delete a row when I click on the delete button on that gridview. I have the aspx page and the code behind as well as the app code. The DeletePaymentCondition runs the store procedure to delete the row. But somehow the overall code doesnt work

aspx

    <asp:GridView ID="gridview1" runat="server" HorizontalAlign="left" AutoGenerateColumns="false" CssClass="table table-bordered " GridLines="None" 
        AllowSorting="True" OnRowDeleting="OnRowDeleting">  
         <Columns>
             <asp:TemplateField ItemStyle-HorizontalAlign="left" HeaderText="Payment Condition" HeaderStyle-CssClass="OGColor" HeaderStyle-ForeColor="white" SortExpression="monthToQuarters">
                <ItemTemplate>
                      <span style="font-size:12px; color: #2980b9; text-align:left">
                      <asp:Label ID="lblUserId" runat="server" Visible="true" Text="<%# bind('payConditionId')%>"/>
</span>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150"/>           
        </Columns>
    </asp:GridView>

cs



protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblEmpID = (Label)gridPayment.Rows[e.RowIndex].FindControl("lblUserId");  //This is Table Id load on Label1

        int id = Convert.ToInt32(lblEmpID.Text.ToString());


        dsPayment = objcommission.Delete(id);
        gridPayment.DataSource = dsPayment.Tables[0];
        gridPayment.DataBind();

    }

app code

public DataSet DeletePayment(int id)
{
    DataSet dsGetAllPayment;
    dsGetAllPaymentCondition = SqlHelper.ExecuteDataset(OGconnection, CommandType.Text, "Delete FROM tblPay where pay ='" + id + "'");
    return dsGetAllPayment;
}

what is the datatype of "payConditionId" field? and I thought DeletePaymentCondition is having a stored procedure, but it looks like an inline sql statement. — Jeric Cruz
– Jeric Cruz, Commented Apr 26, 2017 at 15:08

Cleptus · Accepted Answer · 2017-04-26 15:24:16Z

1

You shoul execute two different SQL, one for the delete and a new select one to retreive the new data.

The DELETE should be executed using in a NonQuery because it does not return rows (only the number of rows affected).

public DataSet DeletePaymentCondition(int ids)
{
    int rowsAffected = SqlHelper.ExecuteNonQuery(OGconnection, CommandType.Text, "Delete FROM [Accounting].[dbo].[tblPayConditions] where payConditionId ='" + ids + "'");
    DataSet dsGetAllPaymentCondition = SqlHelper.ExecuteDataSet(OGconnection, CommandType.Text, "Select * FROM [Accounting].[dbo].[tblPayConditions]");
    return dsGetAllPaymentCondition;
}

As a good praxys, you should consider changing it into parametrized queries. In this case it is safe because of the integer conversion, but in similar code with string parameters you would be prone to SQL Injection attacks

edited Apr 26, 2017 at 15:24

answered Apr 26, 2017 at 15:18

Cleptus

3,5494 gold badges31 silver badges38 bronze badges

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

3 Comments

chris-crush-code Over a year ago

Haven't done web forms in a very long time but you shouldn't have to rebind the data when clicking delete on the gridviewrow should you? I thought it automatically removed the row and left the rest of the data there.

Charles Xavier Over a year ago

still doenst delete the row..Maybe there is something wrong with my .cs or .aspx code

Cleptus Over a year ago

set a breakpoint and check rowsAffected variable after the ExecuteNonQuery is being run, It should have a value greater than zero. Also check the value of ids before the ExecuteNonQuery. Is there a record with that ID in the table?

Charles Xavier · Accepted Answer · 2017-04-27 20:47:07Z

0

I got the solution. I've made changes to the cs file and as well as the code provided by bradbury9.

protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
    {

        int index = Convert.ToInt32(gridPaymentCondition.DataKeys[e.RowIndex].Value.ToString());

        dsPaymentCondition = objcommission.DeletePaymentCondition(index);
        gridPaymentCondition.DataSource = dsPaymentCondition.Tables[0];

        updatePaymentConditionsWithoutRefresh();
    }

answered Apr 27, 2017 at 20:47

Charles Xavier

1,0453 gold badges15 silver badges34 bronze badges

2 Comments

Cleptus Over a year ago

Glad to help. If the answer helped you, I would suggest marking it as helpful

Charles Xavier Over a year ago

Also I had to put DataKeyNames="lblUserId" in the gridview for it to work. But thanks again!

Collectives™ on Stack Overflow

delete row from gridview sql

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related