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)...
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'
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]