aspose file tools*
The moose likes JDBC and the fly likes SQL and JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL and JSP" Watch "SQL and JSP" New topic
Author

SQL and JSP

Bob Kimmel
Greenhorn

Joined: Aug 27, 2003
Posts: 26
I'm fairly new at program and I'm
working by myself mostly. I'v been sussusful
in writting a JSP to do inserts and Deletes.
My problem it Updates and create seaches by
what user enter in the form. I'll star with the
Update I've just about given up:-)
----------------------------------------------------------------------
latest compile error (I've work through many) ORA-00936: missing expression
---------------------------------------------------------------------
Here is my code:<%@page import="java.util.*"
%>
<HTML>
<HEAD> <TITLE>Listing the content of a Database</TITLE> </HEAD>
<BODY>
<P><img src="CCEVS_Banner.jpg" width="760" height="65">
<table width="100%" height="30" border="0">
<tr>
<td width="14%"><div align="center"><font color="#666666" size="3" face="Arial, Helvetica, sans-serif"><strong><a href="project
.html">PROJECTS</a></strong></font></div></td>
<td width="19%"><div align="center"><font color="#FFFFFF" face="Arial, Helvetica, sans-serif"><strong><a href="resources.html">RESOURCES</a></strong></font></div></td>
<td width="13%"><div align="center"><font color="#FFFFFF" face="Arial, Helvetica, sans-serif"><strong><a href="cctls.html">CCTLs</a></strong></font></div></td>
<td width="19%"><div align="center"><font color="#FFFFFF" size="3" face="Arial, Helvetica, sans-serif"><strong><a href="certlogo.html">CERT.
LOGO</a></strong></font></div></td>
<td width="17%"><div align="center"><font color="#FFFFFF" face="Arial, Helvetica, sans-serif"><strong><a href="orod.html">ORs
/ ODs</a></strong></font></div></td>
<td width="18%"><div align="center"><font color="#FFFFFF" size="3" face="Arial, Helvetica, sans-serif"><strong><a href="website.html">WEBSITE</a></strong></font></div></td>
</tr>
</table>
<P><strong><font size="5">Update Project Database Content </font></strong>
<FORM ACTION="ProjectUpdate.jsp" METHOD="POST">

<p><font size="4"><strong>Name of Project to be Updated:</strong></font></p>
<p>PRODUCT_NAME:
<INPUT TYPE=TEXT NAME=PRODUCT_NAME>
</p>
<p><strong><font color="#000000" size="4">Updated Information:</font></strong><BR>
PROJECT_ID:
<INPUT TYPE=TEXT NAME=PROJECT_ID>
<BR>
MSR :
<INPUT TYPE=TEXT NAME=MSR>
<BR>
PROTECTION_PROFILE :
<INPUT TYPE=TEXT NAME=PROTECTION_PROFILE>
<BR>
HRS:
<INPUT TYPE=TEXT NAME=HRS>
<BR>
EAL_ID:
<INPUT TYPE=TEXT NAME=EAL_ID>
<BR>
PPs_STs:
<INPUT TYPE=TEXT NAME=PP_ST>
<BR>
Technology_ID:
<INPUT TYPE=TEXT NAME=TECHNOLOGY_ID>
<BR>
Sponsor:
<INPUT TYPE=TEXT NAME=SPONSOR>
<BR>
Status:
<INPUT TYPE=TEXT NAME=STATUS>
<BR>
<INPUT TYPE=Submit VALUE="Update Project">
<input type="reset" name="Reset" value="Reset">
</p>
</FORM>
<HR>
<P> <font size="5"><strong>Content of the Project Database</strong>: </font><font size="5"></font>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR>
<TD>PRODUCT_NAME </TD>
<TD>PROJECT_ID </TD>
<TD>MSR </TD>
<TD>PROTECTION_PROFILE </TD>
<TD>HRS </TD>
<TD>EAL_ID </TD>
<TD>PP_ST </TD>
<TD>TECHNOLOGY_ID </TD>
<TD>SPONSOR</TD>
<TD>STATUS</TD>
</TR>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection connection = java.
sql.DriverManager.getConnection("jdbc racle:thin:@52796e:1521:ccevs","user name", "passwd");
java.sql.Statement statement = connection.createStatement();
Enumeration parameters = request.getParameterNames();
if(parameters.hasMoreElements()) {

String PRODUCT_NAME = request.getParameter("PRODUCT_NAME");
String PROJECT_ID = request.getParameter("PROJECT_ID");
String MSR = request.getParameter("MSR");
String PROTECTION_PROFILE = request.getParameter("PROTECTION_PROFILE");
String HRS = request.getParameter("HRS");
String EAL_ID = request.getParameter("EAL_ID");
String PP_ST = request.getParameter("PP_ST");
String TECHNOLOGY_ID = request.getParameter("TECHNOLOGY_ID");
String SPONSOR = request.getParameter("SPONSOR");
String STATUS = request.getParameter("STATUS");


String query3 = ("UPDATE CCEVS_PROJECTS_REGISTRY set PRODUCT_NAME="+PRODUCT_NAME+",PROJECT_ID="+PROJECT_ID+",MSR ="+MSR+",PROTECTION_PROFILE="+PROTECTION_PROFILE+",HRS="+HRS+",PP_ST="+PP_ST+",TECHNOLOGY_ID="+TECHNOLOGY_ID+",SPONSOR="+SPONSOR+",STATUS="+STATUS+" where PRODUCT_NAME = "+PRODUCT_NAME+" ");


System.out.println(query3);
java.sql.Statement statement3 = connection.createStatement();
statement3.executeQuery(query3);
java.sql.ResultSet resultset3 = statement3.getResultSet();
}
java.sql.ResultSet columns = statement.executeQuery("SELECT PRODUCT_NAME, PROJECT_ID, MSR, PROTECTION_PROFILE, HRS, EAL_ID, PP_ST, TECHNOLOGY_ID, SPONSOR, STATUS FROM CCEVS_PROJECTS_REGISTRY");
while(columns.next()) {
String PRODUCT_NAME = columns.getString("PRODUCT_NAME");
String PROJECT_ID = columns.getString("PROJECT_ID");
String MSR = columns.getString("MSR");
String PROTECTION_PROFILE = columns.getString("PROTECTION_PROFILE");
String HRS = columns.getString("HRS");
String EAL_ID = columns.getString("EAL_ID");
String PP_ST = columns.getString("PP_ST");
String TECHNOLOGY_ID = columns.getString("TECHNOLOGY_ID");
String SPONSOR = columns.getString("SPONSOR");
String STATUS = columns.getString("STATUS");%>

<TR>
<TD> <%= PRODUCT_NAME %> </TD>
<TD> <%= PROJECT_ID %> </TD>
<TD> <%= MSR %> </TD>
<TD> <%= PROTECTION_PROFILE%> </TD>
<TD> <%= HRS %> </TD>
<TD> <%= EAL_ID %> </TD>
<TD> <%= PP_ST %> </TD>
<TD> <%= TECHNOLOGY_ID %> </TD>
<TD> <%= SPONSOR %> </TD>
<TD> <%= STATUS %> </TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
Hope someone can help figure these out.
Thanks in advance,
Bob
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Can you tell the line num?


Groovy
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60992
    
  65

Hi Bob,
Welcome to the Ranch!
Here a few tips to help you get the most out of these forums:
1) There's a checkbox in the Options section of the 'post' page labeled 'Disable smilies'. Be sure to check it when posting code so that phrases like jdbcracle don't get turned into happy little faces.
2) Be sure to use the UBB code tags (helpful buttons are below the text area) to surround your code. That way, the formatting will be preserved. Many members, my self included, will not read long expanses of unformatted code. It's just too difficult.
3) When you make an error in posting, you can go back and edit the post to correct it.
Hope this helps,
bear
JSP Forum Bartedner


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Bob Kimmel
Greenhorn

Joined: Aug 27, 2003
Posts: 26
Sorry that was pretty bad.
Now I'm getting:
org.apache.jasper.JasperException: ORA-00933: SQL command not properly ended

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60992
    
  65

Well sure enough, the error message says it all. Your SELECT statement is incomplete.
With the obligatory statement of my opinion that doing SQL in JSPs is a horrible thing to do to yourself, I'm going to move this discussion over to the JDBC forum since it's more about database access than JSP.
bear
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Hi Bob
I support Bear Bibeault's opinion. I did not check the complete code. But I found this in a quick look


Can you remove '(' and ')' then try
--------------
Sainudheen
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL and JSP