Description:
Hi,
I just upgraded to server version 5 after using 4.11 but i got a lot off issues/problems.
How ever one problem i havent solved yet is the "already an open DataReader" issue.
Im using ConnectionString:
Data Source=192.168.2.37;Database=dbj;User ID=root;Password=******; Allow Zero Datetime=true;pooling=false;
The code in "How to repeat" works fine with MySql 4.11 but with 5.0 it wont pass.
How to repeat:
public static string Welcome(int Offers, int Lotteries)
{
MySql.Data.MySqlClient.MySqlConnection objCon = new MySqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnectionString"]);
MySql.Data.MySqlClient.MySqlDataAdapter objDa = new MySqlDataAdapter();
MySql.Data.MySqlClient.MySqlDataAdapter objDa2 = new MySqlDataAdapter();
System.Data.DataSet objDS = new DataSet();
objDS.DataSetName = "start";
//objDS.Tables.Add(NewTable("biggest","select bannerid, imagename as src, description as alt, url from tbanner where displaystart <= CURRENT_DATE() and displayend >= CURRENT_DATE() order by modifieddate asc limit 1"));
//objDS.Tables.Add(NewTable("latest","select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.sortdate desc limit 10"));
objDa.SelectCommand = new MySqlCommand("select bannerid, imagename as src, description as alt, url from tbanner where displaystart <= CURRENT_DATE() and displayend >= CURRENT_DATE() order by modifieddate asc limit 1", objCon);
objDa.Fill(objDS,"biggest");
objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.sortdate desc limit 10", objCon);
objDa.Fill(objDS,"latest");
objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers, (select count(*) from tsms x where x.message = a.smscode and x.modifieddate >= (select adddate(current_date,-7)) and smscode != '') as smslastweek from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by smslastweek desc limit 10", objCon);
objDa.Fill(objDS,"mostpopular");
objDa.SelectCommand = new MySqlCommand("select a.lotteryid, a.lotteryname, if(length(a.lotterytext)>=90,concat(left(a.lotterytext,90),\"...\"), a.lotterytext) as lotterytext, a.smscode, if((select count(*) from tlotteryowners x where x.lotteryid = a.lotteryid)=1,(select customerid from tlotteryowners x where x.lotteryid = a.lotteryid),\"\") as customerid, (select y.commercialname from tlotteryowners x, tcustomer y where x.lotteryid = a.lotteryid and x.customerid = y.customerid limit 1) as commercialname from tlottery a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.enddate limit 10", objCon);
objDa.Fill(objDS,"newlotteries");
for(int i = 0; i < objDS.Tables["biggest"].Rows.Count; ++i)
{
string bannerid = objDS.Tables["biggest"].Rows[i]["bannerid"].ToString();
DB.Query("update tbanner set displays = displays + 1 where bannerid = " + bannerid);
}
objDa.SelectCommand = new MySqlCommand("select distinct a.offergroupid, a.groupname, a.imagename as offerimage, if(length(a.offertext)>=90,concat(left(a.offertext,90),\"...\"), a.offertext) as offertext, a.smscode, cast(a.enddate as char(10)) enddate, a.tariff, a.displays, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid,if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select y.imagename from toffergroupmembers x, tcustomer y where x.offergroupid = a.offergroupid and x.customerid = y.customerid),\"ico001.jpg\") as imagename, (select y.commercialname from toffergroupmembers x, tcustomer y where x.offergroupid = a.offergroupid and x.customerid = y.customerid limit 1) as commercialname FROM toffergroup a, toffergroupprio b where a.offergroupid = b.offergroupid and a.isdone = 'NO' and b.startdate <= CURRENT_DATE() and b.enddate >= CURRENT_DATE() order by a.modifieddate asc limit " + Offers, objCon);
objDa.Fill(objDS,"offers");
for(int i = 0; i < objDS.Tables["offers"].Rows.Count; ++i)
{
string offergroupid = objDS.Tables["offers"].Rows[i]["offergroupid"].ToString();
DB.Query("update toffergroup set displays = displays + 1 where offergroupid = " + offergroupid);
}
objDa.SelectCommand = new MySqlCommand("select a.lotteryid, a.lotteryname, if(length(a.lotterytext)>=90,concat(left(a.lotterytext,90),\"...\"), a.lotterytext) as lotterytext, a.smscode, cast(a.enddate as char(10)) enddate, a.tariff, a.imagename, if((select count(*) from tlotteryowners x where x.lotteryid = a.lotteryid)=1,(select customerid from tlotteryowners x where x.lotteryid = a.lotteryid),\"\") as customerid, (select y.commercialname from tlotteryowners x, tcustomer y where x.lotteryid = a.lotteryid and x.customerid = y.customerid limit 1) as commercialname from tlottery a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by rand() desc limit " + Lotteries, objCon);
objDa.Fill(objDS,"lotteries");
objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, if(length(a.offertext)>=90,concat(left(a.offertext,90),\"...\"), a.offertext) as offertext, a.smscode, a.modifieddate, (select x.customerid from toffergroupmembers x where x.offergroupid = a.offergroupid limit 1) as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' order by a.sortdate desc limit 7", objCon);
objDa.Fill(objDS,"topten");
objDa.Dispose();
objCon.Close();
objCon.Dispose();
return objDS.GetXml().ToString();
}
Description: Hi, I just upgraded to server version 5 after using 4.11 but i got a lot off issues/problems. How ever one problem i havent solved yet is the "already an open DataReader" issue. Im using ConnectionString: Data Source=192.168.2.37;Database=dbj;User ID=root;Password=******; Allow Zero Datetime=true;pooling=false; The code in "How to repeat" works fine with MySql 4.11 but with 5.0 it wont pass. How to repeat: public static string Welcome(int Offers, int Lotteries) { MySql.Data.MySqlClient.MySqlConnection objCon = new MySqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBConnectionString"]); MySql.Data.MySqlClient.MySqlDataAdapter objDa = new MySqlDataAdapter(); MySql.Data.MySqlClient.MySqlDataAdapter objDa2 = new MySqlDataAdapter(); System.Data.DataSet objDS = new DataSet(); objDS.DataSetName = "start"; //objDS.Tables.Add(NewTable("biggest","select bannerid, imagename as src, description as alt, url from tbanner where displaystart <= CURRENT_DATE() and displayend >= CURRENT_DATE() order by modifieddate asc limit 1")); //objDS.Tables.Add(NewTable("latest","select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.sortdate desc limit 10")); objDa.SelectCommand = new MySqlCommand("select bannerid, imagename as src, description as alt, url from tbanner where displaystart <= CURRENT_DATE() and displayend >= CURRENT_DATE() order by modifieddate asc limit 1", objCon); objDa.Fill(objDS,"biggest"); objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.sortdate desc limit 10", objCon); objDa.Fill(objDS,"latest"); objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, a.offertext, a.smscode, a.modifieddate, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers, (select count(*) from tsms x where x.message = a.smscode and x.modifieddate >= (select adddate(current_date,-7)) and smscode != '') as smslastweek from toffergroup a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by smslastweek desc limit 10", objCon); objDa.Fill(objDS,"mostpopular"); objDa.SelectCommand = new MySqlCommand("select a.lotteryid, a.lotteryname, if(length(a.lotterytext)>=90,concat(left(a.lotterytext,90),\"...\"), a.lotterytext) as lotterytext, a.smscode, if((select count(*) from tlotteryowners x where x.lotteryid = a.lotteryid)=1,(select customerid from tlotteryowners x where x.lotteryid = a.lotteryid),\"\") as customerid, (select y.commercialname from tlotteryowners x, tcustomer y where x.lotteryid = a.lotteryid and x.customerid = y.customerid limit 1) as commercialname from tlottery a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by a.enddate limit 10", objCon); objDa.Fill(objDS,"newlotteries"); for(int i = 0; i < objDS.Tables["biggest"].Rows.Count; ++i) { string bannerid = objDS.Tables["biggest"].Rows[i]["bannerid"].ToString(); DB.Query("update tbanner set displays = displays + 1 where bannerid = " + bannerid); } objDa.SelectCommand = new MySqlCommand("select distinct a.offergroupid, a.groupname, a.imagename as offerimage, if(length(a.offertext)>=90,concat(left(a.offertext,90),\"...\"), a.offertext) as offertext, a.smscode, cast(a.enddate as char(10)) enddate, a.tariff, a.displays, if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select customerid from toffergroupmembers x where x.offergroupid = a.offergroupid),\"\") as customerid,if((select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid)=1,(select y.imagename from toffergroupmembers x, tcustomer y where x.offergroupid = a.offergroupid and x.customerid = y.customerid),\"ico001.jpg\") as imagename, (select y.commercialname from toffergroupmembers x, tcustomer y where x.offergroupid = a.offergroupid and x.customerid = y.customerid limit 1) as commercialname FROM toffergroup a, toffergroupprio b where a.offergroupid = b.offergroupid and a.isdone = 'NO' and b.startdate <= CURRENT_DATE() and b.enddate >= CURRENT_DATE() order by a.modifieddate asc limit " + Offers, objCon); objDa.Fill(objDS,"offers"); for(int i = 0; i < objDS.Tables["offers"].Rows.Count; ++i) { string offergroupid = objDS.Tables["offers"].Rows[i]["offergroupid"].ToString(); DB.Query("update toffergroup set displays = displays + 1 where offergroupid = " + offergroupid); } objDa.SelectCommand = new MySqlCommand("select a.lotteryid, a.lotteryname, if(length(a.lotterytext)>=90,concat(left(a.lotterytext,90),\"...\"), a.lotterytext) as lotterytext, a.smscode, cast(a.enddate as char(10)) enddate, a.tariff, a.imagename, if((select count(*) from tlotteryowners x where x.lotteryid = a.lotteryid)=1,(select customerid from tlotteryowners x where x.lotteryid = a.lotteryid),\"\") as customerid, (select y.commercialname from tlotteryowners x, tcustomer y where x.lotteryid = a.lotteryid and x.customerid = y.customerid limit 1) as commercialname from tlottery a where a.isdone = 'NO' and a.startdate <= CURRENT_DATE() and a.enddate >= CURRENT_DATE() order by rand() desc limit " + Lotteries, objCon); objDa.Fill(objDS,"lotteries"); objDa.SelectCommand = new MySqlCommand("select a.offergroupid, cast(a.groupname as char(23)) as groupname, if(length(a.offertext)>=90,concat(left(a.offertext,90),\"...\"), a.offertext) as offertext, a.smscode, a.modifieddate, (select x.customerid from toffergroupmembers x where x.offergroupid = a.offergroupid limit 1) as customerid, (select count(*) from toffergroupmembers x where x.offergroupid = a.offergroupid) as customers from toffergroup a where a.isdone = 'NO' order by a.sortdate desc limit 7", objCon); objDa.Fill(objDS,"topten"); objDa.Dispose(); objCon.Close(); objCon.Dispose(); return objDS.GetXml().ToString(); }