Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
XML
--CREATE PROC sp_Temptable1_Update
AS
DECLARE @id smallint,
@fname varchar(10),
@age smallint,
@email varchar(25),
@status varchar(10),
@job varchar(10)
BEGIN TRAN
  UPDATE Temptable1 SET Fname = @fname, Age = @age, Email = @email, Estatus = @status, Job = @job
  WHERE Id = @id
  IF @@ERROR <> 0 GOTO Error
COMMIT TRAN
Error:
  IF @@TRANCOUNT <> 0
  ROLLBACK TRAN

protected void Buttonupdate_Click(object sender, EventArgs e)
    {
        //For updating the values in the database
        SqlConnection cn = new SqlConnection(db);
        SqlCommand cd = new SqlCommand();
        cd.Connection = cn;
        cd.CommandText = "sp_Temptable1_UPDATE";
        cd.CommandType = CommandType.StoredProcedure;
        cd.Parameters.Add("@id", SqlDbType.SmallInt).Value = DropDownList2.SelectedItem.Value.ToString();
        cd.Parameters.Add("@fname", SqlDbType.VarChar).Value = TextBoxName.Text;
        cd.Parameters.Add("@age", SqlDbType.SmallInt).Value = TextBoxAge.Text;
        cd.Parameters.Add("@email", SqlDbType.VarChar).Value = TextBoxEmail.Text;
        cd.Parameters.Add("@status", SqlDbType.VarChar).Value = TextBoxStatus.Text;
        cd.Parameters.Add("@job", SqlDbType.VarChar).Value = TextBoxJob.Text;
       
        cn.Open();
        cd.ExecuteNonQuery();
        cn.Close();
    }
Posted
Updated 13-Dec-10 2:32am
v2
Comments
Sandesh M Patil 13-Dec-10 8:46am    
What is your question?
Sandesh M Patil 13-Dec-10 10:26am    
Accept the answer if it was correct

i think you declare parameters before As in store procedure. See following updated sp


CREATE PROC sp_Temptable1_Update
@id smallint,
@fname varchar(10),
@age smallint,
@email varchar(25),
@status varchar(10),
@job varchar(10)

AS

BEGIN TRAN
  UPDATE Temptable1 SET Fname = @fname, Age = @age, Email = @email, Estatus = @status, Job = @job
  WHERE Id = @id
  IF @@ERROR <> 0 GOTO Error
COMMIT TRAN
Error:
  IF @@TRANCOUNT <> 0
  ROLLBACK TRAN
 
Share this answer
 
Try by Applying each parameter like following way.

C#
cd.Parameters.Add(new SqlParameter(@"id",SqlDbType.SmallInt));
cd.Parameters[@"id"].Value = //Assign values here
cd.Parameters[@"id"].Direction = ParameterDirection.Input;


Do this for all the parameters and RUN command Again.

I think the ParameterDirection absense might causing the error you getting.

Please vote and Accept Answer if it Helped.
 
Share this answer
 
v2
Comments
shiju87 9-Jan-11 23:38pm    
Thank You

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