aspose 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: 8971
    
    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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Multiple query problems