Click here to Skip to main content
15,937,007 members
Please Sign up or sign in to vote.
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
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;

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;
}
 
Share this answer
 
v2
Comments
Ajesh1986 7-May-14 9:23am    
Eventhough you clear command parameter before calling SP select this won't work
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;
    }
 
Share this answer
 
Comments
Ajesh1986 7-May-14 3:50am    
Thank you very much you have solved a headache for me....thnksssssssssss......
Ajesh1986 7-May-14 4:03am    
the code is working absolutely fine ....
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.
 
Share this answer
 
Comments
Ajesh1986 7-May-14 3:50am    
Thank You very much sir for you reply....its working fine now......
 on row deleting event od grid 

{
//bind grid

GridView1.DataBind();
    }

}
 
Share this answer
 
Comments
Ajesh1986 7-May-14 9:25am    
this won't work

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900