Input string was not in a correct format in MySQL query
In my code I am making multiple calls to the database in a row. However when I try to read in a integer value from the database I am getting the following error: Input string was not in a 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 the error at the point I marked in the code. Any help开发者_开发技巧 would be much appreciated!
One of the values of still_active
isn't a valid integer - an empty string maybe?
For simplicity, what I would do is before you do the Command, make sure you have displayed the commandtext to be sure it reads how you expected.
Also, be aware, what happens in your code if something is NULL?
This happens if the column value which you are reading is either null or DBNull or blank or not a proper integer value.
If still_active is always TINYINT(1) then use next:
int projectId = (int)reader["still_active"];
or better use byte
.
Consider checking the value of the field before converting as it seems it might be a Null field
reader["still_active"] != DBNull
its better to use TryParse
:
int projectId =0;
Int32.TryParse(Convert.ToInt32(reader["still_active"].ToString(),out projectId);
精彩评论