After deleting a row from grid view How to rebind or fill data to gridview

Question

4.33/5 (3 votes)

See more:

I am using sqlserver, three tier architecture and store procedure for this...

My aspx Page

ASP.NET

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
               onrowcancelingedit="GridView1_RowCancelingEdit"
               onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"
               onrowupdating="GridView1_RowUpdating">
           <Columns>
           <asp:TemplateField HeaderText="ProductId">
           <ItemTemplate>
           <asp:Label ID="lblProductId" runat="server" Text='<%#Eval("ProductId") %>'>'> </asp:Label>
           </ItemTemplate>
           </asp:TemplateField>
            <asp:TemplateField HeaderText="ProductCategory">
           <ItemTemplate>
           <asp:Label ID="lblProcuctCategory" runat="server" Text='<%#Eval("ProductCategory") %>'>'> </asp:Label>
           </ItemTemplate>
           <EditItemTemplate>
           <asp:TextBox ID="txtProductCategory" runat="server" Text='<%#Eval("ProductCategory") %>'>' ></asp:TextBox>
           </EditItemTemplate>
           </asp:TemplateField>
            <asp:TemplateField HeaderText="GirthFrom">
           <ItemTemplate>
           <asp:Label ID="lblGirthFrom" runat="server" Text='<%#Eval("GirthFrom") %>'>'> </asp:Label>
           </ItemTemplate>
           <EditItemTemplate>
           <asp:TextBox ID="txtGirthfrom" runat="server" Text='<%#Eval("GirthFrom") %>'>' ></asp:TextBox>
           </EditItemTemplate>
           </asp:TemplateField>
            <asp:TemplateField HeaderText="GirthTo">
           <ItemTemplate>
           <asp:Label ID="lblGirthTo" runat="server" Text='<%#Eval("GirthTo") %>'>'> </asp:Label>
           </ItemTemplate>
           <EditItemTemplate>
           <asp:TextBox ID="txtGirthTo" runat="server" Text='<%#Eval("GirthTo") %>'>' ></asp:TextBox>
           </EditItemTemplate>
           </asp:TemplateField>
               <asp:TemplateField HeaderText="Action">
                   <ItemTemplate>
                       <asp:LinkButton ID="edit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
                       <asp:LinkButton ID="Delete" runat="server" CommandName="Delete" Text="Delete"></asp:LinkButton>
                   </ItemTemplate>
                   <EditItemTemplate>
                       <asp:LinkButton ID="Update" runat="server" CommandName="Update" Text="Update"></asp:LinkButton>
                       <asp:LinkButton ID="Cancel" runat="server" CommandName="Cancel" Text="cancel"></asp:LinkButton>
                   </EditItemTemplate>
               </asp:TemplateField>
           </Columns>
           </asp:GridView>

My aspx.cs Page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    Creation obj=new Creation();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            Gridviewdatabind();
        }
            
        
    }
    private void Gridviewdatabind()
    {
        DataTable dt = obj.display1();
            GridView1.DataSource = dt;
            GridView1.DataBind();
    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string lblProductId = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblProductId")).Text;
        obj.ProductId = Convert.ToInt32(lblProductId);
        int res = obj.delete();
        Gridviewdatabind();
        
        
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        Gridviewdatabind();
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string lblProductId = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblProductId")).Text;

        string txtProductCategory = ((TextBox)GridView1.Rows[e.RowIndex]
                           .FindControl("txtProductCategory")).Text;
        string txtGirthFrom = ((TextBox)GridView1.Rows[e.RowIndex]
                           .FindControl("txtGirthFrom")).Text;
        string txtGirthTo = ((TextBox)GridView1.Rows[e.RowIndex]
                               .FindControl("txtGirthTo")).Text;
        obj.ProductCategory = txtProductCategory;
        obj.GirthFrom = Convert.ToInt32(txtGirthFrom);
        obj.GirthTo = Convert.ToInt32(txtGirthTo);
        obj.ProductId = Convert.ToInt32(lblProductId);
        int res = obj.update();
        GridView1.EditIndex = -1;
        Gridviewdatabind();

    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        Gridviewdatabind();
    }
    
}

My Datalayer(databaseoperatio class file)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web; 
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DBoperations
/// </summary>
public class DBoperations
{

    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Micro;Integrated Security=True");
    SqlCommand cmd = new SqlCommand();
	public DBoperations()
	{
        cmd.Connection = con;
		//
		// TODO: Add constructor logic here
		//
	}

public DataTable display1(Creation obj)
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_SelectCatgory"; 
        con.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(dt);
        con.Close();
        return dt;
    }

public int DELETECREATION(Creation obj)
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_DeleteCategory";
        cmd.Parameters.AddWithValue("@pdtid", obj.ProductId);
        con.Open();
        int res = cmd.ExecuteNonQuery();
        con.Close();
        return res;
    }

My property layer(class file)

CSS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for Creation
/// </summary>
public class Creation
{
    DBoperations dbo = new DBoperations();
    private string _pdtctg;
    private int _girfrom;
    private int _girto;
    private int _id;


    public string ProductCategory
    {
        get
        {
            return _pdtctg;
        }
        set
        {
            _pdtctg = value;
        }
    }
    public int GirthFrom
    {
        get
        {
            return _girfrom;
        }
        set
        {
            _girfrom = value;
        }
    }
    public int GirthTo
    {
        get
        {
            return _girto;
        }
        set
        {
            _girto = value;
        }
    }
    public int ProductId
    {
        get
        {
            return _id;
        }
        set
        {
            _id = value;
        }
    }




    public Creation()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public DataTable display1()
    {
        DataTable dt = dbo.display1(this);
        return dt;

    }



    public int delete()
    {
        int res = dbo.DELETECREATION(this);
        return res;
    }

My stored Procedures
//stored procedure for select query

SQL

ALTER PROCEDURE SP_SelectCatgory



AS
BEGIN
select * from Categorycrt order by ProductId desc

    END

//stored procedure for delete query

SQL

ALTER PROCEDURE SP_DeleteCategory(@pdtid int)

AS
    BEGIN
    delete from categorycrt where ProductId=@pdtid
    END

The code is working for the intial fill of gridvew and after delete or update of rows the gridview is not rebind ;its not filling back with the new datatable deleted or update, the data is updated and deleted from datatable but it is not filling in grid view an exceptio is thrown

Error shown is : Sqlexception was unhandled by user code::

Procedure SP_SelectCatgory has no parameters and arguments were supplied.

Kindly help me please

Posted 6-May-14 20:58pm

Ajesh1986

Add a Solution

Comments

Laiju k 7-May-14 3:17am

have debugged using breakpoints where did it shows exception

Ajesh1986 7-May-14 3:26am

sda.fill(dt);-----exception is thrown here

MNamrata 7-May-14 3:21am

Have get value in obj.ProductId = Convert.ToInt32(lblProductId);

Is "Procedure SP_SelectCatgory has no parameters and arguments were supplied." this your actual error?

Ajesh1986 7-May-14 3:31am

obj.productId is getting value and the corresponding row is getting deleted or updated and i need to show the gridview again as it is updated...but the sda.fll(dt) of display() is throwing an exception(mentioned in the question).....data is not getting filled in data table.

MNamrata 7-May-14 3:38am

public DataTable display1(Creation obj)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;

4 solutions

Solution 3

Clear command parameters before calling SP.

Try this

public DataTable display1(Creation obj)
{
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_SelectCatgory";
con.Open();
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
con.Close();
return dt;
}

Posted 6-May-14 21:30pm

MNamrata

Updated 6-May-14 21:31pm

v2

Comments

Ajesh1986 7-May-14 9:23am

Eventhough you clear command parameter before calling SP select this won't work

Solution 4

 on row deleting event od grid 

{
//bind grid

GridView1.DataBind();
    }

}

Posted 6-May-14 21:34pm

Kan07

Comments

Ajesh1986 7-May-14 9:25am

this won't work

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Brahmmam · Accepted Answer · 2014-05-06T21:23:00

You are using same SQLCommand class for both deleting and displaying records
while deleting record you have added one parameter these parameter is carry forwarded to display method but in your select SP there is Such Parameter available That is Why you are getting error

Try to do like this in My Datalayer(databaseoperatio class file)

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web; 
using System.Data;
using System.Data.SqlClient;
 
/// <summary>
/// Summary description for DBoperations
/// </summary>
public class DBoperations
{
 
    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Micro;Integrated Security=True");
    SqlCommand cmd = null;
	public DBoperations()
	{
		//
		// TODO: Add constructor logic here
		//
	}
 
public DataTable display1(Creation obj)
    {
        cmd= new SqlCommand();
        cmd.Connection = con;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_SelectCatgory"; 
        con.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        sda.Fill(dt);
        con.Close();
        return dt;
    }
 
public int DELETECREATION(Creation obj)
    {
        cmd= new SqlCommand();
        cmd.Connection = con;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SP_DeleteCategory";
        cmd.Parameters.AddWithValue("@pdtid", obj.ProductId);
        con.Open();
        int res = cmd.ExecuteNonQuery();
        con.Close();
        return res;
    }

DamithSL · Accepted Answer · 2014-05-06T21:22:00

Don't share the connection and command objects. As a best practice, create connection, commands when needed, dispose it ones you done with it. check below code.

C#

public class DBoperations
{
    public const string CONNECTION_STRING = "Data Source=.;Initial Catalog=Micro;Integrated Security=True";

    public DBoperations()
    {
    }

    public static DataTable display1(Creation obj)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        using (SqlCommand cmd = new SqlCommand("SP_SelectCatgory", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_SelectCatgory";
            con.Open();
            DataTable dt = new DataTable();
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                sda.Fill(dt);
                con.Close();
                return dt;
            }
        }
    }

    public static int DELETECREATION(Creation obj)
    {
        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        using (SqlCommand cmd = new SqlCommand("SP_DeleteCategory", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pdtid", obj.ProductId);
            con.Open();
            int res = cmd.ExecuteNonQuery();
            return res;
        }

    }
}

then you can call above methods as

C#

DBoperations.display1(obj);
DBoperations.DELETECREATION(obj);

note that you have not following any naming conventions. try to follow coding standards.

After deleting a row from grid view How to rebind or fill data to gridview

4 solutions

Solution 3

Solution 2

Solution 1

Solution 4

Add your solution here

Preview 0