Hi, I a having a problem with some code I am trying to run. What I want to happen is to be able to select a row that is displayed in a JTable and view the results for that row in a DOS window. When I select a row I get the error message in the Subject line. I think the problem is that I am not passing the PersonID I need for the SQL statement. I will first paste in the valueChanged method and then paste in my full code if that helps. Thank you for your time.
Method:
// I am having a problem here getting the selected row to display results in the DOS window
public void valueChanged(ListSelectionEvent e)
{
try
{
String selectedPerson = "SELECT * FROM Person Where PersonID = ?";
PreparedStatement displayPerson = connection.prepareStatement(selectedPerson);
int selectedRow = table.getSelectedRow();
displayPerson.setInt(1, selectedRow);
ResultSet queryInfo = displayPerson.executeQuery();
//output the results set data to DOS window to make sure SQL is working
System.out.println(queryInfo.getString("LastName") +" " +
queryInfo.getString("FirstName") + " " +
queryInfo.getInt("PersonID"));
}
catch (SQLException sqle)
{
System.err.println(sqle);
}
}
Entire Code:
import javax.swing.*;
import javax.swing.event.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.border.*;
import javax.swing.table.*;
import java.sql.*;
public class AddressBook extends JFrame implements ActionListener, ListSelectionListener
{
// properties for panels and scroll pane
private JScrollPane tablePanel;
private JTable table;
private JPanel infoPane;
private JPanel buttonPane;
Dimension tableSize = new Dimension(400, 200);
//properties for textfield
private JTextField personID = new JTextField();
private JTextField personFirstName = new JTextField();
private JTextField personLastName = new JTextField();
private JTextField personAdd1 = new JTextField();
private JTextField personAdd2 = new JTextField();
private JTextField personCity = new JTextField();
private JTextField personState = new JTextField();
private JTextField personZip = new JTextField();
private JTextField personWorkNum = new JTextField();
private JTextField personFaxNum = new JTextField();
private JTextField personEmail = new JTextField();
//properties for database
String url = "jdbc
dbc:addressBook";
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
Connection connection;
Statement statement;
ResultSet results;
ResultsModel tableModel;
//declare listener for valueChanged method
private ListSelectionModel rowSM;
public static void main (String args[])
{
AddressBook a = new AddressBook();
}
public AddressBook()
{
super("Address Book");
setBounds(0, 0, 400, 500);
setDefaultCloseOperation(DISPOSE_ON_CLOSE);
addWindowListener(new WindowHandler());
//JScrollPane
//table for scroll Pane
tableModel = new ResultsModel();
table = new JTable(tableModel);
table.setAutoCreateColumnsFromModel(true);
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
table.setPreferredScrollableViewportSize(tableSize);
table.setShowVerticalLines(true);
table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
tablePanel = new JScrollPane(table);
tablePanel.setBorder(BorderFactory.createLineBorder(Color.darkGray));
getContentPane().add(tablePanel, BorderLayout.NORTH);
rowSM = table.getSelectionModel();
//Center Pane
//Create labels for input fields
JLabel eMailLabel = new JLabel( "Email Address: " );
JLabel idLabel = new JLabel( "ID: ", JLabel.RIGHT);
JLabel firstNameLabel = new JLabel( "Firs tName: ", JLabel.RIGHT);
JLabel lastNameLabel = new JLabel( "Last Name: ", JLabel.RIGHT);
JLabel add1Label = new JLabel( "Address 1: ", JLabel.RIGHT);
JLabel add2Label = new JLabel( "Address 2: ", JLabel.RIGHT);
JLabel cityLabel = new JLabel( "City: ", JLabel.RIGHT);
JLabel stateLabel = new JLabel( "State: ", JLabel.RIGHT);
JLabel zipLabel = new JLabel( "Zip: ", JLabel.RIGHT);
JLabel workLabel = new JLabel( "Work Number: ", JLabel.RIGHT);
JLabel faxLabel = new JLabel( "Fax Number: ", JLabel.RIGHT);
idLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
firstNameLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
lastNameLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
add1Label.setPreferredSize(eMailLabel.getPreferredSize());//set same size
add2Label.setPreferredSize(eMailLabel.getPreferredSize());//set same size
cityLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
stateLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
zipLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
workLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
faxLabel.setPreferredSize(eMailLabel.getPreferredSize());//set same size
//Box for labels
Box labelPane = Box.createVerticalBox();
labelPane.add(idLabel);
labelPane.add(firstNameLabel);
labelPane.add(lastNameLabel);
labelPane.add(add1Label);
labelPane.add(add2Label);
labelPane.add(cityLabel);
labelPane.add(stateLabel);
labelPane.add(zipLabel);
labelPane.add(workLabel);
labelPane.add(faxLabel);
labelPane.add(eMailLabel);
//Box for input fields
Box infoPane = Box.createVerticalBox();
infoPane.add(personID);
infoPane.add(personFirstName);
infoPane.add(personLastName);
infoPane.add(personAdd1);
infoPane.add(personAdd2);
infoPane.add(personCity);
infoPane.add(personState);
infoPane.add(personZip);
infoPane.add(personWorkNum);
infoPane.add(personFaxNum);
infoPane.add(personEmail);
Box inputPane = Box.createHorizontalBox();
inputPane.add(labelPane);
inputPane.add(infoPane);
getContentPane().add(inputPane, BorderLayout.CENTER);
//Button Pane
JButton newButton = new JButton("New");
JButton deleteButton = new JButton("Delete");
JButton saveButton = new JButton("Save");
//Box for Buttons
JPanel buttonPane = new JPanel();
buttonPane.add(newButton);
buttonPane.add(deleteButton);
buttonPane.add(saveButton);
getContentPane().add(buttonPane, BorderLayout.SOUTH);
//add event listeners
newButton.addActionListener(this);
deleteButton.addActionListener(this);
saveButton.addActionListener(this);
rowSM.addListSelectionListener(this);
pack();
setVisible(true);
show();
openConnection();
}
public void actionPerformed(ActionEvent ae)
{
String cmd = ae.getActionCommand();
if (cmd.equalsIgnoreCase("NEW"))
{
}
else if (cmd.equalsIgnoreCase("DELETE"))
{
}
else if (cmd.equalsIgnoreCase("SAVE"))
{
}
}
public void openConnection()
{
try
{
//code to open the connection
Class.forName(driver);
connection = DriverManager.getConnection(url);
statement = connection.createStatement();//statement for query
tableModel.setResultSet(
statement.executeQuery("Select PersonID AS ID, LastName +', ' + FirstName AS NAME, Email FROM Person"));
}
catch(ClassNotFoundException cnfe)
{
System.err.println(cnfe);
}
catch(SQLException sqle)
{
System.err.println(sqle);
}
}
// I am having a problem here getting the selected row to display results in the DOS window
public void valueChanged(ListSelectionEvent e)
{
try
{
String selectedPerson = "SELECT * FROM Person Where PersonID = ?";
PreparedStatement displayPerson = connection.prepareStatement(selectedPerson);
int selectedRow = table.getSelectedRow();
displayPerson.setInt(1, selectedRow);
ResultSet queryInfo = displayPerson.executeQuery();
//output the results set data to DOS window to make sure SQL is working
System.out.println(queryInfo.getString("LastName") +" " +
queryInfo.getString("FirstName") + " " +
queryInfo.getInt("PersonID"));
}
catch (SQLException sqle)
{
System.err.println(sqle);
}
}
//inner class defining handler for window events
class WindowHandler extends WindowAdapter
{
//handler for window closing event
public void windowClosing(WindowEvent e)
{
dispose();
System.exit(0);
}
}
}
1