0

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";
        }
    }
}
6
  • Do any of these help? stackoverflow.com/questions/6098646/… stackoverflow.com/questions/11209241/… Commented Dec 17, 2022 at 21:52
  • @user700390 no... it keeps throwing same Exception even if I hardcode the correct"name" and "password" Commented Dec 17, 2022 at 22:03
  • 1
    Possibly not what you want to hear, but if you use Entity Framework Core instead of data readers, this sort of problem (and a whole load of others) will go away by themselves. You get a strongly typed interface to your data, which will save you a lot of work. Commented Dec 17, 2022 at 22:18
  • @AvrohomYisroel actually tested again the "while (reader.Read())" by counting the rows it reads and it reads only 1 row, and that's correct because has the filter of the name and passoword given. Anyway thanks for your tip. Commented Dec 17, 2022 at 22:22
  • Maybe if you set a breakpoint after while (reader.read()) and take a look at the reader.Item[] property, make sure you are getting the columns as you expect. Maybe you need to set the data type in the parameter? Commented Dec 17, 2022 at 22:27

1 Answer 1

0

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.