CODEWITHSUNDEEP
asp.netsql-servergridviewstored-procedures
Azhar Shahid
Azhar Shahid

Reputation: 151

Why record is not deleting from grid

I have used checkbox in gridview.

I am trying to delete checked record from gridview on button click.But it is not deleting record.

I am trying but not getting what I am doing wrong.

Here is my code--

Grid code-

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Name" 
        DataSourceID="SqlDataSource1" 
            style="padding: 0px; margin: 0px; border: thin solid #FF9933; width: 500px;" 
            CellPadding="0" EmptyDataText="No records found" ForeColor="#333333" 
            GridLines="None">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <HeaderStyle BackColor="Moccasin" Width="500px" Font-Bold="True" 
            ForeColor="Black" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#E9E7E2" />
        <SortedAscendingHeaderStyle BackColor="#506C8C" />
        <SortedDescendingCellStyle BackColor="#FFFDF8" />
        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="cbRows" runat="server"  />    
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="chkdelete" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        </Columns>   
    </asp:GridView>
    </div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:HRMSConnectionString %>" 
        SelectCommand="SELECT [Name] FROM [Languages]">
    </asp:SqlDataSource>

my default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    tblAdd.Visible = false;
    Label1.Visible = false;
    GridView1.DataBind();
    if (!Page.IsPostBack)
        {
            fillLanguageGrid();
        }
}

public void fillLanguageGrid()
    {
        GridView1.DataSourceID = "SqlDataSource1";
        GridView1.DataBind();
        GridView1.Visible = true;
    }



protected void btnDelete_Click(object sender, EventArgs e)
{
    foreach (GridViewRow gvrow in GridView1.Rows)
    {
        CheckBox chkdelete = (CheckBox)gvrow.FindControl("chkdelete");
        if (chkdelete.Checked)
        {
            string name= Convert.ToString(GridView1.DataKeys[gvrow.RowIndex].Values["Name"].ToString());
            deleteRecordByName(name);
        }
    }
    fillLanguageGrid();
}

public void deleteRecordByName(string Name)
{
    string strcon = ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString;
    SqlConnection sqlConnection = new SqlConnection(strcon);
    SqlDataAdapter da = new SqlDataAdapter("hrm_DeleteLanguages2", sqlConnection);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand.Parameters.Add(new SqlParameter("@Name",SqlDbType.VarChar));
    da.SelectCommand.Parameters["@Name"].Value= Name;
    sqlConnection.Open();
    da.SelectCommand.ExecuteNonQuery();
    sqlConnection.Dispose();
}

Stored procedure code-

    CREATE PROCEDURE hrm_DeleteLanguages2
       @name varchar(120)        
AS 
BEGIN 


     DELETE 
     FROM   [HRMS].[dbo].[Languages]
     WHERE  
     Name = @name

END

Here is snapshot of grid-

enter image description here

Upvotes: 0

Views: 57

Answers (3)

user3189944
user3189944

Reputation:

Do not bind Grid View on page load event.

When you select Checkbox and click on a button. First executes page load event. On page load selection of all of your Checkbox are removed and then performs your click event code.

Remove Grid view binding from page load. Keep this code on click event.

Upvotes: 1

senthilkumar2185
senthilkumar2185

Reputation: 2566

protected void btnDelete_Click(object sender, EventArgs e)
{
    foreach (GridViewRow gvrow in GridView1.Rows)
    {
        CheckBox chkdelete = (CheckBox)gvrow.FindControl("chkdelete");
        if (chkdelete.Checked)
        {
            string name= Convert.ToString(GridView1.DataKeys[gvrow.RowIndex]["Name"].ToString());
            deleteRecordByName(name);
        }
    }
    fillLanguageGrid();
}

Upvotes: 0

marc_s
marc_s

Reputation: 754298

Why on earth are you creating a SqlDataAdapter and assigning it's SelectCommand to delete something?!?!? Makes no sense at all....

Try something much easier:

public void deleteRecordByName(string Name)
{
    string strcon = ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString;

    // create a SqlConnection and a SqlCommand to delete the data in a using().... block
    using (SqlConnection sqlConnection = new SqlConnection(strcon))
    using (SqlCommand cmd = new SqlCommand("dbo.hrm_DeleteLanguages2", sqlConnection)) 
    {
         // define this to be a stored procedure
         cmd.CommandType = CommandType.StoredProcedure;

         // define the parameter and set its value
         cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar)).Value= Name;

         // open connection, execute DELETE query, close connection
         sqlConnection.Open();
         cmd.ExecuteNonQuery();
         sqlConnection.Dispose();
    }
}

If you insist on using a SqlDataAdapter, then please at least use the appropriate DeleteCommand to delete rows - don't abuse the SelectCommand for deleting data!

Upvotes: 2

Related Questions