CODEWITHSUNDEEP
c#sqldatareadersqlclient
user5166450
user5166450

Reputation:

My SqlDataReader Has No Rows?

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.

Upvotes: 1

Views: 1057

Answers (2)

ARr0w
ARr0w

Reputation: 1731

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.

Upvotes: 3

Dan Hebdon
Dan Hebdon

Reputation: 201

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.

Upvotes: 0

Related Questions