File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Multiple query problems Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple query problems" Watch "Multiple query problems" New topic
Author

Multiple query problems

matthew weeks
Greenhorn

Joined: Feb 23, 2004
Posts: 15
Im fairly new to java jdbc, and im sure this is a basic problem. Im having trouble trying to get 4 different queries (basic select from where) to display their results in 4 different combo boxes. I can get each of jcombobox to display its relevant query one at a time, but not all 4 together. When i try to execute the form, I get the following SQL exception error: Invalid Handle, on the following line of code:-

resultSetLessonDay = statementLessonDay.executeQuery(queryLessonDay);
I'm not sure if this is right, but ive created a connection, statement, resultSet and Vector for each query.
Anyone willing to help out a poor student. The code for this class is below:-
/*
* frmLessons.java
*
* Created on 27 February 2004
*
*/
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
import java.util.Vector;
public class frmLessons extends javax.swing.JFrame {

Connection connectionQueryRooms, connectionCourseCode, connectionStaffMember, connectionLessonDay;
String url;
Vector vtrRoomNumber = new Vector();
Vector vtrCourseCode = new Vector();
Vector vtrStaffMember = new Vector();
Vector vtrLessonDay = new Vector();

/** Creates new form frmLessons */
public frmLessons() {
initComponents();
}

//Initialize the form
public void initComponents() {

lblTitle = new javax.swing.JLabel();
jScrollPane1 = new javax.swing.JScrollPane();
txtaOutput = new javax.swing.JTextArea();
jSeparator1 = new javax.swing.JSeparator();
butMainMenu = new javax.swing.JButton();
lblInstructions = new javax.swing.JLabel();

cmbRoomNumber = new javax.swing.JComboBox(vtrRoomNumber);
cmbCourseCode = new javax.swing.JComboBox(vtrCourseCode);
cmbStaffMember = new javax.swing.JComboBox(vtrStaffMember);
cmbLessonDay = new javax.swing.JComboBox(vtrLessonDay);
getContentPane().setLayout(null);
addWindowListener(new java.awt.event.WindowAdapter() {
public void windowClosing(java.awt.event.WindowEvent evt) {
exitForm(evt);
}
});

//CourseCode combo box properties
cmbCourseCode.setAutoscrolls(true);
cmbCourseCode.setMaximumRowCount(6);
cmbCourseCode.setToolTipText("Select a Course Code to make the Lesson for");
cmbCourseCode.setSize(60,30);
cmbCourseCode.setLocation(30,120);
getContentPane().add(cmbCourseCode);

//RoomNumber combo box properties
cmbRoomNumber.setAutoscrolls(true);
cmbRoomNumber.setMaximumRowCount(6);
cmbRoomNumber.setToolTipText("Select Room Number for a Lesson to be held in");
cmbRoomNumber.setSize(60,30);
cmbRoomNumber.setLocation(130,120);
getContentPane().add(cmbRoomNumber);

//StaffMember combo box properties
cmbStaffMember.setAutoscrolls(true);
cmbStaffMember.setMaximumRowCount(6);
cmbStaffMember.setToolTipText("Select a Staff Member for the lesson");
cmbStaffMember.setSize(60,30);
cmbStaffMember.setLocation(190,120);
getContentPane().add(cmbStaffMember);

//LessonDay combo box properties
cmbLessonDay.setAutoscrolls(true);
cmbLessonDay.setMaximumRowCount(6);
cmbLessonDay.setToolTipText("Select a Day for a Lesson");
cmbLessonDay.setSize(60,30);
cmbLessonDay.setLocation(250,120);
getContentPane().add(cmbLessonDay);

//Title label
lblTitle.setFont(new java.awt.Font("Dialog", 1, 18));
lblTitle.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
lblTitle.setText("Lessons");
getContentPane().add(lblTitle);
lblTitle.setBounds(0, 20, 564, 24);
jScrollPane1.setVerticalScrollBarPolicy(javax.swing.JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
txtaOutput.setLineWrap(true);
txtaOutput.setWrapStyleWord(true);
jScrollPane1.setViewportView(txtaOutput);
getContentPane().add(jScrollPane1);
jScrollPane1.setBounds(10, 450, 220, 60);
getContentPane().add(jSeparator1);
jSeparator1.setBounds(0, 430, 560, 10);
butMainMenu.setText("Main Menu");
butMainMenu.setToolTipText("Go back to main menu, will lose any unsaved changes");
getContentPane().add(butMainMenu);
butMainMenu.setBounds(450, 460, 100, 26);
lblInstructions.setFont(new java.awt.Font("Dialog", 0, 12));
lblInstructions.setText("Use the following components, in order to create a valid Lesson booking for a course:-");
getContentPane().add(lblInstructions);
lblInstructions.setBounds(10, 50, 480, 16);

try
{
url = "jdbc dbc atabase";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connectionQueryRooms = DriverManager.getConnection(url);
connectionCourseCode = DriverManager.getConnection(url);
connectionStaffMember = DriverManager.getConnection(url);
connectionLessonDay = DriverManager.getConnection(url);


txtaOutput.append( "Connection successful MATT\n"); //println connection successful (\n could be new line
}
catch(ClassNotFoundException cnfex ) //Catches/details all class not found exceptions
//process ClassNotFoundExceptions/database here
{
cnfex.printStackTrace();
txtaOutput.append( "Connection successfulM\n" + cnfex.toString());
}
catch (SQLException sqlex) //Catches/details all SQLs not found exceptions
//process SQL exceptions here
{
sqlex.printStackTrace();
txtaOutput.append("Connection successful\n" + sqlex.toString());

}
catch (Exception ex)
{
//process remaining exceptions here
ex.printStackTrace();
txtaOutput.append( ex.toString());
}



cmbQueryRooms();
cmbQueryCourseCode();
cmbQueryStaffMember();
cmbQueryLessonDay();

pack();
java.awt.Dimension screenSize = java.awt.Toolkit.getDefaultToolkit().getScreenSize();
setSize(new java.awt.Dimension(564, 556));
setLocation((screenSize.width-564)/2,(screenSize.height-556)/2);
}
public void cmbQueryRooms()
{
try
{
String queryRoomNumber;

String url = "jdbc dbc atabase";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connectionQueryRooms = DriverManager.getConnection(url);

txtaOutput.append("Connection to for queries sucessful\n");

Statement statementQueryRooms = connectionQueryRooms.createStatement(); // public Statement statement, statement1;
queryRoomNumber = "SELECT * FROM Rooms ORDER BY Rooms.RoomNumber ";
txtaOutput.append("\nSending query Room Numbers for comboBoxes: " + connectionQueryRooms.nativeSQL(queryRoomNumber) + "\n");

statementQueryRooms.close();


ResultSet resultSetRoomNumber;
resultSetRoomNumber = statementQueryRooms.executeQuery(queryRoomNumber);

txtaOutput.append("\nQuery sucessful\n");

while(resultSetRoomNumber.next())
{
//States the same of the column value for the vector
vtrRoomNumber.add(resultSetRoomNumber.getString("RoomName"));
}
}
catch (ClassNotFoundException cnfex )
{
cnfex.printStackTrace();
txtaOutput.append(" Connection unsuccessful\n" + cnfex.toString());
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
txtaOutput.append(" Connection unsucessful\n" + sqlex.toString());
}
catch (Exception ex)
{
ex.printStackTrace();
txtaOutput.append(ex.toString());
}
}
public void cmbQueryCourseCode()
{
try
{
String queryCourseCode;

//statement = connection.createStatement();
url = "jdbc dbc atabase";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connectionCourseCode = DriverManager.getConnection(url);

txtaOutput.append("Connection to for queries sucessful\n");

Statement statementCourseCode = connectionCourseCode.createStatement();
queryCourseCode = "SELECT * FROM Courses ORDER BY Courses.CourseCode ";

txtaOutput.append("\nSending queries for comboBox Course Code" + connectionCourseCode.nativeSQL(queryCourseCode)+ "\n");

statementCourseCode.close();
ResultSet resultSetCourseCode;
resultSetCourseCode = statementCourseCode.executeQuery(queryCourseCode);

txtaOutput.append("\nQuery sucessful\n");

while(resultSetCourseCode.next()) //&& resultSetLessonDay.next()))
{
//States the same of the column value for the vector
vtrCourseCode.add(resultSetCourseCode.getString("CourseCode"));
}}
catch (ClassNotFoundException cnfex )
{
cnfex.printStackTrace();
txtaOutput.append(" Connection unsuccessful\n" + cnfex.toString());
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
txtaOutput.append(" Connection unsucessful\n" + sqlex.toString());
}
catch (Exception ex)
{
ex.printStackTrace();
txtaOutput.append(ex.toString());
}
}
public void cmbQueryStaffMember()
{
try
{
String queryStaffMember;

//statement = connection.createStatement();
String url = "jdbc dbc atabase";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connectionStaffMember = DriverManager.getConnection(url);

txtaOutput.append("Connection to for queries sucessful\n");

Statement statementStaffMember = connectionStaffMember.createStatement(); // public Statement statement, statement1;
queryStaffMember = "SELECT * FROM Staff ORDER BY Staff.StaffSName ";

txtaOutput.append("\nSending queries for comboBox Staff Members" + connectionStaffMember.nativeSQL(queryStaffMember)+ "\n");

statementStaffMember.close();

ResultSet resultSetStaffMember;
resultSetStaffMember = statementStaffMember.executeQuery(queryStaffMember);

txtaOutput.append("\nQuery sucessful\n");

while(resultSetStaffMember.next()) //&& resultSetLessonDay.next()))
{
//States the same of the column value for the vector
vtrStaffMember.add(resultSetStaffMember.getString("StaffSName"));
}
}
catch (ClassNotFoundException cnfex )
{
cnfex.printStackTrace();
txtaOutput.append(" Connection unsuccessful\n" + cnfex.toString());
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
txtaOutput.append(" Connection unsucessful\n" + sqlex.toString());
}
catch (Exception ex)
{
ex.printStackTrace();
txtaOutput.append(ex.toString());
}
}
public void cmbQueryLessonDay()
{
try
{

String queryLessonDay;

//statement = connection.createStatement();
String url = "jdbc dbc atabase";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connectionLessonDay = DriverManager.getConnection(url);

txtaOutput.append("Connection to for queries sucessful\n");

Statement statementLessonDay = connectionLessonDay.createStatement();
queryLessonDay = "SELECT * FROM DayOfWeek ";

txtaOutput.append("\nSending queries for comboBox Days " + connectionLessonDay.nativeSQL(queryLessonDay )+ "\n");
statementLessonDay.close();

ResultSet resultSetLessonDay;
resultSetLessonDay = statementLessonDay.executeQuery(queryLessonDay);
txtaOutput.append("\nQuery sucessful\n");

while(resultSetLessonDay.next()) //&& resultSetLessonDay.next()))
{
//States the same of the column value for the vector
vtrLessonDay.add(resultSetLessonDay.getString("DayName"));
}
}
catch (ClassNotFoundException cnfex )
{
cnfex.printStackTrace();
txtaOutput.append(" Connection unsuccessful\n" + cnfex.toString());
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
txtaOutput.append(" Connection unsucessful\n" + sqlex.toString());
}
catch (Exception ex)
{
ex.printStackTrace();
txtaOutput.append(ex.toString());
}

}

/** Exit the Application */
private void exitForm(java.awt.event.WindowEvent evt) {
System.exit(0);
}

/**
* @param args the command line arguments
*/
public static void main(String args[])
{
new frmLessons().show();
}

// Variables declaration
public javax.swing.JLabel lblInstructions;
public javax.swing.JSeparator jSeparator1;
public javax.swing.JScrollPane jScrollPane1;
public javax.swing.JButton butMainMenu;
public javax.swing.JTextArea txtaOutput;
public javax.swing.JLabel lblTitle;
public javax.swing.JComboBox cmbRoomNumber;
public javax.swing.JComboBox cmbCourseCode;
public javax.swing.JComboBox cmbStaffMember;
public javax.swing.JComboBox cmbLessonDay;

// End of variables declaration

}
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8997
    
    9

Closing the Statement object before using it to execute a query is a Bad Idea. It is a Good Idea to close Connection, Statement and ResultSet instances when you are finished with them in "finally" blocks (as in catch-try-finally), so they get closed even if an exception occurs.


[How To Ask Questions On JavaRanch]
Luke Chadwick
Greenhorn

Joined: Mar 05, 2004
Posts: 8
It could be that you close the statement, before you've got all the results in the query. Try and place the close statement after the while bit, that may solve it.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple query problems