locked
How to insert & edit unique value using store procedure RRS feed

  • 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.

     

    Thursday, April 9, 2020 10:10 PM

All replies

  • 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

    Friday, April 10, 2020 7:23 AM
  • 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

    Friday, April 10, 2020 11:28 AM
  • 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

    Friday, April 10, 2020 11:51 AM
  • User-1367813752 posted

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

    Friday, April 10, 2020 11:58 AM
  • User-1367813752 posted

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

    Thanks and regards

    Friday, April 10, 2020 11:59 AM
  • User-18289217 posted

    umeshdaiya@gmail.com

    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

    Saturday, April 11, 2020 8:28 AM