Ad:

JSP Discussion Thread
Marked Solved
Views: 1236

Jun 21st, 2009

Retrieval and comparison of a value stored in a MySQL database

Expand Post »

Hello everyone, I'm working on a project where I wish to find out who the instructor for a particular course is. The user is has to input the course name and course id for that course id then the script will search for the instructor. If the value returned is null (instructor not found), the user will be asked to refine his search. But if all goes well the user will be supplied with the information he needs.

But for some reason when I try the code I came up with, all I get is a blank screen.

This is what came up with so far:

JSP Syntax (Toggle Plain Text)
 
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
 
<%@ page import="java.sql.*" %>
<%
 
        Connection con = null; // object.
        ResultSet rst = null; // object.
        Statement stmt = null; // object.
 
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test2";
            con = DriverManager.getConnection(url, "root", "123");
            stmt = con.createStatement();
            String id = request.getParameter("id");
            String Course_name = request.getParameter("Course_name");
 
            rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = " + Course_name + " and id = " + id);
 
%>
 
 
 
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Login Result:</h1>
 
 
        <%
 
        String Instructor = rst.getString("Instructor");
 
            if (Instructor == null) {
 
        %> Sorry, no match for that user in our database. Please try again.<%          
 
            } else if (Instructor != null){
 
        %> 
 
             Hello %= Instructor%>, go to <a href="index.jsp">staff home</a> to begin.--> <%
 
 
            }
 
        rst.close();
        stmt.close();
        con.close();
 
        } catch (ClassNotFoundException e) {
            System.err.println("Could not load database Driver!");
        } catch (SQLException e) {
            System.err.println("Could not connect to to the database!");
        }
 
        %>
 
 
 
 </body>
</html>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<%@ page import="java.sql.*" %>
<%

        Connection con = null; // object.
        ResultSet rst = null; // object.
        Statement stmt = null; // object.

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test2";
            con = DriverManager.getConnection(url, "root", "123");
            stmt = con.createStatement();
            String id = request.getParameter("id");
            String Course_name = request.getParameter("Course_name");
            
            rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = " + Course_name + " and id = " + id);
            
%>



<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Login Result:</h1>


        <%
        
        String Instructor = rst.getString("Instructor");

            if (Instructor == null) {

        %> Sorry, no match for that user in our database. Please try again.<%          
            
            } else if (Instructor != null){

        %> 
        
             Hello %= Instructor%>, go to <a href="index.jsp">staff home</a> to begin.--> <%


            }

        rst.close();
        stmt.close();
        con.close();

        } catch (ClassNotFoundException e) {
            System.err.println("Could not load database Driver!");
        } catch (SQLException e) {
            System.err.println("Could not connect to to the database!");
        }

        %>

       

 </body>
</html>

Any help is appreciated thanks in advance.

Similar Threads

CodeBoy101

Reputation Points: 8

Solved Threads: 0

Junior Poster in Training

Offline

71 posts
since Dec 2007

Permalink

Jun 21st, 2009

Re: Retrieval and comparison of a value stored in a MySQL database

I don't know if this the case, but you MUST first call:
rs.next() to get the row from the ResultSet.
Meaning that when you do:
String Instructor = rst.getString("Instructor"); you probably get an exception, go inside catch, and nothing is displayed:
Also about this:

JSP Syntax (Toggle Plain Text)
if (Instructor == null) 
    Sorry, no match for that user in our database. Please try again.
if (Instructor == null) 
    Sorry, no match for that user in our database. Please try again.

If there is no match the rs.next() will return false. So you need an if statement checking that. If it is false, THEN there is no match. If there is no match then the rs.next() will be false not the Instructor null. If you manage to call rs.next() and the rst.getString("Instructor") then there was a row returned so there are values to retrieve.
When Instructor is null doesn't mean that there were no rows. It means that you got a row from the database and the table column with name "Instructor" doesn't have a value. Of course if "Instructor" is Primary Key that would be impossible but I am talking in general.

So I suggest, first put some message inside the catch that are printed at the page:

JSP Syntax (Toggle Plain Text)
catch (ClassNotFoundException e) {
            System.err.println("Could not load database Driver!");
%> <%= e.getMessage()%> <%
        } catch (SQLException e) {
            System.err.println("Could not connect to to the database!");
%> <%= e.getMessage()%> <%
        }
catch (ClassNotFoundException e) {
            System.err.println("Could not load database Driver!");
%> <%= e.getMessage()%> <%
        } catch (SQLException e) {
            System.err.println("Could not connect to to the database!");
%> <%= e.getMessage()%> <%
        }

And then and MOST IMPORTANT do whatever you want to do from the beginning but this time follow the tutorial at the top of the JSP forum with title JSP database connectivity according to Model View Controller (MVC) Model 2

javaAddict

Sponsor

Featured Poster

Reputation Points: 1014

Solved Threads: 446

Nearly a Senior Poster

Offline

3,258 posts
since Dec 2007

Permalink

Jun 21st, 2009

Re: Retrieval and comparison of a value stored in a MySQL database

THANK YOU!!!! THANK YOU!!!! THANK YOU!!!! THANK YOU!!!!

Putting the %> <%= e.getMessage()%> <% helped me to pin-point and solve my problem almost instantly. You're a JSP genius!!!

This is what my code looks like now:

JSP Syntax (Toggle Plain Text)
<%@ page import="java.sql.*" %>
<%
 
        Connection con = null; // object.
        ResultSet rst = null; // object.
        Statement stmt = null; // object.
 
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test2";
            con = DriverManager.getConnection(url, "root", "123");
            stmt = con.createStatement();
            String id = request.getParameter("id");
            String Course_name = request.getParameter("Course_name");
 
            rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = '" + Course_name + "' and id = " + id);
 
%>
 
 
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Login Result:</h1>
 
        <%
            if (rst.next() == false) {
 
        %> Sorry, no match for that user in our database. Please try again.<% } 
 
 
            else {
 
            String Instructor = rst.getString("Instructor");    
 
         %>
 
        Hello <%= Instructor%>, go to <a href="index.jsp">staff home</a> to begin. <%
            }//end else
 
                    rst.close();
                    stmt.close();
                    con.close();
 
       }//end if try
 
catch (ClassNotFoundException e) {
                    System.err.println("Could not load database Driver!");
        %> <%= e.getMessage()%> <%
 
                }
 
catch (SQLException e) {
                    System.err.println("Could not connect to to the database!");
        %> <%= e.getMessage()%> <%
 
                }
 
        %>
 
 
    </body>
</html>
<%@ page import="java.sql.*" %>
<%

        Connection con = null; // object.
        ResultSet rst = null; // object.
        Statement stmt = null; // object.

        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test2";
            con = DriverManager.getConnection(url, "root", "123");
            stmt = con.createStatement();
            String id = request.getParameter("id");
            String Course_name = request.getParameter("Course_name");

            rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = '" + Course_name + "' and id = " + id);

%>


<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Login Result:</h1>
        
        <%
            if (rst.next() == false) {

        %> Sorry, no match for that user in our database. Please try again.<% } 
            
            
            else {

            String Instructor = rst.getString("Instructor");    
               
         %>

        Hello <%= Instructor%>, go to <a href="index.jsp">staff home</a> to begin. <%
            }//end else

                    rst.close();
                    stmt.close();
                    con.close();
                
       }//end if try

catch (ClassNotFoundException e) {
                    System.err.println("Could not load database Driver!");
        %> <%= e.getMessage()%> <%
        
                }

catch (SQLException e) {
                    System.err.println("Could not connect to to the database!");
        %> <%= e.getMessage()%> <%
        
                }

        %>
        
        
    </body>
</html>

CodeBoy101

Reputation Points: 8

Solved Threads: 0

Junior Poster in Training

Offline

71 posts
since Dec 2007

Permalink

Jun 22nd, 2009

Re: Retrieval and comparison of a value stored in a MySQL database

Click to Expand / Collapse Quote originally posted by javaAddict ...

And then and MOST IMPORTANT do whatever you want to do from the beginning but this time follow the tutorial at the top of the JSP forum with title JSP database connectivity according to Model View Controller (MVC) Model 2

Now you must follow the link above and start all over

javaAddict

Sponsor

Featured Poster

Reputation Points: 1014

Solved Threads: 446

Nearly a Senior Poster

Offline

3,258 posts
since Dec 2007

Permalink

Jun 22nd, 2009

Re: Retrieval and comparison of a value stored in a MySQL database

Will do!

CodeBoy101

Reputation Points: 8

Solved Threads: 0

Junior Poster in Training

Offline

71 posts
since Dec 2007

Permalink

Oct 14th, 2010

Re: Retrieval and comparison of a value stored in a MySQL database

is this search function?? are u using any search form??

atikahuk

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Oct 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in JSP Forum Timeline: How to make a field in mysql database as link to a file after doing a search

Next Thread in JSP Forum Timeline: FileName Checker

DaniWeb Message

Cancel Changes

User Name
Password