Results 1 to 10 of 10
  1. #1
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default Problem in retrieving values from Oracle 10g database

    Hi

    I am trying to retrieve values from database into JSP Page, here is my code

    Java Code:
    <%@ page import="java.sql.*" %>
    <% Class.forName("oracle.jdbc.driver.OracleDriver");%>
    <HTML>
        <HEAD>
            <script language="javascript">
                function editData(PartNo){
                    window.location.href="editDataServlet?PartNo="+PartNo;
                }
                function deleteData(PartNo){
                    window.location.href="deleteData?PartNo="+PartNo;
                }
            </script>
        </HEAD>
        <BODY bgcolor="#FFFFCC">
            <H1 align="center"> Inventory </H1>
                   <center>
                <td align="right" width=37> <strong><img title="Go back" src="Undo.gif" width="20" height="20" onClick="javascript:history.back()" style="vertical-align:middle;" onMouseOver="this.style.cursor='hand';"></strong>
    
                <td align="right" width=38> <strong><img title="Print it" src="Print.gif" width="20" height="20" onClick="javascript:window.print()" style="vertical-align:middle;" onMouseOver="this.style.cursor='hand';">
                    </strong>
            </center>
            <h2 align="center">Records for the selected Part No</h2>
            <%
                        String connectionURL = "jdbc:oracle:thin:@localhost:1521:demo";
                        String driver = "oracle.jdbc.driver.OracleDriver";
                        String user = "root";
                        String pass = "root";
                        Connection connection = null;
                        ResultSet rs;
                        PreparedStatement pst;
                        try {
                            connection = DriverManager.getConnection(connectionURL, user, pass);
                            String PartNo = request.getParameter("PartNo");
                           pst = connection.prepareStatement("select PartNo,SerialNo from batchfile where PartNo = '" + PartNo + "'");
                            rs = pst.executeQuery();
                            //while(rs.next()){
                          if (!rs.next()) {
                                out.println("<br>");
                                out.println("<table align=\"center\" font=\"16\">");
                                out.println("<tr><th>Sorry, Could not find data</th></tr>");
                                out.println("</table>");
    
                            } else { 
    
            %>
    
            <TABLE cellpadding="15" border="1" style="background-color:#ECE5B6" align="center">
                <TR>
                    <TH>Part No</TH>
                    <TH>Serial No</TH>
    
                    <TH>Edit</TH>
                    <TH>Delete</TH>
                </TR>
                <%
                                 do {
                %>
                <TR>
                    <TD> <%= rs.getString(1)%> </TD>
                    <TD> <%= rs.getString(2)%> </TD>
    
                    <TD><input type="button" name="edit" value="Edit" style="background-color:#49743D;font-weight:bold;color:#ffffff;" onclick="editData('<%= rs.getString(1)%>');" ></TD>
                    <TD><input type="button" name="delete" value="Delete" style="background-color:#ff0000;font-weight:bold;color:#ffffff;" onclick="deleteData('<%= rs.getString(1)%>');" ></TD>
                </TR>
                <%                        rs.next();
                                 } while (rs.isAfterLast() != true);
    
                %>
            </TABLE>
            <BR>
            <%
            }
          }
                        catch (Exception e) {
                            e.printStackTrace();
                        }
                       
    
            %>
        </BODY>
    </HTML>
    This is my code, when i enter PartNo and click search, it doen't give me anythg, Please help me am stuck here.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,002
    Rep Power
    20

    Default

    Don't do this in a JSP page.
    Get the data via a call to a servlet, which will call a database layer that will return the data you need. The servlet will then forward that to your JSP for display.

    That will allow you to test the individual bits. It will also ensure that you close your resources properly, since you;re opening a connection there that you never close.

    Do you know whether this is even getting into this code?

  3. #3
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    Thanks for the reply...I will try creating servlet. No, It is not getting into the code...may thats the reason y am not getting the values in my jsp page...anyway will try with servlet..

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,002
    Rep Power
    20

    Default

    I can't even see in that mass of code up there where the form is, and where it is submitting to.

  5. #5
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    Sorry for the huge code...i have two jsp pages first one demo.jsp where i enter the value of partno and click submit, it goes to retrieve.jsp which is the above code...i want to retrieve two values partno and serialno for the given partno thats it...i dont knw where exactly the problem is ...And also i have created two buttons for each row edit and delete...I am now trying to do with servlet...Please help me out:)

    There is no form here..the values retrieved from db with two button at the end of each row edit and delete, when i click edit the whole row will be edited in the next page..del-it will delete.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,002
    Rep Power
    20

    Default

    Right.
    So the problem is almost certainly with the first one.
    What does that form look like?

  7. #7
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    This is the form demo.jsp

    Java Code:
    <HTML>
        <HEAD>
            <TITLE>Edit Master List</TITLE>
        </HEAD>
    
        <BODY>
        <body bgcolor="#FFFFCC">
        <tr>
        <h1 align="center">Edit Master List</h1>
            <center>
           <input type="image" name="Inventory" value="Inventory" src="i.gif">
           <input type="image" name="n" value="n" src="n.gif">
           <input type="image" name="v" value="v" src="v.gif">
           <input type="image" name="e" value="e" src="e.gif">
           <input type="image" name="n" value="n" src="n.gif">
           <input type="image" name="t" value="t" src="t.gif">
           <input type="image" name="o" value="o" src="o.gif">
           <input type="image" name="r" value="r" src="r.gif">
           <input type="image" name="y" value="y" src="y.gif">
       </center>
    </tr>
    <BR>
    
    <center>
        <input type="image" name="Back" value="Back" src="Btn_back.png" onClick="javascript:history.back()">
       <input type="image" name="Home" value="Home" src="Home.png" onClick="javascript:window.location='Home.jsp';">
        <input type="image" name="Master List" value="Master List" src="masterlist.png" onClick="javascript:window.location='masterlist.jsp';" >
        <input type="image" name="Material List" value="Material List" src="materiallist.png" onClick="javascript:window.location='materiallist.jsp';">
        <input type="image" name="Material Order" value="Material Order" src="materialorder.png" onClick="javascript:window.location='materialorder.jsp';">
        <input type="image" name="Show all" value="Show all" src="showall.png" onClick="javascript:window.location='showall.jsp';">
        <input type="image" name="Logout" value="Logout" src="logout.png" onClick="javascript:window.location='logout.jsp';">
    </center>
    
    
        <br><br>
    
    <h2 align="center">Master List</h2>
    <FORM name="basicform" method="post" action="retrieve.jsp">
                <BR>
    
                <center>
                    <TR>
                        <TH width="50%">Please enter your Part No</TH>
                        <TD width="50%"><INPUT TYPE="text" NAME="Please enter your PartNo"></TD>
                    </TR>
                    <TD width="50%"><input type="Submit" name="Search" value="Search"></TD>
                </center>
    
    </FORM>
    </HTML>
    I am getting values when the SQL statement is "select * from batchfile;", with this statement am getting values in my jsp page but when i write select * from batchfile where partno = '"+partno+"'; with this statement it gives me nothg ....Please correct me if am wrong...

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,002
    Rep Power
    20

    Default

    Use the PreparedStatement properly.
    "select PartNo,SerialNo from batchfile where PartNo = ?"

    then use setString() on the prepared statement (presuming partNo is a varchar column) to set the part no.

    However, I would say first off, have you split this out yet? If you have then test the database stuff first, outside of your server as a standalone bit of code. That way you'll know whether it's that code causing the problem or something in the servlet/JSP/client side of things.

    First rule of development, break things down.

  9. #9
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    Thanks for the reply, will create servlet and use prepared statement.

  10. #10
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default

    Thanks i got it with servlet. Thanks alot for the replies.

Similar Threads

  1. Retrieving data from database every 10 secs.
    By vnsuresh.abi in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 03-31-2011, 11:14 AM
  2. inserting date in oracle database
    By jackjosh_in in forum Advanced Java
    Replies: 6
    Last Post: 05-23-2010, 06:38 PM
  3. Replies: 3
    Last Post: 03-19-2010, 12:59 AM
  4. Retrieving image stored as BLOB object in oracle
    By venkateshcoolmoon in forum JDBC
    Replies: 11
    Last Post: 01-17-2010, 06:06 PM
  5. Replies: 4
    Last Post: 12-19-2009, 10:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •