Click here to Skip to main content
15,906,625 members
Please Sign up or sign in to vote.
3.40/5 (2 votes)
See more:
how to write the query for insert update delete by using stored procedure....it should be in single stored procedure...
and how to use that stored procedure in code behind file(.cs)...

here i have written the query separately for insert and update...
My doubt is how to use this coding for both insert and update in code behind(.cs)...
SQL
create proc sp_userinformation
(
@username varchar(20),
@password varchar(20),
@firstname varchar(20),
@lastname varchar(20),
@email varchar(20),
@phoneno varchar(20),
@location varchar(15),
@created_by varchar(20),
@ERROR char(500) output)
as
insert into userinformation(username,password,firstname,lastname,email,phoneno,location,created_by)values (@username,@password,@firstname,@lastname,@email,@phoneno,@location,@created_by)  
set @ERROR='Sucessfully Inserted'

create proc sp_userinformation1
(
@username varchar(20),
@password varchar(20),
@firstname varchar(20),
@lastname varchar(20),
@email varchar(20),
@phoneno varchar(20),
@location varchar(15),
@created_by varchar(20),
@ERROR char(500) output)
as
update  userinformation set
username=@username,password=@password,firstname=@firstname, lastname =@lastname, ,location=@location,created_by=@created_by where  email=@email or phoneno=@phoneno

set @ERROR='Sucessfully Updated'
C#
protected void btnsubmit_Click(object sender, EventArgs e)
{
string UserName = txtuser.Text;
string Password = txtpwd.Text;
string ConfirmPassword = txtcnmpwd.Text;
string FirstName = txtfname.Text;
string LastName = txtlname.Text;
string Email = txtEmail.Text;
string Phoneno = txtphone.Text;
string Location = txtlocation.Text;
string Created_By = txtuser.Text;
int count = 0;
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=sample;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("sp_userinformation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@PhoneNo", Phoneno);
cmd.Parameters.AddWithValue("@Location", Location);
cmd.Parameters.AddWithValue("@Created_By", Created_By);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
count = cmd.ExecuteNonQuery();
message = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "The Values are inserted Successfully " + "');</script>")
lblErrorMsg.Text = message;
}


here i have written the query for insert...
My doubt is how to use this coding for both insert and update in code behind file(.cs)...
Please help me...

[Edit]Code block added[/Edit]
Posted
Updated 9-Mar-13 0:06am
v6

1 solution

Hi,

From front end pass one indicator to update or insert then use the indicator in the SP and do it in same SP.
SQL
create proc sp_userinformation
 (
 @username varchar(20),
 @password varchar(20),
 @firstname varchar(20),
 @lastname varchar(20),
 @email varchar(20),
 @phoneno varchar(20),
 @location varchar(15),
 @created_by varchar(20),
 @ERROR char(500) output),
 @indicator char(1)-----U-update and I--Insert
 as

if @indicator='I'
begin
 insert into userinformation(username,password,firstname,lastname,email,phoneno,location,created_by)values (@username,@password,@firstname,@lastname,@email,@phoneno,@location,@created_by) 
set @ERROR='Sucessfully Inserted'
end

If @indicator='U'
begin
   update userinformation set
 username=@username,password=@password,firstname=@firstname, lastname =@lastname, ,location=@location,created_by=@created_by where email=@email or phoneno=@phoneno

 set @ERROR='Sucessfully Updated'
 
end

[Edit]Code block added[/Edit]
 
Share this answer
 
v2
Comments
M.Thiyagaraja 9-Mar-13 4:12am    
Thank you Davidduraisamy...
My Doubt is how to use this stored procedure in code behind file...
here is my .cs file
protected void btnsubmit_Click(object sender, EventArgs e)
{
string UserName = txtuser.Text;
string Password = txtpwd.Text;
string ConfirmPassword = txtcnmpwd.Text;
string FirstName = txtfname.Text;
string LastName = txtlname.Text;
string Email = txtEmail.Text;
string Phoneno = txtphone.Text;
string Location = txtlocation.Text;
string Created_By = txtuser.Text;
int count = 0;
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=sample;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("sp_userinformation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@PhoneNo", Phoneno);
cmd.Parameters.AddWithValue("@Location", Location);
cmd.Parameters.AddWithValue("@Created_By", Created_By);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
count = cmd.ExecuteNonQuery();
message = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "The Values are inserted Successfully " + "');</script>")
lblErrorMsg.Text = message;
}

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