JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » Servlet/JSP 討論區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友   
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 多重條件查詢--SQL 敘述句無效
j_lo2005





發文: 48
積分: 0
於 2007-12-20 14:59 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
在多重條件查詢下(SQL語法是用連接的方式),執行時出現錯誤。
但我試著使用
1
<%=strSQL %>

將輸出在網頁上SQL CODE, 放在ORACLE Sql*上執行又正常。
可否請教前輩們指導!
謝謝
程式碼
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<%@ page contentType="text/html;charset=Big5" import="java.sql.*" %>
<%
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection con =DriverManager.getConnection("jdbc:oracle:thin:@");
  Statement statement = con.createStatement();
  ResultSet rs =null;
 %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<%
//==============================================================================
String   strSQL="Select J.IQR_NO BatchNo,J.ITEM_NO MaterialNo,S.INSPECTION_LOT_NO SapNo,S.PIECES_PER_CASE SapQty,S.SAMPLE_QTY Sampling,S.QUANTITY OkQty,S.USAGE_DECISION_CODE Judgement,to_char(S.GR_DATE,'yyyy/mm/dd')  IncomingDate,J.SHELF_LIVE ShelfLive,J.SUPPLIER_NAME SupplierName from JOBS J INNER JOIN SAMPLES S ON J.JOB_ID=S.JOB_ID";   
String   str=null;   
Boolean isFirst=true;
  str =(String)request.getParameter("BatchNo");
  if   (str!=null){   
        if(isFirst==true){   
            strSQL+=" where J.IQR_NO='"+str+"'   ";   
            isFirst=false;   
        }   
  }   
  str =(String) request.getParameter("MaterialType");
  if   (str!=null){   
       if(isFirst==true){   
            strSQL+=" where J.ITEM_NO='"+str+"'   ";   
            isFirst=false;   
        }else{   
            strSQL+="   and  J.ITEM_NO='"+str+"'   ";   
        }   
  }   
    str =(String) request.getParameter("VenderNo");
  if   (str!=null){   
        if(isFirst==true){   
            strSQL+="where   J.SUPPLIER_REF='"+str+"'   ";   
            isFirst=false;   
        }else{   
            strSQL+="   and   J.SUPPLIER_REF='"+str+"'   ";   
        }   
  } 
 rs=statement.executeQuery("strSQL");
 


錯誤訊息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
HTTP Status 500 - 
 
--------------------------------------------------------------------------------
 
type Exception report
 
message 
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception 
 
org.apache.jasper.JasperException: Exception in JSP: /05_R_RM.jsp:43
 
40:             strSQL+="   and   J.SUPPLIER_REF='"+str+"'   ";   
41:         }   
42:   } 
43:  rs=statement.executeQuery("strSQL");
44: //==============================================================================
45: while(rs.next())
46: {
 
Stacktrace:
  org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:467)
  org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
  org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
  org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
root cause 
 
javax.servlet.ServletException: ORA-00900: SQL 敘述句無效
 
  org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
  org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
  org.apache.jsp._05_005fR_005fRM_jsp._jspService(_05_005fR_005fRM_jsp.java:153)
  org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
  org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
  org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
  org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
root cause 
 
java.sql.SQLException: ORA-00900: SQL 敘述句無效
 
  oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
  oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
  oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
  oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
  oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
  oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)
  oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
  oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:634)
  org.apache.jsp._05_005fR_005fRM_jsp._jspService(_05_005fR_005fRM_jsp.java:87)
  org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
  org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
  org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
  org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.23 logs.
 
--------------------------------------------------------------------------------
 
Apache Tomcat/5.5.23


reply to postreply to post
作者 Re:多重條件查詢--SQL 敘述句無效 [Re:j_lo2005]
lterryh





發文: 1
積分: 0
於 2007-12-20 17:56 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
1
rs=statement.executeQuery("strSQL");

換成
1
rs=statement.executeQuery(strSQL);


reply to postreply to post
» JWorld@TW »  Servlet/JSP 討論區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8