Result set type is TYPE_FORWARD_ONLY

[Talhah Mafawalla]

Hello to everyone

I am having a problem scrolling through a result set that recieves data from a MS Access database. Essentially i would like to move forward and back through the result set while displaying the results in textfields. Ive go this to work fine with a MySQL database. The error that i get is as follows:

java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY
at sun.jdbc.odbc.JdbcOdbcResultSet.isLast(JdbcOdbcResultSet.java:2082)

import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import javax.swing.*;

public class NewClass {

JTextField tf = new JTextField(10);
JTextField tf1 = new JTextField(10);
JTextField tf2 = new JTextField(10);
JButton bt = new JButton("Next");
JButton bt1 = new JButton("Previous");
JButton bt2 = new JButton("Close");
JFrame fr = new JFrame();

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

public static void main(String[] args) {
new NewClass();
}

public NewClass() {
tf.setEditable(false);
tf1.setEditable(false);
tf2.setEditable(false);

// String driverClass = "com.mysql.jdbc.Driver";
// String url = "jdbc:mysql://localhost/company";
String driverClass = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbcdbc:companyAcc";
String userId = "root";

String password = "";
String sql = "SELECT deptNo,dname,loc FROM dept";

try {
Class.forName(driverClass);
conn = DriverManager.getConnection(url, userId, password);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

rs.next();
tf.setText(rs.getString("deptno"));
tf1.setText(rs.getString("dname"));
tf2.setText(rs.getString("loc"));

} catch(ClassNotFoundException e1) {
System.out.println("\n Class Not Found Exception: " + e1.getMessage());
} catch(SQLException e1) {
System.out.println("\n SQL Exception: " + e1.getMessage());
} catch(Exception e1) {
System.out.println("\n Exception: " + e1.getMessage());
}


bt.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {

try {
if(rs.isLast()) {
JOptionPane.showMessageDialog(null,"This is the last row.");
} else {
rs.next();
tf.setText(rs.getString("deptno"));
tf1.setText(rs.getString("dname"));
tf2.setText(rs.getString("loc"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
});

bt1.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {

try {
if(rs.isFirst()) {
JOptionPane.showMessageDialog(null,"This is the first row.");
} else {
rs.previous();
tf.setText(rs.getString("deptno"));
tf1.setText(rs.getString("dname"));
tf2.setText(rs.getString("loc"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
});

bt2.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
System.exit(0);
}
});

GridBagLayout gb = new GridBagLayout();
GridBagConstraints gc = new GridBagConstraints();

JPanel p1 = new JPanel();
p1.setLayout(gb);

JPanel p2 = new JPanel();
p2.setLayout(new FlowLayout());

fr.setLayout(new BorderLayout());

gc.anchor = GridBagConstraints.EAST;
JLabel lb = new JLabel("Dept No:", JLabel.RIGHT);
gc.gridx = 0;
gc.gridy = 0;
gb.setConstraints(lb,gc);
p1.add(lb);

gc.gridx = 1;
gc.gridy = 0;
tf.setEditable(false);
tf.setBackground(Color.WHITE);
gb.setConstraints(tf,gc);
p1.add(tf);

JLabel lb1 = new JLabel("Dept Name:", JLabel.RIGHT);
gc.gridx = 0;
gc.gridy = 1;
gb.setConstraints(lb1,gc);
p1.add(lb1);

gc.gridx = 1;
gc.gridy = 1;
tf1.setEditable(false);
tf1.setBackground(Color.WHITE);
gb.setConstraints(tf1,gc);
p1.add(tf1);

JLabel lb2 = new JLabel("Location:", JLabel.RIGHT);
gc.gridx = 0;
gc.gridy = 2;
gb.setConstraints(lb2,gc);
p1.add(lb2);

gc.gridx = 1;
gc.gridy = 2;
tf2.setEditable(false);
tf2.setBackground(Color.WHITE);
gb.setConstraints(tf2,gc);
p1.add(tf2);

p2.add(bt);
p2.add(bt1);
p2.add(bt2);
p2.add(bt3);

fr.getContentPane().add(p1, BorderLayout.CENTER);
fr.getContentPane().add(p2, BorderLayout.SOUTH);

fr.setBounds(150,150,400,400);
fr.setTitle("Department");
fr.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
fr.setVisible(true);

}
}

Sorry if i posted alot.

Thanks in advanced
Talhah

[Martin Simons]

Try changing

stmt = conn.createStatement();

to

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

MySQL ResultSets are SCROLL_INSENSITIVE by default, ODBC Bridge ResultSets are not. To tell you the truth, I don't know if the bridge even supports Scrollable ResultSets, but try as above and find out.

[Talhah Mafawalla]

Thanks Martin, the code was spot on.

The Result set can now scroll forward and back.

Thanks again
Talhah

[Martin Simons]

No Problem. Have fun.