JSP RSS JSP RSS

Retrieval and comparison of a value stored in a MySQL database

Thread Solved

•
•
Join Date: Dec 2007
Posts: 65
Reputation: CodeBoy101 is an unknown quantity at this point 
Solved Threads: 0
CodeBoy101's Avatar
CodeBoy101 CodeBoy101 is offline Offline
Junior Poster in Training

Retrieval and comparison of a value stored in a MySQL database

 
0
  #1
Jun 21st, 2009
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) 
  1.  
  2. <%@page contentType="text/html" pageEncoding="UTF-8"%>
  3. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
  4. "http://www.w3.org/TR/html4/loose.dtd">
  5.  
  6. <%@ page import="java.sql.*" %>
  7. <%
  8.  
  9.         Connection con = null; // object.
  10.         ResultSet rst = null; // object.
  11.         Statement stmt = null; // object.
  12.  
  13.         try {
  14.             Class.forName("com.mysql.jdbc.Driver");
  15.             String url = "jdbc:mysql://localhost:3306/test2";
  16.             con = DriverManager.getConnection(url, "root", "123");
  17.             stmt = con.createStatement();
  18.             String id = request.getParameter("id");
  19.             String Course_name = request.getParameter("Course_name");
  20.  
  21.             rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = " + Course_name + " and id = " + id);
  22.  
  23. %>
  24.  
  25.  
  26.  
  27. <html>
  28.     <head>
  29.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  30.         <title>JSP Page</title>
  31.     </head>
  32.     <body>
  33.         <h1>Login Result:</h1>
  34.  
  35.  
  36.         <%
  37.  
  38.         String Instructor = rst.getString("Instructor");
  39.  
  40.             if (Instructor == null) {
  41.  
  42.         %> Sorry, no match for that user in our database. Please try again.<%          
  43.  
  44.             } else if (Instructor != null){
  45.  
  46.         %> 
  47.  
  48.              Hello %= Instructor%>, go to <a href="index.jsp">staff home</a> to begin.--> <%
  49.  
  50.  
  51.             }
  52.  
  53.         rst.close();
  54.         stmt.close();
  55.         con.close();
  56.  
  57.         } catch (ClassNotFoundException e) {
  58.             System.err.println("Could not load database Driver!");
  59.         } catch (SQLException e) {
  60.             System.err.println("Could not connect to to the database!");
  61.         }
  62.  
  63.         %>
  64.  
  65.  
  66.  
  67.  </body>
  68. </html>

Any help is appreciated thanks in advance.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 1,678
Reputation: javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all 
Solved Threads: 226
Featured Poster
javaAddict's Avatar
javaAddict javaAddict is offline Offline
Posting Virtuoso

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

 
2
  #2
Jun 21st, 2009
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) 
  1. if (Instructor == null) 
  2.     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) 
  1. catch (ClassNotFoundException e) {
  2.             System.err.println("Could not load database Driver!");
  3. %> <%= e.getMessage()%> <%
  4.         } catch (SQLException e) {
  5.             System.err.println("Could not connect to to the database!");
  6. %> <%= e.getMessage()%> <%
  7.         }

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
Check out my New Bike at my Public Profile at the "About Me" tab
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 65
Reputation: CodeBoy101 is an unknown quantity at this point 
Solved Threads: 0
CodeBoy101's Avatar
CodeBoy101 CodeBoy101 is offline Offline
Junior Poster in Training

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

 
0
  #3
Jun 21st, 2009
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) 
  1. <%@ page import="java.sql.*" %>
  2. <%
  3.  
  4.         Connection con = null; // object.
  5.         ResultSet rst = null; // object.
  6.         Statement stmt = null; // object.
  7.  
  8.         try {
  9.             Class.forName("com.mysql.jdbc.Driver");
  10.             String url = "jdbc:mysql://localhost:3306/test2";
  11.             con = DriverManager.getConnection(url, "root", "123");
  12.             stmt = con.createStatement();
  13.             String id = request.getParameter("id");
  14.             String Course_name = request.getParameter("Course_name");
  15.  
  16.             rst = stmt.executeQuery("select Instructor, id, Course_name from untitled where Course_name = '" + Course_name + "' and id = " + id);
  17.  
  18. %>
  19.  
  20.  
  21. <html>
  22.     <head>
  23.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  24.         <title>JSP Page</title>
  25.     </head>
  26.     <body>
  27.         <h1>Login Result:</h1>
  28.  
  29.         <%
  30.             if (rst.next() == false) {
  31.  
  32.         %> Sorry, no match for that user in our database. Please try again.<% } 
  33.  
  34.  
  35.             else {
  36.  
  37.             String Instructor = rst.getString("Instructor");    
  38.  
  39.          %>
  40.  
  41.         Hello <%= Instructor%>, go to <a href="index.jsp">staff home</a> to begin. <%
  42.             }//end else
  43.  
  44.                     rst.close();
  45.                     stmt.close();
  46.                     con.close();
  47.  
  48.        }//end if try
  49.  
  50. catch (ClassNotFoundException e) {
  51.                     System.err.println("Could not load database Driver!");
  52.         %> <%= e.getMessage()%> <%
  53.  
  54.                 }
  55.  
  56. catch (SQLException e) {
  57.                     System.err.println("Could not connect to to the database!");
  58.         %> <%= e.getMessage()%> <%
  59.  
  60.                 }
  61.  
  62.         %>
  63.  
  64.  
  65.     </body>
  66. </html>
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 1,678
Reputation: javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all javaAddict is a name known to all 
Solved Threads: 226
Featured Poster
javaAddict's Avatar
javaAddict javaAddict is offline Offline
Posting Virtuoso

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

 
0
  #4
Jun 22nd, 2009
•
•
•
•
Originally Posted by javaAddict View Post
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
Check out my New Bike at my Public Profile at the "About Me" tab
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 65
Reputation: CodeBoy101 is an unknown quantity at this point 
Solved Threads: 0
CodeBoy101's Avatar
CodeBoy101 CodeBoy101 is offline Offline
Junior Poster in Training

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

 
0
  #5
Jun 22nd, 2009
Will do!
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the JSP Forum
Thread Tools Search this Thread



apache array backbutton combobox comma connection csv database development directorystructure dropdownlist dynamicpagetitles eclipse frames glassfish ie8 imagetodatabse imageupload integer internet java javaee javascript jsf jsp jsppagetitles levels mvc2 mvcmodel2 mysql netbeans network parameters passing ping printinserverinsteadofclient read redirect request.getparameter response seperated servlet servletdopost()readxml sessions software sql ssl state_saving_method stocks sun tomcat tutorial update values video web write
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC