ลองBreak Pointดูแล้ว DataSet ไม่มีข้อมู ลแต่เอาไปรันอีกเครื่องนึงดูปรากฎว่าใช้ได้ปกติ
ฐานข้อมูลเดียวกันคิวรี่ใน DB ดูแล้วมีข้อมูลจริงแต่มันไม่เข้า DataSet
จากโค้ดข้างล่างถ้าเปลี่ยนเป็น strSQL = "SELECT * FROM customers"; ข้อมูลก็เข้า DataSet ปกติ
Code (C#)
if (dataGridView1.CurrentRow != null)
{
String strCusid = dataGridView1.CurrentRow.Cells["gv_cusid"].Value.ToString();
strSQL = "SELECT CONCAT(cus_id,' ',cus_name) AS cus_id," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 1 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m1," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 2 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m2," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 3 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m3," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 4 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m4," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 5 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m5," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 6 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m6," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 7 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m7," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 8 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m8," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 9 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m9," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 10 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m10," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 11 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m11," +
"(SELECT IF(SUM(po2.po_price) IS NULL,0.00,FORMAT(SUM(po2.po_price),2)) FROM po,po2 WHERE po.po_cusid = '" + strCusid + "' AND 12 = MONTH(STR_TO_DATE(po.po_date,'%d/%m/%Y')) AND po.po_id = po2.po_poid) AS cus_m12 " +
"FROM customers WHERE customers.cus_id = '" + strCusid + "'";
objCmd = new MySqlCommand(strSQL, hm.objConn());
dtAdapter = new MySqlDataAdapter(objCmd);
dtAdapter.Fill(ds, "customers2");
dataGridView2.DataSource = ds.Tables["customers2"];
hm.closeConn();
}
Tag : .NET, MySQL, Win (Windows App), C#, VS 2008 (.NET 3.x)