Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
hello,
i am newbie to SPs. i want to perform insert,update and delete operations in one stored procedure.but i don't know how to pass flag or something like that to SP that SP knows that this flag is for this particular operation.
 
here is my code and store procedure:
 
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
   {
       con.Open();
       string id = grd.Rows[e.RowIndex].Cells[0].Text;
       //cust.deleteRecord(string id);
       //SqlCommand cmd = new SqlCommand("delete from tblEmp where id=@id", con);
       SqlCommand cmd = new SqlCommand("deleteRecord", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@id", id);
       cmd.ExecuteNonQuery();
       grd.EditIndex=-1;
       con.Close();
       binddata();
   }
 

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        con.Open();
        string id = grd.Rows[e.RowIndex].Cells[0].Text;
        string Name = ((TextBox)grd.Rows[e.RowIndex].Cells[1].Controls[0]).Text;
        string City = ((TextBox)grd.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
        string State = ((TextBox)grd.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
        string Salary = ((TextBox)grd.Rows[e.RowIndex].Cells[4].Controls[0]).Text;
        //SqlCommand cmd = new SqlCommand("update tblEmp set Name=@Name,City=@City,State=@State,Salary=@Salary where id=@id", con);
        SqlCommand cmd = new SqlCommand("updateRecord", con);
        cmd.CommandType = CommandType.StoredProcedure;
        //cust.updateRecord(string id, string Name,string City,string State,string Salary);
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@Salary", Salary);
        cmd.ExecuteNonQuery();
        grd.EditIndex = -1;
        con.Close();
        binddata();
    }
 

ALTER PROCEDURE [dbo].[operations]
@Id int,
@Name varchar(20),
@City varchar(20),
@State varchar(20),
@Salary int,
@Mode tinyint
as
Begin
	If @Mode = 1
		Begin
			insert into tblEmp(Name,City,[State],Salary) values(@Name,@City,@State,@Salary) 
		End
	If @Mode = 2
		Begin 
			update tblEmp
			set Name=@Name,City=@City,[State]=@State,Salary=@Salary where id=@id
		End
	If @Mode = 3
		Begin 
			delete from tblEmp where id=@id
		End
END
 
kindly please help.
 
Thanks & Regards,
Krunal kakadiya
 

[edit]Code block added - OriginalGriff[/edit]
Posted 29-Jun-11 3:36am
Edited 29-Jun-11 3:51am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

1)modify parameters of store procedure as below
 
ALTER PROCEDURE [dbo].[operations]
@Id int,
@Name varchar(20)=Null,
@City varchar(20)=Null,
@State varchar(20)=Null,
@Salary int = 0,
@Mode tinyint=0
 
2)
 
add following for GridView1_RowInserting
 
cmd.Parameters.AddWithValue("@Mode", 1);
add following for GridView1_RowUpdating
 
cmd.Parameters.AddWithValue("@Mode", 2);
add following for GridView1_RowDeleting
 
cmd.Parameters.AddWithValue("@Mode", 3);
  Permalink  

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

  Print Answers RSS
0 CHill60 195
1 Zoltán Zörgő 175
2 Richard Deeming 170
3 Richard MacCutchan 160
4 Peter Leow 145
0 Sergey Alexandrovich Kryukov 9,328
1 OriginalGriff 6,831
2 Peter Leow 4,477
3 Zoltán Zörgő 3,984
4 Richard MacCutchan 2,832


Advertise | Privacy | Mobile
Web03 | 2.8.150129.1 | Last Updated 29 Jun 2011
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100