MySQL查询中的输入字符串格式不正确

本文关键字:字符串 格式 不正确 输入 查询 MySQL | 更新日期: 2023-09-27 17:58:12

在我的代码中,我连续多次调用数据库。然而,当我试图从数据库中读取整数值时,我会收到以下错误:输入字符串的格式不正确。这是我的代码:

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;
}

它在我在代码中标记的点处抛出错误。任何帮助都将不胜感激!

MySQL查询中的输入字符串格式不正确

still_active的其中一个值不是有效的整数-可能是空字符串?

为了简单起见,我要做的是在执行命令之前,确保您已经显示了命令文本,以确保它符合您的预期。

另外,请注意,如果某个值为NULL,代码中会发生什么?

如果正在读取的列值为null或DBNull或为空或不是正确的整数值,就会发生这种情况。

如果still_active始终为TINYINT(1),则使用next:

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

或者更好地使用CCD_ 2。

考虑在转换之前检查字段的值,因为它可能是一个Null字段

reader["still_active"] != DBNull

最好使用TryParse:

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