I am doing a login in C#. When I press the button to login which has the method that validate the user it throws this exception "InvalidCastException: object cannot be cast from dbnull to other types.".

I already verified almost anything. My database is correct. Both of columns of the tables are VARCHAR. In C# I am trying to save them into string so it shouldn't throw that exception. The exception is also thrown if I put the wrong name and password.

The problem should be inside of "while (reader.Read())" but I'm not sure...

This method is called by the button_OnClick method.

public User validateUser(string name, string pass)
{
    User user = null;

    String query = "SELECT * FROM users WHERE name = @name AND password = @password;";
    try
    {
        con = dbConnect.getConnection();

        if (con != null)
        {
            con.Open();
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                cmd.Parameters.Add(new MySqlParameter("@name", name));
                cmd.Parameters.Add(new MySqlParameter("@password", pass));

                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {

                            string nam = Convert.IsDBNull(reader["name"]) ? "" : reader["name"].ToString();
                            string password = Convert.IsDBNull(reader["password"]) ? "" : reader["password"].ToString();

                            user = new User(nam, password);

                            //   user = new User(reader["name"].ToString(), reader["password"].ToString());
                        }
                    }
                }
            }
        }
    }
    catch (MySqlException error1)
    {
        MessageBox.Show(" 123 "   error1.Message);
    }
    catch (InvalidCastException error2)
    { 
        MessageBox.Show(" 789 "   error2.Message);
    }
    return user;
}

And this is the button_OnClick method:

private void button1_Click(object sender, EventArgs e)
{
    string name = txtUser.Text;
    string pass = txtPass.Text;

    if (name == "" || pass == "")
    {
        MessageBox.Show("Rellena los campos.");
    }
    else
    {
        try
        {
            user = userDao.validateUser(name, pass);
        }
        catch (InvalidCastException ex)
        {
            MessageBox.Show("Error: "   ex.Message);
        }
        if (user == null)
        {
            txtMsg.Text = "ERROR";
        }
        else
        {
            txtMsg.Text = "CORRECTO";
        }
    }
}

CodePudding user response：

The problem is that Convert.IsDBNull(reader["name"]) first calls reader["name"] to extract the value from the DbDataReader, before it checks for DBNull.

Instead of Convert.IsDBNull, use DbDataReader.IsDBNull. I'd also use GetString() instead of GetValue().ToString().

string nam = reader.IsDBNull(reader.GetOrdinal("name")) ? "" : reader.GetString("name");
string password = reader.IsDBNull(reader.GetOrdinal("password")) ? "" : reader.GetString("password");

Like others commented, using a simple ORM, like Dapper, or a full ORM such as EF Core, will simplify and eliminate a lot of this code.