No of records being found are/is =-1

Question

ii'm newbie to MVC and has been assigned a task to make register and login page in plain old sql queries etc. Advance will come later. I succeeded in making register page, it's done perfectly but problem is in login page, It returns -1 for sqlreader , i am passing correct values even debugging showed correct values but still it's behaving like nothing being found. Custom error: No of records being found are/is =-1

code:

view:

@using(Html.BeginForm("loginResult", "Home", FormMethod.Post, new {id="loginForm"}))
{
        <div>
         <i>@Html.Label("Email:")</i>
            @Html.TextBox("txtboxEmail")
        </div>

        <div>
         <i>@Html.Label("Password:")</i>
            @Html.Password("txtboxPassword")
        </div>

        <div>  
         <button type="submit" id="btnLogin" name="Command" value="Login">Login</button> 
        </div>
}

controller:

[HttpPost]
        public ActionResult loginResult(String command, FormCollection formData) 
        {
            if (command == "Login")
            {
                var email = formData["txtboxEmail"];
                var pwd = formData["txtboxPassword"];
                String conStr = "Data Source=HUNAIN-PC;Initial Catalog=registration;User ID=sa;Password=abc123!@#";
                database db = new database();
                var status = db.Login_db(email, pwd, conStr);
                ViewBag.Message = status.Message;
            }
            return View();
        }
        public ActionResult login() 
        {

            ViewBag.Message = "Login";

            return View();
        }

model:

public ConnectionStatus Login_db( String email, String pwd, String conStr) 
        {
            SqlConnection sqlCon = new SqlConnection(conStr);
            SqlCommand sqlCom = new SqlCommand();
            sqlCom.Connection = sqlCon;
            sqlCom.CommandText = "select userEmail, userPwd from tblRegister where userEmail=@email AND userPwd=@pwd";
            sqlCom.Parameters.AddWithValue("@email", email);
            sqlCom.Parameters.AddWithValue("@pwd", pwd);
            ConnectionStatus connectStatus = new ConnectionStatus();
            int row_aff;
            try
            {
                sqlCon.Open();
                SqlDataReader dr = sqlCom.ExecuteReader();
                row_aff = dr.RecordsAffected;
                connectStatus.Message = "No of records being found are/is ="+ row_aff;

            }
            catch (Exception ex)
            {
                connectStatus.Message = ex.Message;
            }
            finally
            {
                sqlCon.Close();
            }
            return connectStatus;
        }
    }
}

ConnectionStatus is a class which has one property MESSAGE.

namespace LoginSys.Models
{
    public class ConnectionStatus
    {
        public String Message { get; set; }
    }
}

Using SQL for password validation (`WHERE Password = @param`) is bad; 1) it most often means that you're storing passwords in clear-text, and 2) it most often means that you're not verifying character casing. ("HellO" == "hELLo") — sisve, Dec 30 '13 at 08:32

score 3 · Accepted Answer · edited May 23 '17 at 11:56

SqlDataReader.RecordsAffected returns -1 for all SELECT statements. It does not represent the number of rows returned. The documentation explains this about the RecordsAffected property:

The number of rows changed, inserted, or deleted; 0 if no rows were affected or the statement failed; and -1 for SELECT statements.

To get the number of rows returned, you could use a query like this:

SELECT COUNT(*) FROM tblRegister WHERE (conditions)

And access the count with the code:

var count = sqlCommand.ExecuteScalar();

As an aside, it looks like you're storing a plaintext password in the database. If that's the case, I urge you to change this. If anybody gains access to your database, they can retrieve users' passwords. Since people tend to use the same passwords for everything, the users' other accounts (like email and bank accounts) might be compromised also.

The more secure way is to store hashed passwords using a hash algorithm designed for passwords (something like bcrypt or PBKDF2, and specifically not something like SHA-1 or MD5). Then, validate the password with this logic:

Query the database for the user trying to sign in.
Hash the submitted password and compare it against the hashed data in the database.
If they match, the password was correct.

Libraries like BCrypt.net will take care of the second step for you, all you need to do is store the result of HashPassword() in the database feed it back into the VerifyPassword() function.

No of records being found are/is =-1

2 Answers2