java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

[Anonymous]

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);
}
}

}

[Angela Lamb]

I think you're getting an invalid cursor error because the cursor is not set on any row in the ResultSet. Try changing your method code to add this:
while (queryInfo.next())
{
System.out.println(queryInfo.getString("LastName") +" " +
queryInfo.getString("FirstName") + " " +
queryInfo.getInt("PersonID"));
}
The next() function will return false if there is no row to move to (empty ResultSet) or will move the cursor to the first row.

[Rajeev Sharma]

Hi Mike,
I think that you should try out using resultset.next() method.
As the resultset doesn't have the pointer on the first record & hence you need to use the next() method for the first record.
Try this......
while(queryInfo.next())
{
queryInfo.getString("FirstName") + " " +
queryInfo.getInt("PersonID"));
}
Good Luck,
Rajeev

[Angela Lamb]

Rajeev, that is almost word for word what I said. Did you bother to read the rest of the posts before you answered?