How to insert & edit unique value using store procedure

Reply

umeshdaiya@g...

None

0 Points

5 Posts

How to insert & edit unique value using store procedure

Apr 09, 2020 10:10 PM|umeshdaiya@gmail.com|LINK

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.

Reply

Kulrom

Contributor

4202 Points

1138 Posts

Re: How to insert & edit unique value using store procedure

Apr 10, 2020 07:23 AM|Kulrom|LINK

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

Remember to click Mark As Answer when you get a reply which answers your question.
My website: ASP.NET Custom Software Development

Reply

umeshdaiya@g...

None

0 Points

5 Posts

Re: How to insert & edit unique value using store procedure

Apr 10, 2020 11:28 AM|umeshdaiya@gmail.com|LINK

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

Reply

Kulrom

Contributor

4202 Points

1138 Posts

Re: How to insert & edit unique value using store procedure

Apr 10, 2020 11:51 AM|Kulrom|LINK

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

Remember to click Mark As Answer when you get a reply which answers your question.
My website: ASP.NET Custom Software Development