Trying to Login But Whenever i tried to login it run catch condition and display failed

Question

I am trying to Login a user using this code that I defined in a class. Whenever I tried to login it runs catch condition and display failed as you can see in the code. Please help me to solve this problem

Here is the Class code:

public bool Select(hostelClass h)
    {
        bool isSuccess = false;
        SqlConnection conn = new SqlConnection(myconnstring);
        DataTable dt = new DataTable();
        try
        {
            string sql = "SELECT COUNT(*) FROM hm_login WHERE username=@username AND password=@password";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("@username", h.username);
            cmd.Parameters.Add("@password", h.password);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            conn.Open();
            var i = cmd.ExecuteScalar();
            adapter.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                UserID = h.username;
                welcome settingsForm = new welcome();
                settingsForm.Show();
                isSuccess = true;
            }
            else
            {

                MessageBox.Show("Please enter Correct Username or Password");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed");
        }
        finally
        {
            conn.Close();
        }
        return isSuccess;
    }

Here is the Form/Button Code

private void Button_Click(object sender, EventArgs e)
{
    hc.username = textBoxUsername.Text;
    hc.password = textBoxPassword.Text;
              
    bool value = hc.Select(hc);
    
    if (value)
    {
         clear();
         this.Hide();
    }
}

Answer 1

Several things to change in your approach. Instead of "select *", you can query as "Select 1 from..". Use the command.Parameters.Add(), instead of AddwithValue(). AddwithValue is known to have issues. Call the cmd.ExecuteScalar() instead of ExecuteNonQuery.