CODEWITHSUNDEEP
javamysqljsp
hinata
hinata

Reputation: 385

Getting Values from MySQL database in JSP

Following code is for getting PONos from the MySQL database and view them in buttons.

When user click on a (PONo viewing) button , another page appears to view Purchase order information for that specific PONo.(viewpo.jsp)

But as in the screenshot when I click on 104 shows 102 Purchase order details. When I click on 102 shows 102 Purchase order details. enter image description here How to fix this error?

 <%
    String CONN_STRING = "jdbc:mysql://localhost/pmsdb";
    String USERNAME = "dbuser";
    String PASSWORD = "dbpassword";

    Connection conn=null;
    PreparedStatement pst=null;
    ResultSet rs =null;
    ResultSet rs2 =null;
    ResultSet rs3 =null;
    Statement stmt=null;
    Statement stmt2=null;

    Object userr = session.getValue("Username");
    String user = userr.toString();
    Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    String sql0="SELECT * FROM websupplierinfo WHERE username='"+user+"'";
    pst=conn.prepareStatement(sql0);
    rs3=pst.executeQuery();

    if(rs3.next()){
    String companyname = rs3.getString("Company");

    String sql = "SELECT * FROM purchaseorderinfo WHERE Supplier='"+companyname+"' ORDER BY PONo DESC";
   // String sql2 = "SELECT * FROM itemspoinfo WHERE Company='"+companyname+"' ORDER BY PONo DESC";
    stmt = conn.createStatement();
    rs=stmt.executeQuery(sql);

    //stmt2=conn.createStatement();
   // rs2=stmt2.executeQuery(sql2);

    %> 

<div class="row">
    <div class="col-md-12">
        <div class="citydetails">
            <div class="panel panel-primary">
                <div class="panel-heading">Available Purchase Orders</div>
                    <div class="panel-body">
                        <table class="table">
                            <tr>
                                <th>All Purchase Orders</th>


                            </tr>
                            <tr><td><form>
            <div class="form-group">
                <div class="row">
                    <div class="col-sm-6 center-block">
                        <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="All Purchase Orders" onclick="window.open('alladminpo.jsp','_blank','resizable=yes')">
                    </div>
                </div>
            </div>    
        </form></td>
        </tr>    

      <th>Latest Purchase Order Numbers</th>                                  
    <%while(rs.next()){ %>
    <tr>
    <td>
      <form>
            <div class="form-group">
                <div class="row">
                    <div class="col-sm-2 center-block">
                        <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="<%=rs.getString("PONo")%>" onclick="window.open('viewpo.jsp','_blank','resizable=yes')">
                   <% 
          String po = rs.getString("PONo");
          session.putValue("PONo", po);
                   %>
                    </div>
                </div>
            </div>    
        </form>
      <% }}%>
    </td>                             
    </tr>                   
                        </table>
                    </div>
            </div>
        </div>
    </div>
</div>

viewpo.jsp (important codesegments);

    <%
    String CONN_STRING = "jdbc:mysql://localhost/pmsdb";
    String USERNAME = "dbuser";
    String PASSWORD = "dbpassword";

    Connection conn=null;
    ResultSet rs =null;
    ResultSet rs2 =null;

    Statement stmt=null;
    Statement stmt2=null;

    Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    Object po = session.getValue("PONo");
    String pno=po.toString();

    String sql = "SELECT * FROM purchaseorderinfo WHERE PONo='"+pno+"'";
    String sql2 ="SELECT * FROM itemspoinfo WHERE PONo='"+pno+"'";

    stmt = conn.createStatement();
    rs=stmt.executeQuery(sql);

    stmt2=conn.createStatement();
    rs2=stmt2.executeQuery(sql2);
    %>  

<div class="row">
<div class="col-md-12">
<div class="citydetails">
    <div class="panel panel-primary">
      <div class="panel-heading">Purchase Order Details</div>
      <div class="panel-body">
          <table class="table">
              <thead>
                    <tr>
                        <th>Purchase Order <%=session.getValue("PONo") %></th>
                    </tr>   
              </thead>
            <tbody>

                    <tr>

                        <td>
                            <% if(rs.next()){ %>
                            Purchase Order Date : <%= rs.getString("PODate") %><br>
                            Expected Date : <%=rs.getString("ExpectedDate") %> <br>
                            <% } %> 

                            <table class="table">
                                <tbody>
                                    <tr>
                                        <th>Items</th>
                                        <th>Quantity</th>
                                    </tr>
                                     <% if (rs2.next()){ %>
                                    <tr>

                                        <td><%=rs2.getString("ItemName")%></td>
                                        <td><%=rs2.getString("Qty")%></td>

                                    </tr>
                                    <% } %>

Upvotes: 1

Views: 428

Answers (2)

Srinu Chinka
Srinu Chinka

Reputation: 1491

You are replacing the PONo value in the session, it holds only last value. So pass the PONo value as a request parameter to viewpo.jsp.

   <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="<%=rs.getString("PONo")%>" onclick="window.open('viewpo.jsp?PONo=<%=rs.getString("PONo")%>','_blank','resizable=yes')">

And remove the following code in your jsp, because it replacing the value of PONo.

 <% 
      String po = rs.getString("PONo");
      session.putValue("PONo", po);
 %>

In viewpo.jsp we can access the PONo like the following:

String pno=request.getParameter('PONo');

You have to request.getParameter("PONo") wherever you are using session.getValue("PONo");.

Upvotes: 1

Shriram
Shriram

Reputation: 4411

In a while loop you are using 

 String po = rs.getString("PONo");
          session.putValue("PONo", po);

the above statement and setting attribute PONo with latest value , which will be overridden for each value. so everytime you will get the last value fetched from the db. You have to set the value which you have clicked.

Upvotes: 1

Related Questions