I have an desktop application built with vb.net and mysql and it was working good but yesterday I faced problem when I execute a select command:
dim xSql As String = "SELECT
p.ID,
p.TheName0,
(SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att
INNER JOIN tbl_students st
ON st.ID = att.StudentID
INNER JOIN tbl_groups_classes cls
ON cls.ID = att.ClassID
WHERE st.ParentID = p.ID
and cls.TheDate BETWEEN @Date1 and @Date2
and att.TheStatus <> 'غائب'
) as CurrMost,
(SELECT IFNULL(SUM(att.S_FinalAmount),0) From tbl_groups_classes_att att
INNER JOIN tbl_students st
ON st.ID = att.StudentID
INNER JOIN tbl_groups_classes cls
ON cls.ID = att.ClassID
WHERE st.ParentID = p.ID and cls.TheDate< @Date1 and att.TheStatus <> 'غائب'
) as PrevMost,
(SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
AND pay.TheDate BETWEEN @Date1 and @Date2
) as CurrMadf,
(SELECT IFNULL(SUM(pay.TheAmount),0) From tbl_parents_payments pay Where p.ID = pay.ParentID
AND pay.TheDate < @Date1
) as PrevMadf,
(SELECT CurrMost PrevMost) as AllMost,
(SELECT CurrMadf PrevMadf) as AllMadf,
(SELECT AllMost - AllMadf) AS FinalTotal
from tbl_parents p"
xDS = New Ds_Edsa
Dim xCMD4 As New MySqlCommand(xSql, Conn)
xCMD4.Parameters.Add("@Date1", MySqlDbType.Date).Value = CDate(xxFrom)
xCMD4.Parameters.Add("@Date2", MySqlDbType.Date).Value = CDate(xxTo)
xDadp = New MySqlDataAdapter(xCMD4)
xDadp.Fill(xDS.Tables("AllPar"))
the problem only in the online mysql hosting but when I run the command on localhost (mysql) it's working!
before everything was good so it's a new problem
the error that I get is (fatal error encountered during command execution)
CodePudding user response:
I think that your SQL is very complicated, so you should know the count of the records that you are processing and I think you should separate the commands to get each part in one command and then merge them in one data table inside the dataset.