return book

[sarathd kumar]

hi..
i'm doing library management project this module is return book.

<form name="book_return" method="post" action="return.jsp">

    <%
    // String status1 = request.getParameter("status1");
 // String com=" 1 = 1";

     // get connection
     Connection con = lm.Database.getConnection();
     PreparedStatement ps=null;
     ResultSet rs=null;
     String mid=request.getParameter("mid");
     //Statement st = con.createStatement();
     //ResultSet rs=ps.executeQuery("select * from issues where mid = ?");
    
      String query = "select * from issues where mid = ?";
            ps = con.prepareStatement(query);
            ps.setString(1, mid);
            rs = ps.executeQuery();
     
    // ResultSet rs = st.executeQuery("select * from issues where mid = " + mid);
    
    // System.out.println("query=="+query);
     //PreparedStatement ps=con.prepareStatement("select * from issues where = ?");
     if (! rs.next())
     {
         //out.println("<h4>Sorry! Record Not Found!</h4>");
         out.println("<p/> <a href='javascript:history.back()'>Back</a>");
         return;
     }
    %>

<table class="table table-striped">

<th>ISBN NO</th>
<th>Borrow Date</th>
<th>Renewal Date</th>
<th>Student ID</th>
<th>Student Name</th>
<th>Fine</th>
  	<tr>
  	
<td><%=rs.getString("isbn") %></td>
  <td><%=rs.getString("bdate") %></td>
<td><%=rs.getString("rdate") %></td>
  <td><%=rs.getString("mid") %></td>
  <td><input type="submit" value="Return" class="btn btn-primary" /><td>
</tr>
<%while(rs.next())
{
	
	%>
	 	<tr>
  <td><%=rs.getString("isbn") %></td>
  <td><%=rs.getString("bdate") %></td>
<td><%=rs.getString("rdate") %></td>
  <td><%=rs.getString("mid") %></td>
  <td><input type="submit" value="Return" class="btn btn-primary" /><td>
</tr>
<%
}
rs.close();
rs.close();
con.close();
%>
	
</table>
</form>

i need to save the which book are return and update the status like this...

public int returnBook()
	    {
	        Connection con=null;
	        PreparedStatement ps=null;
	        try
	        {
	            con = Database.getConnection();
	            ps  = con.prepareStatement("select ifnull(max(bid),0) + 1 from books");
	            ResultSet rs = ps.executeQuery();
	            rs.next();
	            int bid = rs.getInt(1);
	            rs.close();
	            
	            ps = con.prepareStatement("insert into returns values (?,?,?,?,?,?)");
	             ps.setString(1,isbn);
	             ps.setString(2,bdate);
	             ps.setString(3,rdate);
	             ps.setInt(4,mid);
	             ps.setString(5,name);
	             ps.setInt(6, fine);
		             System.out.println("Query==="+ps);
		             ps.executeUpdate();
		             // remove row from issues
		             ps = con.prepareStatement("delete from issues where isbn = ?");
		             ps.setString(1,isbn);
		             System.out.println("Query==="+ps);
		             ps.executeUpdate();
		             
		             // update books                      
                     
		             ps = con.prepareStatement("update books set status='a' where isbn = ?");
		             ps.setString(1,isbn);
		             
		             System.out.println("Query==="+ps);
	            if ( ps.executeUpdate() == 1)
	                return bid;
	            else
	                return -1;
	        }
	        catch(Exception ex)
	        {
	            System.out.println(ex.getMessage());
	            return -1;
	        }
	        finally
	        {
	            Database.clean(con,ps);
	        }
	    }

But i get error like this..

Query===com.mysql.jdbc.ServerPreparedStatement[2] - insert into returns values (null,null,null,0,null,0)
Query===com.mysql.jdbc.ServerPreparedStatement[3] - delete from issues where isbn = null
Query===com.mysql.jdbc.ServerPreparedStatement[4] - update books set status='a' where isbn = null

please help me...and thanks

[Ulf Dittmer]

That looks like debugging output to me, not like error messages. What happens if you run this code, and how specifically is that different from what you expected. Also check the DB directly to see if anything is inserted, updated or deleted.

[sarathd kumar]

yes it insert only null value
how can i get the values from on it
thanks

[Ulf Dittmer]

*Are* the values you're using null? How have you made sure that they are not?

[J. Kevin Robbins]

That's probably not a valid insert statement. A SQL insert looks like this.

insert into returns (isbn, bdate, rdate, mid, name, fine) values (?, ?, ?, ?, ?, ?)

What you have only works if a value is specified for every column. If you skip even one column, they all default to null. Are you accounting for all columns?
Use a tool like WinSQL to test your SQL statements.

But more importantly, get rid of that awful scriptlet code. Java code does not belong in a jsp file. Please read this article to find out the proper way to structure a web application.