Display error when record not found

Hi

I seem to have a problem at which i have been working on for about five hours and cant seem to get to a solution.

This is what i am doing, i have a search field which queries a database and displays the results of its search in a table below it. The problem is that when i search for a non existent record i want to display an error message that says a record was not found.

I have tried the following:
1. Surrounded the code with a try catch which caught SQLExceptions, though when a non existent record was searched no error was caught.

2.I tried to somehow count the returned record count from the resultset and if the count was zero display the error, but had no luck there either

Here is the code:

<%@page contentType="text/html;charset=iso-8859-1" language="java" import="java.sql.*" 
errorPage="errorPage.jsp"%>
<%@page pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<jsp:useBean id="objClassName" scope="page" class="packageName.ClassName" />
<%
try{
    ResultSet rs = null;
    objClassName = new packageName.ClassName("Access");
    Connection conn = objClassName.getConn();
    if(request.getParameter("search") != null){
        String custId = request.getParameter("custId");
        String sql = "SELECT * FROM CustomerDetails WHERE CustomerId = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, custId);
        rs = pstmt.executeQuery();
    }else {
        String sql = "SELECT * FROM CustomerDetails";
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
    }
%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Trans Atlantic Airline - Home</title>
    </head>
    <body>
        <form action="customerDetailsView.jsp" method="get">
            <%@include file="header.html"%>
            <table border="1px">
                <tr height="400px" >
                    <td width="185px" align="left" valign="top" bgcolor="#0000FF">
                        <table width="100px" cellpadding="5px" border="1px">
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="index.jsp">Home</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="customerDetailsView.jsp">Customer Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="flightDetailsView.jsp">Flight Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="bookingsView.html">Booking Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="departureInformationView.html">Departure Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="acceptedCreditCardsView.html">Credit Card Details</a></td>
                            </tr>
                        </table>
                        <p> </p>
                    </td>
                    <td width="90%" align="left" valign="top">
                        <table border="1" width="780px">
                            <tr>
                                <td colspan="4">
                                    
                                    Customer Id:
                                    <input type="text" id="custId" name="custId" size="10" /><br />
                                    <input type="submit" id="search" name="search" value="Search" />
                                    
                                </td>
                            </tr>
                            <tr>
                                <th width="30px"> </th>
                                <th width="30px"> </th>
                                <th width="90px">Customer Id</th>
                                <th width="150px">Customer Name</th>
                                <th width="150px">Customer Contact No</th>
                                <th width="120px">Credit Card Id</th>
                                <th width="180px">Credit Card No</th>
                            </tr>
                        </table>
                        <div style="overflow:auto;height:403px">
                            <table border="1" width="780px">
                                <%
                                while(rs.next()){
        String custId = rs.getString("CustomerId");
                                %>
                                <tr>
                                    <td width="31px">
                                        <a href="customerDetailsForm.jsp?custId=<%=custId%>&act=del">
                                            <img src="images/rbrs.gif" alt="Delete Customer">
                                        </a>
                                    </td>
                                    <td width="29px">
                                        <a href="customerDetailsForm.jsp?custId=<%=custId%>&act=upd">
                                            <img src="images/rbs.gif" alt="Update Customer">
                                        </a>
                                    </td>
                                    <td width="93px"><%=custId%></td>
                                    <td width="149px"><%=rs.getString("CustomerName")%></td>
                                    <td width="150px"><%=rs.getString("CustomerContactNo")%></td>
                                    <td width="124px"><%=rs.getString("CreditCardId")%></td>
                                    <td width="177px"><%=rs.getString("CreditCardNo")%></td>
                                </tr>
                                <%
                                }
}catch(SQLException ex){
    out.print("<h1>No record found<h1>");
}
                                %>
                            </table>
                        </div>
                    </td>
                </tr>
                <tr>
                    <td> </td>
                    <td>
                        <a href="customerDetailsForm.jsp">Add New</a>
                    </td>
                </tr>
            </table>
        </form>
    </body>
</html>

I thank everyone in advance.

Thank you
Talhah Mafawalla

I tried to somehow count the returned record count from the resultset and if the count was zero display the error, but had no luck there either

How did you count the number of records in the result set?

Hi Nitesh Kant,

That was the problem i couldnt find a way to count the number of records in the record set. Though that would have made it easy.

I have solved the problem by using the following code which forces the compiler to throw an error when there is no records in the record set:

Which forces the compiler to throw an error when there is no records in the record set.

What is the exception that is thrown?
Okie, as far as i think, you should increment a counter inside the while(rs.next()){} loop. If after the loop execution, the counter value is zero, that means that there are no records in the resultset and you can print the same on the screen.
Do let me know as to which exception is thrown by the code when the result set does not have any rows.

Hi Nitesh,

The error that i got using ex.getMessage() was:

[Microsoft][ODBC Driver Manager] Invalid cursor state

hi Talhah,

in your jsp it is not going to print "no records found"
because no exception is thrown.
when the resultset does not contain any rows it never goes
into the while loop
so before you go into the loop check whether there is a row
in the resultset and if not there display the message.

- Avoid using scriptlets/Java code in JSPs
- Avoid using DB stuff in JSPs

Anyways, you can use a VO, often known as TO, instead of directly using resultset for rendering.

Otherwise take a boolean variable say, hasValues=false outside your while(rs.next()) and make it true under your loop. Then you can check for that variable. But its just a work around. There are many others as well.

Remove your workarounds, now. Do the trick with your original code.
[ April 09, 2007: Message edited by: Adeel Ansari ]

Originally posted by nelson christos:
so before you go into the loop check whether there is a row
in the resultset and if not there display the message.

Thats the exact thing he was unable to do.

Thanks Adeel and Nelson,

I think i have got a solution by using a variable in the while loop to count the number of times it get executed, thus if the number is zero i displayed a "Record not found" message. Otherwise the table gets built.

Though Adeel would the reason you recommend not using scriptlets/Java code in JSPs and DB stuff in JSPs because of security reasons?

Should the database stuff go into a java class to get executed a then only return a result(e.g. I have a class that handles the database connection either for MySQL or Access by passing to it a string and it returns a Connection). Would this be what you are talking about?

Also what would "VO" and "TO" stand for. I have just started learning jsp and i am looking for good guidlines to follow (coding conventions etc.)

The code that i have produced now is as follows:

<%@page contentType="text/html;charset=iso-8859-1" language="java" import="java.sql.*" 
errorPage="errorPage.jsp"%>
<%@page pageEncoding="UTF-8"%>
 
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<jsp:useBean id="objDatabseConnection" scope="page" class="transatlanticairline.DatabaseConnection" />
<%
    int results = 0;
    ResultSet rs = null;
    objDatabseConnection = new transatlanticairline.DatabaseConnection("Access");
    Connection conn = objDatabseConnection.getConn();
    if(request.getParameter("search") != null){
        String custId = request.getParameter("custId");
        String sql = "SELECT * FROM CustomerDetails WHERE CustomerId = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, custId);
        rs = pstmt.executeQuery();
    }else {
        String sql = "SELECT * FROM CustomerDetails";
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
    }
%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Trans Atlantic Airline - Home</title>
    </head>
    <body>
        <form action="customerDetailsView.jsp" method="get">
            <%@include file="header.html"%>
            <table border="1px">
                <tr height="347px" >
                    <td width="185px" align="left" valign="top" bgcolor="#0000FF">
                        <table width="100px" cellpadding="5px" border="1px">
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="index.jsp">Home</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="customerDetailsView.jsp">Customer Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="flightDetailsView.jsp">Flight Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="bookingsView.html">Booking Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="departureInformationView.html">Departure Details</a></td>
                            </tr>
                            <tr>
                                <td nowrap><img src="images/bullet.gif" width="15px" height="15px"></td>
                                <td nowrap><a style="color:#FFFFFF" href="acceptedCreditCardsView.html">Credit Card Details</a></td>
                            </tr>
                        </table>
                        <p> </p>
                    </td>
                    <td width="90%" align="left" valign="top">
                        <table border="1" width="780px">
                            <tr>
                                <td colspan="4">
                                    Customer Id:
                                    <input type="text" id="custId" name="custId" size="10" /><br />
                                    <input type="submit" id="search" name="search" value="Search" />
                                </td>
                            </tr>
                            <tr>
                                <th width="30px"> </th>
                                <th width="30px"> </th>
                                <th width="90px">Customer Id</th>
                                <th width="150px">Customer Name</th>
                                <th width="150px">Customer Contact No</th>
                                <th width="120px">Credit Card Id</th>
                                <th width="180px">Credit Card No</th>
                            </tr>
                        </table>
                        <div style="overflow:auto;height:347px">
                            <table border="1" width="780px">
                                <%
                                while(rs.next()){
                                results++;
        String custId = rs.getString("CustomerId");
                                %>
                                
                                <tr>
                                    <td width="31px">
                                        <a href="customerDetailsForm.jsp?custId=<%=custId%>&act=del">
                                            <img src="images/rbrs.gif" alt="Delete Customer">
                                        </a>
                                    </td>
                                    <td width="29px">
                                        <a href="customerDetailsForm.jsp?custId=<%=custId%>&act=upd">
                                            <img src="images/rbs.gif" alt="Update Customer">
                                        </a>
                                    </td>
                                    <td width="93px"><%=custId%></td>
                                    <td width="149px"><%=rs.getString("CustomerName")%></td>
                                    <td width="150px"><%=rs.getString("CustomerContactNo")%></td>
                                    <td width="124px"><%=rs.getString("CreditCardId")%></td>
                                    <td width="177px"><%=rs.getString("CreditCardNo")%></td>
                                </tr>
                                <%
                                }
                                %>
                            </table>
                            <%
                            if(results == 0) {
                            out.print("<h2>The record was not found.<h2>");
                            }
                            %>
                        </div>
                    </td>
                </tr>
                <tr>
                    <td> </td>
                    <td>
                        <a href="customerDetailsForm.jsp">Add New</a>
                    </td>
                </tr>
            </table>
        </form>
    </body>
</html>

Sorry for posting so much code but i have made chages to various parts of the jsp file.

Thanks again for everyones help
Talhah Mafawalla

[ April 09, 2007: Message edited by: Talhah Mafawalla ]
[ April 09, 2007: Message edited by: Talhah Mafawalla ]

Originally posted by Talhah Mafawalla:
Though Adeel would the reason you recommend not using scriptlets/Java code in JSPs and DB stuff in JSPs because of security reasons?

No, its not about security. Its about readability and ease of maintenance. Better say its about using the right tool in a right way. Since, JSP is a display technology we must not do other stuff in there.

Should the database stuff go into a java class to get executed a then only return a result(e.g. I have a class that handles the database connection either for MySQL or Access by passing to it a string and it returns a Connection). Would this be what you are talking about?

Yes. But, there are best practices to follow such as Connection Pooling, DAO, TO/VO, ORM Tools etc.