Click here to Skip to main content
15,880,651 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying an editable grid but it showing some error which I cant trace..please help me .I am getting these errors when...

CSS
Update
Additional information: Procedure or function sp_neetha has too many arguments specified.

Delete

Additional information: The connection was not closed. The connection's current state is open.



my code is here...

MY STORED PROCEDURE FOR TABLE NAME NEETHA( PLS READ MY DOUBTS WHICH IS COMMENTED)

SQL
create procedure sp_neetha (@id int ,@school nchar(10),@office nchar(10),@flag nchar(10))
as
begin
if(@flag='insert')
begin
insert into neetha (id,school ,office) values(@id,@school,@office)
end
if(@flag='delete')
begin
delete from neetha where id=@id
end
if(@flag='select')
begin
select * from neetha
end
if(@flag='update')
begin
update neetha set school=@school,office=@office where id=@id
end
end



C# CODE
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace joins
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {


            if(!IsPostBack)
            {


                grid();
            }
        }

        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\joins\joins\App_Data\Database1.mdf;Integrated Security=True");
        SqlCommand cmd = new SqlCommand();


        public void grid()//HERE AM GETTING ERRORS..should we pass all the values in in select query? I gave cmd.Parameters.AddWithValue("@flag", "select"); only but showing expected parameter like... y?
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id",DbType.Int32);
            cmd.Parameters.AddWithValue("@school", DbType.String);
            cmd.Parameters.AddWithValue("@office", DbType.String);
            cmd.Parameters.AddWithValue("@flag", "select");
            
            cmd.ExecuteNonQuery();
            
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt=new DataTable();
            da.Fill(dt);
            con.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();

          

        }



        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
          
            grid();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {

            int id =   Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());


            TextBox txtid = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditid");
            TextBox txtschool = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditscool");
            TextBox txtoffs = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txteditoffs");
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", txtid.Text);
            cmd.Parameters.AddWithValue("@school", txtschool.Text);
            cmd.Parameters.AddWithValue("@office", txtoffs.Text);

            cmd.Parameters.AddWithValue("@flag","update");

            cmd.ExecuteNonQuery();
            con.Close();
            GridView1.EditIndex = -1;
            grid();


        }


        protected void Button6_Click(object sender, EventArgs e)
        {
            grid();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {

            int id1=   Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "sp_neetha";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", DbType.Int32);
            cmd.Parameters.AddWithValue("@school", DbType.String);
            cmd.Parameters.AddWithValue("@office", DbType.String);
            cmd.Parameters.AddWithValue("@flag", "delete");
            grid();
           int result = cmd.ExecuteNonQuery();
           if (result==1)
           {

               grid();

            }


            con.Close();
      }

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {


            if(e.CommandName.Equals("add"))
            {


                TextBox txtid1 = (TextBox)GridView1.FooterRow.FindControl("txteditfooter");
                TextBox txtschool1 = (TextBox)GridView1.FooterRow.FindControl("txteditscool");
                TextBox txtoffs1 = (TextBox)GridView1.FooterRow.FindControl("txteditfooterofs");
              
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "sp_neetha";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", txtid1.Text);
                cmd.Parameters.AddWithValue("@school", txtschool1.Text);
                cmd.Parameters.AddWithValue("@office", txtoffs1.Text);
                cmd.Parameters.AddWithValue("@flag", "insert");
                int result = cmd.ExecuteNonQuery();
                if(result==1)
                {

                    GridView1.EditIndex = -1;
                        grid();

                      

                }

                con.Close();




            }
        }

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



    }
}
Posted
Comments
[no name] 21-Jul-15 23:33pm    
Isn't this a repost?
[no name] 21-Jul-15 23:40pm    
first one I fixed through u ppl help. but now am getting this I cant fix it.please help me

Why can't you use varchar instead of nChar (Store procedure variables) ?

I just read your question, it seems ok... just give it a try

Clear Parameters before assigning parameters to the SqlCommand
cmd.Parameters.Clear();

let me know if problem still persist, I can check further...
 
Share this answer
 
v2
Comments
[no name] 22-Jul-15 0:33am    
but after clearing paramaeter it doesn't update.returning same ol value why? where I give clear()?? in grid()0r row updating?
Based on your code it looks like you store the command in a class level variable or similar. Why? Instead of doing that simply define the command when you use it.

Something like
C#
...

            con.Open();
            SqlCommand cmd = new SqlCommand("sp_neetha", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", txtid.Text);
            cmd.Parameters.AddWithValue("@school", txtschool.Text);
            cmd.Parameters.AddWithValue("@office", txtoffs.Text);
            cmd.Parameters.AddWithValue("@flag","update");
 
            cmd.ExecuteNonQuery();
            con.Close();
...

Making it this way you don't need to worry if the command is used already and so on.
 
Share this answer
 
Hello ,
In the Delete method , you have called grid() , which is responsible for the error . In that method (GridView1_RowDeleting) you have already opened the connection and load the data from database .Now , while the connection is opened again you try to open the connection by calling the grid() , which throws error . So, first close the connection then call that grid() method .
con.open();
//other stuffs
con.close();

//last call the method
grid();
 
Share this answer
 
Comments
[no name] 22-Jul-15 1:52am    
worked thanks alot
Animesh Datta 22-Jul-15 1:56am    
what it shows
[no name] 22-Jul-15 2:00am    
now delete button is not working. why?
Animesh Datta 22-Jul-15 2:03am    
In the row deleting methoid change from
cmd.Parameters.AddWithValue("@id", DbType.Int32);
to
cmd.Parameters.AddWithValue("@id", txtid.Text); and do this for all other parameters
[no name] 22-Jul-15 2:08am    
but all objects r null not working ERORR is---aditional information: Object reference not set to an instance of an object.

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