edit link_button grid view with store procedure

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

i am very new to store procedure so if there is small error then also help me to solve it......
i have doing Edit data within grid view with link button my store procedure look like...

SQL

ALTER procedure [dbo].[editstudent] @sid int, @firstname varchar(50),@lastname varchar(50),@gender varchar(50),@address varchar(50),@phone varchar(50),@dob datetime,@email varchar(50)
as
update student set  firstname=@firstname, lastname=@lastname, gender=@gender, address=@address, phone=@phone, dob=@dob, email=@email where sid=@sid

*************************************************
and aspx.cs page for that link button look like.....

C#

protected void linkedit_Click(object sender, EventArgs e)
        {
            LinkButton lnkButton = sender as LinkButton;
            GridViewRow row = (GridViewRow)lnkButton.NamingContainer;
            txtsid.Text = row.Cells[2].Text;
            SqlCommand cm  = new SqlCommand("editstudent", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.Add("@sid", SqlDbType.Int).Value = txtsid.Text;
            cn.Open();
            SqlDataReader dr = cm.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    txtsid.Text = dr["Sid"].ToString();
                    txtfirstname.Text = dr["Firstname"].ToString();
                    txtlastname.Text = dr["Lastname"].ToString();
                    txtgender.Text = dr["Gender"].ToString();
                    txtaddress.Text = dr["Address"].ToString();
                    txtphone.Text = dr["Phone"].ToString();
                    txtdob.Text = dr["Dob"].ToString();
                    txtemail.Text = dr["Email"].ToString();

                }
                dr.Close();
                cn.Close();
                fillgrid();
            }
            else
            {
                Response.Write("there is no data to edit");  
            }  

        }

but i have seen error like.......
Procedure or function 'editstudent' expects parameter '@firstname', which was not supplied.
so plese help me to solve it ......

Posted 22-Jul-12 22:14pm

pandya purvang

Updated 22-Jul-12 23:06pm

AshishChaudha

v2

Add a Solution

3 solutions

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)

Kaushik Saha from Kolkata,India · Answer 1 · 2012-07-22T23:04:00

This is only for edit

SQL

ALTER procedure [dbo].[editstudent] @sid int, @firstname varchar(50),@lastname varchar(50),@gender varchar(50),@address varchar(50),@phone varchar(50),@dob datetime,@email varchar(50)
as
update student set  firstname=@firstname, lastname=@lastname, gender=@gender, address=@address, phone=@phone, dob=@dob, email=@email where sid=@sid

*************************************************
this is only for selection

SQL

create procedure [dbo].[selectstudent]
@sid int
begin
select firstname,lastname,gender,address,phone,dob,email from student where
sid=@sid
end

and aspx.cs page for that link button look like.....

C#

protected void linkedit_Click(object sender, EventArgs e)
{
LinkButton lnkButton = sender as LinkButton;
GridViewRow row = (GridViewRow)lnkButton.NamingContainer;
txtsid.Text = row.Cells[2].Text;
SqlCommand cm = new SqlCommand("selectstudent", cn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("@sid", SqlDbType.Int).Value = Convert.Int32(txtsid.Text.ToString());
cn.Open();
SqlDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{

txtfirstname.Text = dr["Firstname"].ToString();
txtlastname.Text = dr["Lastname"].ToString();
txtgender.Text = dr["Gender"].ToString();
txtaddress.Text = dr["Address"].ToString();
txtphone.Text = dr["Phone"].ToString();
txtdob.Text = dr["Dob"].ToString();
txtemail.Text = dr["Email"].ToString();
 
}
dr.Close();


/////This record is updating This portion not effective working but useless because here initial from database ans send to database without any changes
SqlCommand cmd = new SqlCommand("editstudent", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sid", SqlDbType.Int).Value = Convert.Int32(txtsid.Text.ToString());
cmd.Parameters.Add("@firstname", SqlDbType.Varchar).Value = txtfirstname.Text;
cmd.Parameters.Add("@lastname", SqlDbType.Varchar).Value = txtlastname.Text;
cmd.Parameters.Add("@gender", SqlDbType.Varchar).Value = txtgender.Text;
cmd.Parameters.Add("@address", SqlDbType.Varchar).Value = txtaddress.Text;
cmd.Parameters.Add("@phone", SqlDbType.Varchar).Value = txtphone.Text;
cmd.Parameters.Add("@dob", SqlDbType.DaeTime).Value = txtdob.Text;
cmd.Parameters.Add("@email", SqlDbType.Varchar).Value = txtemail.Text;
cmd.ExecutNonQuery()
cn.Close();
fillgrid();
}
else
{
Response.Write("there is no data to edit"); 
} 
 
}

Hopefully It will help to understand using stored procedure.

Aarti Meswania · Answer 2 · 2012-07-22T23:05:00

Solution 2

you should pass all parameters from your C#.net code
include lines

C#

cm.Parameters.Add("@firstname", SqlDbType.VarChar).Value = txtfirstname.Text;
        ...
//this way pass suitable values for all 7 parameters you have declared in store procedure

when ever you define parameters in store procedure
you have to pass values for each parameter while executing store procedure

SQL

ALTER procedure [dbo].[editstudent] 
--this is parameter list
@sid int, 
@firstname varchar(50),
@lastname varchar(50),
@gender varchar(50),
@address varchar(50),
@phone varchar(50),
@dob datetime,@email varchar(50)
--you must pass values to above parameters while executing this store procedure
as
update student set  firstname=@firstname, lastname=@lastname, gender=@gender, address=@address, phone=@phone, dob=@dob, email=@email where sid=@sid

Happy Coding!
:)

Posted 22-Jul-12 23:05pm

Aarti Meswania

Updated 22-Jul-12 23:30pm

v2

Comments

pandya purvang 23-Jul-12 5:13am

form coding include lines means????? at which places i have to write this code?

Aarti Meswania 23-Jul-12 5:27am

in your code,
after this statement "SqlCommand cmd = new SqlCommand("editstudent", cn);"

add parameter list with values,

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sid", SqlDbType.Int).Value = txtsid.Text;
cmd.Parameters.Add("@firstname", SqlDbType.Varchar).Value = txtfirstname.Text;
cmd.Parameters.Add("@lastname", SqlDbType.Varchar).Value = txtlastname.Text;
cmd.Parameters.Add("@gender", SqlDbType.Varchar).Value = txtgender.Text;
cmd.Parameters.Add("@address", SqlDbType.Varchar).Value = txtaddress.Text;
cmd.Parameters.Add("@phone", SqlDbType.Varchar).Value = txtphone.Text;
cmd.Parameters.Add("@dob", SqlDbType.DaeTime).Value = txtdob.Text;
cmd.Parameters.Add("@email", SqlDbType.Varchar).Value = txtemail.Text;
cmd.ExecutNonQuery()

other two solutions are provided here with total solution code.

pandya purvang 23-Jul-12 5:43am

okk i have change the code but new error is occurs that shows "indexoutofrangeexception at the line
txtsid.Text = dr["Sid"].ToString(); what is the solution....
i have mentione here that i have autoincrement sid from the database..

pandya purvang 23-Jul-12 6:19am

okkk thanks dear finally its working really thanks for help me..

Aarti Meswania 23-Jul-12 6:27am

thanks

AshishChaudha · Answer 3 · 2012-07-22T23:16:00

Check by doing follwoing code in linkedit button click

C#

protected void linkedit_Click(object sender, EventArgs e)
{
LinkButton lnkButton = sender as LinkButton;
GridViewRow row = (GridViewRow)lnkButton.NamingContainer;
txtsid.Text = row.Cells[2].Text;
SqlCommand cm = new SqlCommand("selectstudent", cn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("@sid", SqlDbType.Int).Value = txtsid.Text;
cn.Open();
SqlDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
txtsid.Text = dr["Sid"].ToString();
txtfirstname.Text = dr["Firstname"].ToString();
txtlastname.Text = dr["Lastname"].ToString();
txtgender.Text = dr["Gender"].ToString();
txtaddress.Text = dr["Address"].ToString();
txtphone.Text = dr["Phone"].ToString();
txtdob.Text = dr["Dob"].ToString();
txtemail.Text = dr["Email"].ToString();
 
}
dr.Close();

SqlCommand cmd = new SqlCommand("editstudent", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@sid", SqlDbType.Int).Value = txtsid.Text;
cmd.Parameters.Add("@firstname", SqlDbType.Varchar).Value = txtfirstname.Text;
cmd.Parameters.Add("@lastname", SqlDbType.Varchar).Value = txtlastname.Text;
cmd.Parameters.Add("@gender", SqlDbType.Varchar).Value = txtgender.Text;
cmd.Parameters.Add("@address", SqlDbType.Varchar).Value = txtaddress.Text;
cmd.Parameters.Add("@phone", SqlDbType.Varchar).Value = txtphone.Text;
cmd.Parameters.Add("@dob", SqlDbType.DaeTime).Value = txtdob.Text;
cmd.Parameters.Add("@email", SqlDbType.Varchar).Value = txtemail.Text;
cmd.ExecutNonQuery()
cn.Close();
fillgrid();
}
else
{
Response.Write("there is no data to edit"); 
} 
 
}

Your procedure should be

SQL

ALTER procedure [dbo].[editstudent] 
--this is parameter list
@sid int, 
@firstname varchar(50),
@lastname varchar(50),
@gender varchar(50),
@address varchar(50),
@phone varchar(50),
@dob datetime,@email varchar(50)
--you must pass values to above parameters while executing this store procedure
as
update student set  firstname=@firstname, lastname=@lastname, gender=@gender, address=@address, phone=@phone, dob=@dob, email=@email where sid=@sid

edit link_button grid view with store procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0