我试图将20000多条记录从MySQL加载到vb.net中的Datatable,但我遇到了一个错误(在读取数据时遇到了致命错误(,尽管当我使用相同的SQL语句获取带有"其中条件";为了获得更少的数据,事实上,我得到了大约6000条没有任何问题或错误的记录,当我在谷歌上搜索这个问题时,我找到了一些预期的解决方案,比如:
- 设置net_write_timeout=99999
- 设置net_read_timeout=99999
我从MySql的变量中更改了它们,但错误仍然存在,并且在我的连接字符串中,我将连接超时设置为500;
关于我的问题有什么建议吗?以及如何在用于获取数据的SQL语句中直接使用(set net_write_timeout=99999(?
我的SQL语句:
SELECT
IF (@acc = d.RegdAccID, @ss, @ss := 0) AS x1,
(select (@s := @s + 1)) AS xCounter,
d.RegDate AS xDate,
d.AccName AS xAccName,
IF (CAST(@ss := @ss + (d.RegdM2-d.RegdD2) AS DECIMAL(15,2))
<> d.RegdMtbkRaseed AND d.RegdMtbkStatus='Yes',
'No',d.RegdMtbkStatus) AS xNewMtbkStatus,
IF(d.RegType = 'Fat',
(
SELECT AccName
FROM tblfatora
INNER JOIN tblaccounts ON tblaccounts.AccID = tblfatora.FatoraReso
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xReso,
IF(d.RegType = 'Fat',
(
SELECT AccName
FROM tblfatora
INNER JOIN tblaccounts ON tblaccounts.AccID = tblfatora.FatoraCustomer
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xCus,
IF(d.RegType = 'Fat',
(
SELECT AccName
FROM tblfatora
INNER JOIN tblaccounts ON tblaccounts.AccID = tblfatora.FatoraDriver
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xDriver,
IF(d.RegType = 'Fat',
(
SELECT FatoraQuan
FROM tblfatora
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xQuan,
IF(d.RegType = 'Fat',
(
SELECT FatoraPurPrice
FROM tblfatora
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xPurPrice,
IF(d.RegType = 'Fat',
(
SELECT FatoraSalePrice
FROM tblfatora
WHERE tblfatora.FatoraRef = d.RegRef
)
,NULL) AS xSalePrice,
(@acc:= d.RegdAccID) AS xacc
FROM (
SELECT
a.RegDate,
a.RegID,
a.RegRef,
a.RegType,
b.RegdID,
b.RegdAccID,
b.RegdM1,
b.RegdD1,
b.RegdM2,
b.RegdD2,
b.RegdNo,
b.RegdMtbkRaseed,
b.RegdMtbkStatus,
b.RegdMtbkNotes,
n.AccName
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
INNER JOIN tblaccounts n ON n.AccID = b.RegdAccID
WHERE (b.RegdM2>0 or b.RegdD2>0)
) d
CROSS JOIN (SELECT @s := 0) c
CROSS JOIN (SELECT @ss := 0) cc
CROSS JOIN (SELECT @acc := 0) aa
ORDER BY
d.RegdAccID,
d.RegDate,
d.RegID,
d.RegdID
我要填写数据表的代码:
Dim xDa As MySqlDataAdapter
Dim xCmd As MySqlCommand
Dim MyVar_Dt_Main As New DataTable
xCmd = New MySqlCommand(MyVar_SqlStr_Main, Conn)
xDa = New MySqlDataAdapter(xCmd)
xDa.Fill(MyVar_Dt_Main)
xDa.Dispose()
xCmd.Dispose()
您要查找的是:xCmd.CommandTimeout=500