My SqlDataReader Has No Rows?

Question

When I'am debugging my console says that my reader has no rows. Below you can find the code.

public ActionResult Login(LoginViewModel loginViewModel)
    {
        bool isSucces = false;


        if (ModelState.IsValid)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=DESKTOP-9DG53HK\TOMSQL;Initial Catalog=Webshop;Integrated Security=True");
            conn.Open();

            string username = loginViewModel._username;
            string password = HashPassword(loginViewModel._username, loginViewModel._password);


            SqlCommand cmd = new SqlCommand("SELECT * FROM [User] WHERE Username = @Username and Password = @Password;", conn);
            cmd.Parameters.AddWithValue("@Username", username);
            cmd.Parameters.AddWithValue("@Password", password);


            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int Role = Convert.ToInt32(reader["Role"]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            bool loginSuccessful = ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0));


            if (loginSuccessful)
            {
                //return View(new LoginViewModel(isSucces, Role));                       
            }
            else
            {
                Console.WriteLine("Invalid username or password");
            }

        }
        return View();
    }

The program skips the while loop completely. I have been searching for a while but could not find the answer maybe you guys see what is wrong.

you sure you are sending right parameters for username and password? can you put a breakpoint and confirm that you have right values and your select does return data? also, shouldn't your query put username and password as quoted string? SELECT * FROM [User] WHERE Username = '@Username' and Password = '@Password'; — Veljko89
– Veljko89, Commented Apr 18, 2018 at 10:33
@Veljko89 username and password are having the correct values. — user5166450
– user5166450, Commented Apr 18, 2018 at 10:35
@Veljko89 quoting parameters converts them to text. Parameters ensure that you never need quoting. If you need to use quoting, it means you are using string concatenation and are susceptible to SQL injection — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 18, 2018 at 10:45

ARr0w · Accepted Answer · 2018-04-18 11:03:58Z

3

First of all, when you have used SqlDataAdapter why do you need SqlDataReader? I recommend SqlDataAdapter because it also manages Connection and better approach then SqlDataReader. There are many things you must have to follow up with SqlDataReader which are Reader.Close(), Reader.Dispose(), Connection.Open() and Connection.Close() and Reader most likely fails on multiple hits when it is not async.

you can simply execute your code like this:

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
int Role = 0;    
bool loginSuccessful = false;
if(ds != null && ds.tables[0].Rows.Count > 0)
{
  Role = Convert.ToInt32(ds.tables[0].Rows[0]["Role"]);
  loginSuccessful = true;
}
else
{
  // No rows found
}

Secondly (asnwering your actual question): there must be no match with the passed parameters therefore 0 Rows.

edited Apr 18, 2018 at 11:03

answered Apr 18, 2018 at 10:35

ARr0w

1,73115 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5166450 Over a year ago

he says dat the ds.tables[0].rows.count > 0 in the if statement 0 is?

Panagiotis Kanavos Over a year ago

@Bogatom the real question is why are you writing this insecure code instead of using MVC's authentication?

Dan Hebdon · Accepted Answer · 2018-04-18 10:38:34Z

0

Are you positive that you are receiving data from your SELECT query? Try replacing it with a query such as:

SELECT * FROM Users WHERE id = 1;

I would also replace your if reader.HasRows with a simple trycatch as more comprehensive error handling.

answered Apr 18, 2018 at 10:38

Dan Hebdon

1971 silver badge7 bronze badges

Collectives™ on Stack Overflow

My SqlDataReader Has No Rows?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related