locked
Deny adding an already exist user RRS feed

  • Question

  • User-2094959909 posted
    Hello everyone,
    I work with asp.net c# and i already worked with the code to add a new user and also go to login page to enter this user with username and password
    My question is i want to do a test in addUser.aspx.cs that tests if this user i'm adding is already exists in the database the program show me a message "user already exists" but if not the program works normally ?
    Can anyone help please
    Wednesday, July 11, 2018 2:40 PM

All replies

  • User-1171043462 posted

    Checking can be easily done within stored proc used for registration

    WebForms: Simple User Registration Form Example in ASP.Net

    MVC: Simple User Registration Form with Entity Framework Database

    Also you can use jQuery AJAX to show whether Username is already Taken

    Web Forms: Check UserName Availability in ASP.Net using jQuery

    MVC: Check Username Availability (Exists) in Database using jQuery AJAX ...

    Wednesday, July 11, 2018 3:53 PM
  • User-2094959909 posted

    public partial class AjouterConsulteur : System.Web.UI.Page
    {
    string strConnString = ConfigurationManager.ConnectionStrings["DBcon"].ConnectionString;
    MySqlCommand com;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
    MySqlConnection con = new MySqlConnection(strConnString);
    //con.Open();
    com = new MySqlCommand();
    com.Connection = con;
    com.CommandType = CommandType.Text;
    com.Parameters.Clear();
    com.Parameters.AddWithValue("@userid",TextBox1.Text);
    com.Parameters.AddWithValue("@name", TextBox2.Text);
    com.Parameters.AddWithValue("@username", TextBox3.Text);
    com.Parameters.AddWithValue("@password", TextBox4.Text);
    com.Parameters.AddWithValue("@role", TextBox5.Text);

    com.CommandText = "insert into tblusers values (@userid,@name,@username,@password,@role)";
    //com.CommandText = "insert into tblusers values ('99','99','99','99','99')";

    //com.ExecuteNonQuery();
    if (con.State == ConnectionState.Closed)
    {
    con.Open();
    com.ExecuteNonQuery();
    con.Close();
    Label1.Text = "Successfully registered";
    TextBox1.Text = "";
    TextBox2.Text = "";
    TextBox3.Text = "";
    TextBox4.Text = "";
    TextBox5.Text = "";

    }

    }
    }

    ----------------

    this is the code i use for adding a new user ... is there any solution i would try in just inside this code or not ?

    thanks btw

    Wednesday, July 11, 2018 9:04 PM
  • User-369506445 posted

    hi

    if you <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="5" data-gr-id="5">userid</g> is a primary key in your database when you <g class="gr_ gr_8 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="8" data-gr-id="8">are insert</g> a duplicate row you get <g class="gr_ gr_6 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="6" data-gr-id="6">error</g> and you'll understand this user already exists

    please <g class="gr_ gr_7 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="7" data-gr-id="7">ry</g> below code :

    SqlConnection con = new SqlConnection(strConnString);
                con.Open();
                string q = "insert into tblusers values (@userid,@name,@username,@password,@role)";
                var com = new SqlCommand(q,con);
                com.Connection = con;
                com.CommandType = CommandType.Text;
                com.Parameters.Clear();
                com.Parameters.AddWithValue("@userid", "1");
                com.Parameters.AddWithValue("@name", "vahid");
                com.Parameters.AddWithValue("@username", "vahidbakhtiary");
                com.Parameters.AddWithValue("@password", "vahidbakhtiary");
                com.Parameters.AddWithValue("@role", "1");
    
             
     
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception exception)
                {
                    if (exception.Message.Contains( @"Violation of PRIMARY KEY constraint"))
                    {
    // user already exists"
    //here generate your message } }

    Thursday, July 12, 2018 7:43 AM
  • User36583972 posted


    Hi Omar27,

    this is the code i use for adding a new user ... is there any solution i would try in just inside this code or not ?

    You can just add a check detect the userid/name already exist in the database, then, give ocustomer a feedback.

    protected void Button1_Click(object sender, EventArgs e)
            {
                MySqlConnection con = new MySqlConnection(strConnString);
                //con.Open();
                com = new MySqlCommand();
               if(CheckExistuser( "userId"))
                {
                    return;
                }
    
                com.CommandText = "insert into tblusers values (@userid,@name,@username,@password,@role)";
                //com.CommandText = "insert into tblusers values ('99','99','99','99','99')";
                //com.ExecuteNonQuery();
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                    com.ExecuteNonQuery();
                    con.Close();
                }
            }
    
            public bool CheckExistuser(string userId)
            {
                bool result = false;
                try
                {
                    //select the userid exist for this record
                    com.CommandText = "SELECT userid, name FROM tblusers where userid= @userid";
                     //exist result = true;
                }
                catch
                {
                    result = false;
                }
    
                return result;
            }
    

    Best Regards,

    Yong Lu

    Thursday, July 12, 2018 8:02 AM
  • User-2094959909 posted
    So i should put if (con.state == connectionState.closed){...........} inside try you added ??

    Thursday, July 12, 2018 10:38 AM
  • User-369506445 posted

    please try below code

    public bool CheckExistuser(string userId)
            {
                bool result = false;
                try
                {
                    string q = "SELECT count (1) FROM tblusers where userid= @userid";
                    SqlConnection con = new SqlConnection(strConnString);
                    var com = new SqlCommand(q, con);
                    com.CommandType = CommandType.Text;
                    com.Parameters.Clear();
                    com.Parameters.AddWithValue("@userid", userId);
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                        int count=(int) com.ExecuteScalar();
                        con.Close();
                        // if count be Equal 0 , doesn't Exist user , 
                        // if count be more than 0 , Exists user , 
                        if (count > 0)  
                            result= true;
                         
                    }
                }
                catch
                {
                    result = false;
                }
    
                return result;
            }

    Thursday, July 12, 2018 11:15 AM
  • User-1171043462 posted

    @Omar,

    I would recommend write a SP that checks whether User Exists in your database and check whether User exists or not using RowsAffected returned by ExecuteNonQuery

    Stored Proc

    CREATE PROCEDURE InsertUser
    	@userid VARCHAR(100),
    	@name VARCHAR(100),
    	@username VARCHAR(100),
    	@password NVARCHAR(100),
    	@role VARCHAR(100)
    AS
    BEGIN
        IF NOT EXISTS(SELECT UserId FROM tblUsers WHERE UserName = @username)
    	BEGIN
    		INSERT INTO tblusers VALUES (@userid,@name,@username,@password,@role)
    	END
    END

    Code

    using (MySqlCommand com = new MySqlCommand("InsertUser"))
    {
            com.CommandType = CommandType.StoredProcedure.
            com.Parameters.AddWithValue("@userid",TextBox1.Text);
    		com.Parameters.AddWithValue("@name", TextBox2.Text);
    		com.Parameters.AddWithValue("@username", TextBox3.Text);
    		com.Parameters.AddWithValue("@password", TextBox4.Text);
    		com.Parameters.AddWithValue("@role", TextBox5.Text);
            com.Connection = con;
            con.Open();
            int rowsAffected = com.ExecuteNonQuery();
            con.Close();
    		if(rowsAffected > 0)
    		{
    			//User does not Exists
    		}
    		else
    		{
    		    //User Exists
    		}
    }

    Thursday, July 12, 2018 6:24 PM