Checking for user existence in SQL Server table

Question

I need to create a class that contains logic for checking a SQL Server table for user via Login control. When I run my code and enter data in Login control it does not recognize user and writes a error message. Can someone look my code for errors?

Here is the class code :

public int checkUser (string Username, string Password)
{
        using (SqlConnection sqlCnn = new SqlConnection(cnn))
        {
            Int32 count = 0;
            string sqlQuery = "SELECT COUNT(*) AS LoginInfo FROM users" +
                "WHERE Username = @Name AND Password = @Password";
            //sqlCnn.Open();

            using (SqlCommand comm = new SqlCommand(sqlQuery, sqlCnn))
            {
                //comm.Parameters.AddWithValue("@Name", Username);
                //comm.Parameters.AddWithValue("@Password", Password);
                comm.Parameters.Add("@Name", SqlDbType.NChar).Value = Username;
                comm.Parameters.Add("@Password", SqlDbType.NChar).Value = Password;

                try
                {
                    sqlCnn.Open();
                    count = (Int32)comm.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error");
                }
                finally
                {
                    sqlCnn.Close();
                }

                return (Int32)count;
        }
    }
}

And this is the implementation code :

protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
        User1 user = new User1();

        string name = Login1.UserName;
        string pass = Login1.Password;

        if (user.checkUser(name, pass) > 0)
        {
            Response.Redirect("mainPage.aspx");
        }
        else
        {
            Label1.Text = "Error";

        }
}

Did you debug your code and what `checkUser` method returns exactly? — Soner Gönül, Jul 12 '15 at 13:05
Try to run query directly on SSMS and see what's the result. There could be some white space issue. — Raj Karri, Jul 12 '15 at 13:11
Please tell us you're ***not REALLY*** storing your passwords in **plain text!** — marc_s, Jul 12 '15 at 13:43
I'm a beginner and this is my exercise project, I would like to get this working first and then I will try to change pass storing. — Arsen Milosev, Jul 12 '15 at 14:00
checkUser method should accept two values, username and pass and check if that data exist in the users table. — Arsen Milosev, Jul 12 '15 at 14:01

score 1 · Answer 1 · edited May 23 '17 at 12:06

1

Your query string should come out wrong as:

SELECT COUNT(*) AS LoginInfo FROM usersWHERE Username = @Name AND Password = @Password

And this is probably what is causing the exception.

I always use a verbatim string literal so that it's easier to copy the query, and you don't have to think about ending or starting each string with a space:

string sqlQuery = @"SELECT COUNT(*) AS LoginInfo FROM users 
            WHERE Username = @Name AND Password = @Password";

Instead of just Console.WriteLine("Error"); you should probably write the exception:

catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace); //probably a good idea
}

You could also look into the InnerException if it's not null.

I see that you have big letters in your variables Username and Password. You should change the first character to be lowercase. I also always use the AddWithValue

comm.Parameters.AddWithValue("@Name", username);

edited May 23 '17 at 12:06

Community

1
1

answered Jul 12 '15 at 13:18

Binke

897
8
25

I did what you suggested, but unfortunately there is still the same problem. I type in username and pass in login and it writes below the control "Error" like that user doesn't exist in a table. – Arsen Milosev Jul 12 '15 at 14:03
You need to debug your project and set a breakpoint in your catch, if you do not log the exception to your program. You should get an exception in your console and that is the real first step to know what is wrong. If you'r having a hard time with this, you might need to take a course or follow a book with examples so that you at least have the basic knowledge about debugging. – Binke Jul 12 '15 at 16:19
There is an exception when i put a breakpoint in catch and it says : "Exception:Caught: "The connection was not closed. The connection's current state is open." (System.InvalidOperationException) A System.InvalidOperationException was caught: "The connection was not closed. The connection's current state is open." ." – Arsen Milosev Jul 12 '15 at 17:02
Okey, so this might have been your original question if you had checked the exception before. Now if you check stackoverflow you might find something that will help you in with this error. Perhaps this might solve your problem: http://stackoverflow.com/questions/11053731/invalidoperationexception-the-connection-was-not-closed-the-connections-curren – Binke Jul 13 '15 at 06:13
Tried their advices and still nothing.. I will need to think of some other way of doing this obviously.. – Arsen Milosev Jul 13 '15 at 10:49

score 0 · Answer 2 · answered Jul 21 '15 at 14:03

Okay i found the solution for the problem. First mistake was in sql query. I shouldn't have forwarded data that the class accepts as values ( Username = @Name ==> name = @Name ). Second, in the implementation code there had to be added another line for the redirect to be able to transfer the approved user to another page:

FormsAuthentication.RedirectFromLoginPage(name, true);

Checking for user existence in SQL Server table

2 Answers2