สอบถามผู้รู้หน่อยครับ ผมทำ Query ใน MS Access ได้ แต่มาเขียนใน ASP ใช้ SQL Statement เหมือนกัน ทำไมไม่ได้ครับ
SQL Statement ใน MS Access
Code
SELECTDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD, Max(RSUPPLYCTL.SPPYY) AS MaxOfSPPYY, Max(RSUPPLYCTL.SPPMM) AS MaxOfSPPMM, Max(RSUPPLYCTL.SPPDD) AS MaxOfSPPDD, Max(RINVDETAIL.D_ADD_YYYY) AS MaxOfD_ADD_YYYY, Max(RINVDETAIL.D_ADD_MM) AS MaxOfD_ADD_MM, Max(RINVDETAIL.D_ADD_DD) AS MaxOfD_ADD_DD, CVDate(Max(IIf([SPPDD]<>0,CDate([SPPDD] & "/" & [SPPMM] & "/" & [SPPYY]),""))) AS SUP_PLAN, CVDate(Max(IIf([D_ADD_DD]<>0,CDate([D_ADD_DD] & "/" & [D_ADD_MM] & "/" & [D_ADD_YYYY]),""))) AS SUB_ACT, [SUP_PLAN]-[SUB_ACT] AS SUP_DELAY FROM ([DAY-Evaluation] LEFT JOIN RSUPPLYCTL ON ([DAY-Evaluation].P_NO = RSUPPLYCTL.PITEMNO) AND ([DAY-Evaluation].PO = RSUPPLYCTL.PONO)) LEFT JOIN RINVDETAIL ON ([DAY-Evaluation].PO = RINVDETAIL.D_PORD_NBR) AND ([DAY-Evaluation].P_NO = RINVDETAIL.D_SPLPITEM) GROUP BYDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD HAVING (((RINVDETAIL.D_TRANSCD)='20')) ORDER BYDAYEVALUATION.VENDOR,DAYEVALUATION.PO;
ผมเอามาประยุกต์ใช้ใน ASP ดังนี้ครับ
Code
dd1 = "/"
Set Conn8 = Server.CreateObject("ADODB.Connection")
Conn8.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE="&Server.Mappath("DAYEVALUATION.mdb")
Set rs8 = Server.CreateObject("ADODB.Recordset")
sql8 = "SELECTDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD, Max(RSUPPLYCTL.SPPYY) AS MaxOfSPPYY, Max(RSUPPLYCTL.SPPMM) AS MaxOfSPPMM, Max(RSUPPLYCTL.SPPDD) AS MaxOfSPPDD, Max(RINVDETAIL.D_ADD_YYYY) AS MaxOfD_ADD_YYYY, Max(RINVDETAIL.D_ADD_MM) AS MaxOfD_ADD_MM, Max(RINVDETAIL.D_ADD_DD) AS MaxOfD_ADD_DD, CVDate(Max(IIf([SPPDD]<>0,CDate([SPPDD] & '"&dd1&"' & [SPPMM] & '"&dd1&"' & [SPPYY]),""))) AS SUP_PLAN, CVDate(Max(IIf([D_ADD_DD]<>0,CDate([D_ADD_DD] & '"&dd1&"' & [D_ADD_MM] & '"&/&"' & [D_ADD_YYYY]),""))) AS SUB_ACT, [SUP_PLAN]-[SUB_ACT] AS SUP_DELAY FROM ([DAY-Evaluation] LEFT JOIN RSUPPLYCTL ON ([DAY-Evaluation].P_NO = RSUPPLYCTL.PITEMNO) AND ([DAY-Evaluation].PO = RSUPPLYCTL.PONO)) LEFT JOIN RINVDETAIL ON ([DAY-Evaluation].PO = RINVDETAIL.D_PORD_NBR) AND ([DAY-Evaluation].P_NO = RINVDETAIL.D_SPLPITEM) GROUP BYDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD HAVING (((RINVDETAIL.D_TRANSCD)='20')) ORDER BYDAYEVALUATION.VENDOR,DAYEVALUATION.PO"
rs8.open sql8,Conn8,1,3
*** Error ดังนี้ครับ *************************************************************************
Microsoft VBScript compilation error '800a03ea'
Syntax error
/evaluation/dayevaluation.asp, line 264
Code
sql8 = " SELECTDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD, Max(RSUPPLYCTL.SPPYY) AS MaxOfSPPYY, Max(RSUPPLYCTL.SPPMM) AS MaxOfSPPMM, Max(RSUPPLYCTL.SPPDD) AS MaxOfSPPDD, Max(RINVDETAIL.D_ADD_YYYY) AS MaxOfD_ADD_YYYY, Max(RINVDETAIL.D_ADD_MM) AS MaxOfD_ADD_MM, Max(RINVDETAIL.D_ADD_DD) AS MaxOfD_ADD_DD, CVDate(Max(IIf([SPPDD]<>0,CDate([SPPDD] & '"&dd1&"' & [SPPMM] & '"&dd1&"' & [SPPYY]),""))) AS SUP_PLAN, CVDate(Max(IIf([D_ADD_DD]<>0,CDate([D_ADD_DD] & '"&dd1&"' & [D_ADD_MM] & '"&/&"' & [D_ADD_YYYY]),""))) AS SUB_ACT, [SUP_PLAN]-[SUB_ACT] AS SUP_DELAY FROM ([DAY-Evaluation] LEFT JOIN RSUPPLYCTL ON ([DAY-Evaluation].P_NO = RSUPPLYCTL.PITEMNO) AND ([DAY-Evaluation].PO = RSUPPLYCTL.PONO)) LEFT JOIN RINVDETAIL ON ([DAY-Evaluation].PO = RINVDETAIL.D_PORD_NBR) AND ([DAY-Evaluation].P_NO = RINVDETAIL.D_SPLPITEM) GROUP BYDAYEVALUATION.VENDOR,DAYEVALUATION.P_NO,DAYEVALUATION.PO,DAYEVALUATION.DUE,DAYEVALUATION.QTY,DAYEVALUATION.RCV, RINVDETAIL.D_TRANSCD HAVING (((RINVDETAIL.D_TRANSCD)='20')) ORDER BYDAYEVALUATION.VENDOR,DAYEVALUATION.PO"
รบกวนผู้รู้ช่วยแนะนำด้วยครับ ว่าผมควรเขียน SQL Statement ใน ASP อย่างไรถึงจะได้เหมือนใน MS Access ครับ
Load balance : Server 00
