How to insert & edit unique value using store procedure

Question

User-1367813752 posted

my sql server database and store procedure

create table tbl_user
(
userID int Primary Key identity (1,1),
userName varchar(20),
pass varchar(30),
email varchar(50),
userRole int,
userPhoto varchar(100),
userStatus int,
);


//my store procedure is 
create PROCEDURE spCreateUser
	@userName varchar(20),
	@pass varchar(30),
	@email varchar(50),
	@userRole int,
	@userPhoto varchar(100),
	@userStatus int,
	@result int output
AS
BEGIN 
	IF EXISTS(SELECT * FROM tbl_user WHERE userName= @userName)
		SET @result = 1
	ELSE
	BEGIN
		SET @result = 0 
		INSERT INTO tbl_user(userName,pass,email,userRole,userPhoto,userStatus)
		VALUES(@userName,@pass,@email,@userRole,@userPhoto,@userStatus)
	END
	return @result
END

my asp.net c# code 
protected void btn_insert_Click(object sender, EventArgs e)
    {
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
         string path = string.Concat(Server.MapPath("~/UploadFile/" + fu_photo.FileName));
         fu_photo.SaveAs(path);
         using (SqlConnection con = new SqlConnection(CS))
         {
             SqlCommand cmd = new SqlCommand("spCreateUser", con);
             cmd.CommandType = CommandType.StoredProcedure;
             
             cmd.Parameters.AddWithValue("userName", txt_username.Text);
             cmd.Parameters.AddWithValue("pass", txt_pass.Text);
             cmd.Parameters.AddWithValue("email", txt_email.Text);
             cmd.Parameters.AddWithValue("userRole", ddl_role.SelectedValue);
             cmd.Parameters.AddWithValue("userPhoto", path);
             cmd.Parameters.AddWithValue("userStatus", ddl_status.SelectedValue);
             var result = cmd.Parameters.Add("@result", SqlDbType.Int);
             //cmd.Parameters.AddWithValue("output", result);
             result.Direction = ParameterDirection.ReturnValue;
             con.Open();
             int k = cmd.ExecuteNonQuery();
             if (k != 0)
             {
                 lblMsg.Text = "Record Inserted Succesfully into the Database";
                 lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
             }
             
             con.Close();  
         }
    }

This code work perfectly but problem is that how to print message when not insert value in database message show that user already exists in table.

 

Answer 1

User-18289217 posted

using (SqlConnection con = new SqlConnection("CONNECTION_STRING_HERE"))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "spCreateUser";
                    cmd.CommandType = CommandType.StoredProcedure;

                    // OTHER PARAMS HERE

                    cmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();
                    if (cmd.Parameters["@result"].Value != null)
                    {
                        var result = (int)cmd.Parameters["@result"].Value;
                        if (result == 1)
                        {
                            //print something
                        }
                        else
                        {
                            //print something else
                        }
                    }
                    //-- NOTE: when you use the using statement you don't have to close the connection explicitly. 
                    //         The using statement ensures that Dispose is called even if an exception occurs
                }
            }

HTH

Answer 2

User-1367813752 posted

Thanks for reply, as per directed by you code is not work plz tell me what is missing by me.

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
string path = string.Concat(Server.MapPath("~/UploadFile/" + fu_photo.FileName));
fu_photo.SaveAs(path);
using (SqlConnection con = new SqlConnection(CS))
{
           SqlCommand cmd = new SqlCommand("spCreateUser1", con);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("userName", txt_username.Text);
           cmd.Parameters.AddWithValue("pass", txt_pass.Text);
           cmd.Parameters.AddWithValue("email", txt_email.Text);
           cmd.Parameters.AddWithValue("userRole", ddl_role.SelectedValue);
           cmd.Parameters.AddWithValue("userPhoto", path);
           cmd.Parameters.AddWithValue("userStatus", ddl_status.SelectedValue);

           cmd.Parameters.Add("@result", SqlDbType.Int).Direction = ParameterDirection.Output;
           if (cmd.Parameters["@result"].Value != null)
           {
                   var result = (bool)cmd.Parameters["@result"].Value;
                     if (result == true)
                      {
                             lblMsg.Text = "Record Inserted Succesfully into the Database";
                                lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
                      }
                      else
                       {
                              lblMsg.Text = "Record Inserted Succesfully into the Database";
                               lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
                       }
            }

Thanks & Regards

Answer 3

User-18289217 posted

what is not working?

From what I see here you have the same code in the if and else. It should be like the following:

 if (result == true)
{
            lblMsg.Text = "The username is already taken.";
            lblMsg.ForeColor = System.Drawing.Color.Red;
}
else
{
             lblMsg.Text = "Record Inserted Succesfully into the Database";
             lblMsg.ForeColor = System.Drawing.Color.CornflowerBlue;
}

in addition you do not open the connection. add con.Open(); after the using statement

Answer 4

User-1367813752 posted

but code not reached at this if else statement and not error found. i changed message but still not work.

Answer 5

User-1367813752 posted

is there no need to execute scalar or other statement which execute store procedure. 

Thanks and regards

Answer 6

User-18289217 posted

[e-mail]

is there no need to execute scalar or other statement which execute store procedure. 

Thanks and regards

OMG, I totally forgot that. Please add the following just bellow the params adding portion:

cmd.ExecuteNonQuery();

P.S. I edited my original reply. Just add the other params and test it AS IS