Problem in retrieving values from Oracle 10g database

**techsing14** · 03-30-2011, 08:16 AM

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.

**Tolls** · 03-30-2011, 08:43 AM

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?

**techsing14** · 03-31-2011, 08:38 AM

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..

**Tolls** · 03-31-2011, 08:53 AM

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

**techsing14** · 03-31-2011, 09:06 AM

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.

**Tolls** · 03-31-2011, 09:51 AM

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

**techsing14** · 04-01-2011, 02:32 AM

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...

**Tolls** · 04-01-2011, 08:24 AM

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.

**techsing14** · 04-04-2011, 05:57 AM

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

**techsing14** · 04-13-2011, 01:50 AM

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

Thread: Problem in retrieving values from Oracle 10g database

LinkBack

Thread Tools

Search Thread

Display

Problem in retrieving values from Oracle 10g database

Similar Threads

Retrieving data from database every 10 secs.

inserting date in oracle database

problem to insert data in oracle database

Retrieving image stored as BLOB object in oracle

Components do not bind with oracle database

Bookmarks

Bookmarks

Posting Permissions

Aspose.Pdf.Kit 4.6.0

PixelInvaders 0.1

Fanurio 2.6.1

XWiki 5.0.2

Hilbert II 0.04.07

SchemaCrawler 10.3

Jppf 3.3.2

Redline RPM Library 1.1.12

DataNucleus AccessPlatform 3.1.5

Joget Workflow v4.0 BETA