I have the following subroutine to get data from table:
'GetData
Public Sub GetData(ByVal SqlStr As String, ByVal xDt As DataTable, ByVal xPar() As MySqlParameter)
Using xCmd As New MySqlCommand() With {
.CommandType = CommandType.Text,
.CommandText = SqlStr,
.Connection = _ServerConnStr
}
If xPar IsNot Nothing Then
For i As Integer = 0 To xPar.Length - 1
xCmd.Parameters.Add(xPar(i))
Next
End If
Using xDa As New MySqlDataAdapter(xCmd)
xDa.Fill(xDt)
End Using
xDt.Dispose()
End Using
End Sub
and I use that sub to get data from tblfatora1:
Public Sub MySql_GetFhMain()
xDtMain = New DataTable()
Dim xPar(2) As MySqlParameter
xPar(0) = New MySqlParameter("@Date1", MySqlDbType.Date) With {
.Value = Date1}
xPar(1) = New MySqlParameter("@Date2", MySqlDbType.Date) With {
.Value = Date2}
xPar(2) = New MySqlParameter("@FhReso", DbType.Int32) With {
.Value = FhReso}
xSqlStr = "SELECT
FhID, FhRef, FhCode, FhDate, FhQuan, FhPurPrice,
FhPurTotal, FhCarNo, FhResoDetails, FhPalletQuan,
FhPalletPrice, accreso.AccName AS ResoName,
accdriv.AccName AS DriverName, CONCAT(CategoryName,'-',ProductName) AS ProductName,
(SELECT GROUP_CONCAT(AccName) FROM tblaccounts
INNER JOIN tblfatora2 ON
tblfatora2.FbCus = tblaccounts.AccID
where FIND_IN_SET(AccID, FbCus) AND tblfatora2.FhRef = tblfatora1.FhRef)AS TheCustomers,
(SELECT GROUP_CONCAT(PlaceName) FROM tblplaces
INNER JOIN tblfatora2 ON
tblfatora2.FbCusPlace = tblplaces.PlaceID
where FIND_IN_SET(PlaceID, FbCusPlace) AND tblfatora2.FhRef = tblfatora1.FhRef)AS ThePlaces ,
(FhQuan - (SELECT IFNULL(SUM(FbQuan), 0) FROM tblfatora2 WHERE FhRef = tblfatora1.FhRef)) AS TheRemain,
ProductPallet, PlaceName, FhDriver, FhProduct, FhReso, FhResoPlace
FROM tblfatora1
INNER JOIN tblproducts ON tblproducts.ProductID = tblfatora1.FhProduct
INNER JOIN tblaccounts accreso ON accreso.AccID = tblfatora1.FhReso
INNER JOIN tblaccounts accdriv ON accdriv.AccID = tblfatora1.FhDriver
INNER JOIN tblcategories cat ON cat.CategoryID = tblproducts.ProductCategory
INNER JOIN tblcurrencies curr ON accdriv.AccCurrID = curr.CurrencyID
INNER JOIN tblplaces plc ON plc.PlaceID = tblfatora1.FhResoPlace
WHERE (FhDate Between @Date1 And @Date2)"
xClsMySql.GetData(xSqlStr, xDtMain, xPar)
End Sub
but sometimes when I run it I get the following error:
although when I check the rows count I see that there are many rows so the DtMain has rows:
as I said that error sometimes happens, not always, I tried to see the reason of the error but I couldn't