locked
stored procedure to check userid and password are correct? RRS feed

  • Question

  • Hi all,

    I had some code connecting to database with login form.

    I have wrote stored procedure to check login details from user input and redirect to main page if its correct.here is the code 

     

    Create PROCEDURE GetUsers

    @userid varchar(max) = userid,

    @password varchar(max) = password

    AS

    Select userid,password from Users

    where userid=@userid

    AND password = @password

    And here is my c# code calling stored procedure

     protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)

            {

                string MyConn = "Data Source=(local);Initial Catalog=master;Integrated Security=True";

                SqlConnection myConnection = new SqlConnection(MyConn);

                myConnection.Open();

     

                SqlCommand myCommand = new SqlCommand("GetUsers", myConnection);

     

                myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                myConnection.Close();

     

            }

     

    Many thanks

     


    satwick
    Tuesday, September 13, 2011 6:33 PM

Answers

  • Hello Satwick

    Please see this code

    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
        {
    
            string MyConn = "Data Source=(local);Initial Catalog=master;Integrated Security=True";
    
            SqlConnection myConnection = new SqlConnection(MyConn);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand("dbo.GetUser", myConnection);
            myCommand.CommandType = System.Data.CommandType.StoredProcedure;
            myCommand.CommandText = "GetUser";//Mention Your storeprocedure name here
            myCommand.Parameters.Add("@userid", System.Data.SqlDbType.VarChar, 200).Value = Login1.UserName;
            myCommand.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 200).Value = Login1.Password;
            SqlParameter p1 = new SqlParameter("retun", SqlDbType.Int);
            p1.Direction = ParameterDirection.ReturnValue;
            myCommand.Parameters.Add(p1);
            myCommand.ExecuteNonQuery();
            Response.Redirect("login.aspx");
            myCommand.Dispose();
            myConnection.Close();
        }
    

     


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:27 AM
    Wednesday, September 14, 2011 1:20 PM
  • Change command text to:

    myCommand.CommandText="dbo.GetUser";
    



    Mitja
    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:26 AM
    Wednesday, September 14, 2011 6:17 PM

All replies

  • I would rather not use a stored procedure. You can get more useful information with not-using stopred procecure.

    Check it out, how it when using DataReader class:

            public static bool LogingInMethod(string userName, string password)
            {
                bool result = false;
    
                string sqlQuery = "SELECT UserName, Password FROM Users WHERE UserName ='" + userName + "'";
                using (SqlConnection sqlConn = new SqlConnection(p))
                {
                    SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
                    SqlDataReader reader;
                    bool bHasRows = false;
                    try
                    {
                        sqlConn.Open();
                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
    
                                if (password == reader["Password"].ToString().Trim())
                                {
                                    result = true;
                                    break;
                                }
                            bHasRows = true;
                        }
                        reader.Close();
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        sqlConn.Close();
                    }
                    finally
                    {
                        if (sqlConn.State == ConnectionState.Open)
                        {
                            sqlConn.Close();
                        }
                        cmd = null;
                        reader = null;
                        GC.Collect();
                    }
    
                    if (result == true)
                    {
                        result = true;
                    }
    
                    else if (bHasRows == false)
                    {
                        MessageBox.Show("Wrong userName.", "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        result = false;
                    }
                    else
                    {
                        MessageBox.Show("Wrong password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        result = false;
                    }
                }
                return result;
            }
    



    Mitja
    Tuesday, September 13, 2011 6:47 PM
  • If wanna use a stored procedure do it this way:

    CREATE PROCEDURE logincheck
    (
    @u varchar(50),
    @p varchar(100)
    )
    as
    declare @ap varchar(50)
    select @ap=password from tbuser where loginid=@u
    if @ap is null
    return -1
    else
    if @ap=@p
    return 1
    else
    return -2
    

    and a code in C#:

    private int checkUser(string u, string p)
    {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString; //you can do your own way using connection!
            con.Open();
            SqlCommand cmd = new SqlCommand("logincheck", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@u", SqlDbType.VarChar, 50).Value = u;
            cmd.Parameters.Add("@p", SqlDbType.VarChar, 50).Value = p;
            SqlParameter p1 = new SqlParameter("ret", SqlDbType.Int);
            p1.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p1);
            cmd.ExecuteNonQuery();
            int k = Convert.ToInt32(cmd.Parameters["ret"].Value);
            cmd.Dispose();
            return k;
        }
    



    Mitja
    • Marked as answer by satwick Tuesday, September 13, 2011 8:31 PM
    • Unmarked as answer by satwick Wednesday, September 14, 2011 8:35 AM
    Tuesday, September 13, 2011 6:56 PM
  • Thank you for quick reply
    satwick
    Tuesday, September 13, 2011 8:31 PM
  • hi Mitja,

    I have modified code but giving me an error message stored procedure couldn't find but it does exit in db dont know why?any ideas?

    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)

            {

                string MyConn = "Data Source=(local);Initial Catalog=master;Integrated Security=True";

                SqlConnection myConnection = new SqlConnection(MyConn);

                myConnection.Open();

     

                SqlCommand myCommand = new SqlCommand("dbo.GetUser", myConnection);

     

                myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                myCommand.Parameters.Add("@userid", System.Data.SqlDbType.VarChar, 200).Value = Login1.UserName;

                myCommand.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 200).Value = Login1.Password;

                SqlParameter p1 = new SqlParameter("retun", SqlDbType.Int);

                p1.Direction = ParameterDirection.ReturnValue;

                myCommand.Parameters.Add(p1);

                myCommand.ExecuteNonQuery();

                Response.Redirect("login.aspx");

                myCommand.Dispose();

                myConnection.Close();

            }

     

     

     

    and my sp here it is 

     

    ALTER PROCEDURE [dbo].[GetUser]

    (

    @userid varchar(max),

    @password varchar(max))

    AS

    declare @tmpPassword varchar(255),@retun int

    Select @tmpPassword=[password] from Users

    where userid=@userid

    if @tmpPassword=NULL 

    Begin 

    Select @retun=-1

    end

    else 

    begin 

    if @tmpPassword=@password

    begin select @retun=1

    end

    else begin select @retun=0

    end

    end

    select @retun as retun


    thank you 

     


    satwick
    • Edited by satwick Wednesday, September 14, 2011 8:35 AM
    Tuesday, September 13, 2011 8:47 PM
  • In your first post the name of your stored procedure is: Create PROCEDURE GetUsers.

    But in your code you use: SqlCommand myCommand = new SqlCommand("dbo.GetUser", myConnection);

    You forgot the "s"

     

    Noam B.



    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...
    Wednesday, September 14, 2011 11:26 AM
  • Hello Satwick

    Please see this code

    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
        {
    
            string MyConn = "Data Source=(local);Initial Catalog=master;Integrated Security=True";
    
            SqlConnection myConnection = new SqlConnection(MyConn);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand("dbo.GetUser", myConnection);
            myCommand.CommandType = System.Data.CommandType.StoredProcedure;
            myCommand.CommandText = "GetUser";//Mention Your storeprocedure name here
            myCommand.Parameters.Add("@userid", System.Data.SqlDbType.VarChar, 200).Value = Login1.UserName;
            myCommand.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 200).Value = Login1.Password;
            SqlParameter p1 = new SqlParameter("retun", SqlDbType.Int);
            p1.Direction = ParameterDirection.ReturnValue;
            myCommand.Parameters.Add(p1);
            myCommand.ExecuteNonQuery();
            Response.Redirect("login.aspx");
            myCommand.Dispose();
            myConnection.Close();
        }
    

     


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:27 AM
    Wednesday, September 14, 2011 1:20 PM
  • Hi I tried to add line 

    myCommand.CommandText="GetUser";

     

    Still having same problem

    Actuall I changed sp name to getuser not getusers.

     

    Thank you,

    satwick


    satwick
    Wednesday, September 14, 2011 2:40 PM
  • Change command text to:

    myCommand.CommandText="dbo.GetUser";
    



    Mitja
    • Marked as answer by Jackie-Sun Monday, September 26, 2011 6:26 AM
    Wednesday, September 14, 2011 6:17 PM
  • Hi satwick,

    How's your issue? Have you solved it after trying Mitja's suggestion? If you have any updates, please feel free to let me know.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 7:27 AM