As you might have guessed from the title am I trying to do this:
#region check new nations
private void checknewnations()
{
addtolog("server","Checking for new nations");
string sql = "SELECT * FROM " + variables.tbl_nations + " WHERE nations_new=0";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
addtolog("mysql",reader["nations_name"].ToString());
int nation_ID = int.Parse(reader["nations_ID"].ToString());
string nation_name = reader["nations_name"].ToString();
string user_ID = reader["nations_user"].ToString();
addnation(nation_ID, nation_name, user_ID);
}
addtolog("server","Finished checking for new nations.");
//connection.Close();
reader.Dispose();
}
#endregion
which calls this in the while loop:
#region addnation
private void addnation(int nationIDnot, string nationName, string userID)
{
string nationID = makesixdigits(nationIDnot);
string userName = "";
string sql = "SELECT * FROM " + variables.tbl_users + " WHERE users_ID=" + userID;
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
userName = reader["users_name"].ToString();
}
reader.Dispose();
addtolog("add", "[" + nationID.ToString() + "] " + nationName + " [" + userID + "] " + userName);
}
#endregion
This gives me an error in the second block of code (the one that is called upon in the while loop) saying that there already is a datareader associated with the connection. How am I to get this to work, because I am sure there is a way.
DataReader holds on to connection until it iterates through all its records. Try this
using(reader)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(reader);
foreach(DataRow row in dt.Rows)
{
addtolog("mysql",row["nations_name"].ToString());
int nation_ID = int.Parse(row["nations_ID"].ToString());
string nation_name = row["nations_name"].ToString();
string user_ID = row["nations_user"].ToString();
addnation(nation_ID, nation_name, user_ID);
}
}
This way the two reader are not sharing the same connection