The input string was not in the correct format in the MySQL query

In my code, I make several calls to the database in a row. However, when I try to read the integer value from the database, I get the following error: the input string was not in the correct format. This is my code:

private int getNumberOfProjectsAssigned()
{
    ArrayList staffProjects = new ArrayList();
    int numberOfProjects = 0;
    try
    {
        string strConnection = ConfigurationSettings.AppSettings["ConnectionString"];
        MySqlConnection connection = new MySqlConnection(strConnection);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT id_project_pk FROM `test`.`staff_on_project` WHERE id_staff_pk = " + Convert.ToInt32(Session["CurrentUserID"]);
        //SELECT idusers, first_name, last_name, job_title, code_quality, time_bonus,analysis_of_requirements FROM `test`.`users` WHERE security_level > 1;
        connection.Open();

        MySqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            int projectId = Convert.ToInt32(reader["id_project_pk"].ToString());
            staffProjects.Add(projectId);
        }
        connection.Close();
        foreach (int i in staffProjects)
        {
            command.CommandText = "SELECT still_active FROM `test`.`projects` WHERE idprojects = " + i;
            //SELECT idusers, first_name, last_name, job_title, code_quality, time_bonus,analysis_of_requirements FROM `test`.`users` WHERE security_level > 1;
            connection.Open();

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                int projectId = Convert.ToInt32(reader["still_active"].ToString()); // Error Occurs Here
                if(projectId == 1)
                {
                    projectsStaffWorksOn.Add(projectId);
                }
                numberOfProjects = projectsStaffWorksOn.Count;
            }
            connection.Close();
        }            
    }
    catch { }
    return numberOfProjects;
}

It throws an error at the point marked in the code. Any help would be greatly appreciated!

+3
source share
6 answers

One of the values ​​is still_activenot a valid integer - maybe an empty string?

0
source

For simplicity, what would I do before executing a command, be sure to include the command text to make sure it reads as you expected.

, , , - NULL?

0

This happens if the column value you are reading is null or DBNull or empty or does not match the correct integer value.

0
source

If still_active is always TINYINT (1), then use the following command:

int projectId = (int)reader["still_active"];

or better to use byte.

0
source

Consider checking the field value before conversion, as it may be a Null field.

reader["still_active"] != DBNull

0
source

better to use TryParse:

int projectId =0;
Int32.TryParse(Convert.ToInt32(reader["still_active"].ToString(),out projectId); 
-1
source

All Articles