Hello,
Using the connect() method in the example below I am able to query the mysql database with no problem. However, when I call the viewUsers() method in a jsp I receive the following message "java.sql.SQLException: Column 'somename' not found.". In fact it would only work when I change the column name to dept. All other column name result in the error I mentioned earlier. Please Help!
######### JAVA BEAN ##############################
// timeclock.java
package tclock;
import java.sql.*;
import java.util.*;
public class timeclock {
String error;
Connection con;
public timeclock() { }
public void connect() throws ClassNotFoundException,
SQLException,
Exception {
try {
//Class.forName("com.mysql.jdbc.Driver").newInstance ();
Class.forName("org.gjt.mm.mysql.Driver").newInstan ce();
System.out.println("JDBC driver loaded");
con = DriverManager.getConnection
("jdbc:mysql://localhost/time_card?user=dalma&password=");
System.out.println("Database connection established");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM time_sheet");
//ResultSet rs= stmt.executeQuery("Select fname, lname FROM time_sheet");
while (rs.next()) {
String title = rs.getString("fname");
String title1 = rs.getString("lname");
String title2 = rs.getString("dept");
//String title3 = rs.getInteger("id");
//float price = rs.getint("Price");
System.out.println(title1);
}
} catch (ClassNotFoundException cnfe) {
System.out.println("ClassNotFoundException: Could not locate driver");
} catch (SQLException cnfe) {
System.out.println("SQLException: Could not connect to database");
} catch (Exception e) {
System.out.println
("An unknown error occurred while connecting to database");
}
}
public void disconnect() throws SQLException {
try {
if ( con !=null ) {
con.close();
}
} catch(SQLException sqle) {
System.out.println("Unable to close database connection.");
}
}
public ResultSet viewUsers() throws SQLException, Exception {
ResultSet rs= null;
try {
//String queryString=("SELECT * FROM time_sheet");
Statement stmt = con.createStatement();
//rs = stmt.executeQuery(queryString);
rs = stmt.executeQuery("SELECT * FROM time_sheet");
} catch (SQLException sqle) {
error = "SQLException: Could not execute query.";
throw new SQLException(error);
} catch (Exception e) {
error = "An exception has occured while retrieving departments,";
throw new Exception(error);
}
return rs;
}
public static void main (String[] args)
{
timeclock sdb = new timeclock();
try
{
sdb.connect();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
################# JSP #######################################
<%@page language="java" contentType="text/html" import="java.sql.*, java.util.*" errorPage="error.jsp" %>
<jsp:useBean id="mytime" class="tclock.timeclock"/>
<html>
<head><title>JSP Page</title></head>
<body>
<H1>Virgin Islands Water & Power Authority</H1>
<table border="1">
<tr>
<td><b><B>Clock Number:</b></td>
<td><b><B>Date:</b></td>
<td><b><B>Time:</b></td>
<td><b><B>User ID:</b></td>
<td><b><B>First Name:</b></td>
<td><b><B>Last Name:</b></td>
<td><b><B>Departments:</b></td>
<td><b><B>Status:</b></td>
<td><b><B>Record Number:</b></td>
</tr>
<%
mytime.connect();
ResultSet rs = mytime.viewUsers();
while (rs.next()) {
%>
<tr>
<td><%= rs.getString("fname") %></td>
</tr>
<%
}
%>
</table>
</body>
</html>
java.sql.SQLException: Column 'somenam' not found.
