retrieve data from MS SQL Server2000 and resultset problem
Hi,
Can anyone tell me why can't i retrieve all values from DB?The 1st record is also ignored.I have tried to retrieve values from DB,but the 1st record is always ignored or i can't see it.For instance, i have 20 records in my DB,but 19 appears in table,except the 1st 1.
JOptionPane.showInputDialog(null,"Pls enter date(mm/dd/yy),"Date",JOptionPane.INFORMATION_MESSAGE);
con = getConnection();
stmt = con.createStatement();
String showAll = "SELECT * FROM MonthlyTelephoneBill " +
"WHERE (Date =" + "'selectDate'");
billResultSet = stmt.executeQuery(showAll);
if(billResultSet.next())
{
if (SCROLLABLE)
DatabaseResultSetTableModel model = new ScrollingResultSetTableModel(billResultSet);
else
model = new CachingResultSetTableModel(billResultSet);
JTable table = new JTable(model);
JScrollPane scrollPane = new JScrollPane(table);
JOptionPane.showMessageDialog(null,scrollPane,"Database Info",JOptionPane.INFORMATION_MESSAGE);
}
[1065 byte] By [
marcalena] at [2007-9-26 5:31:45]
Where did the following classes come from: DatabaseResultSetTableModel,ScrollingResultSetTableModel,CachingResultSetTableModel? Is it part of J2SE,J2EE or some other API?
If you ran the code
int counter=0;
while(billResultSet.next())
{
System.out.println(billResultSet.getString(1));
counter++;
}
System.out.println("number of rows="+counter);
does it contain all 20 results? Maybe your table model doesn't like you checking to see if there are results without reseting the cursor back to beforeFirst().
Jamie
Hi,
ScrollingResultSetTableModel & cachingResultSetTableModel are another classes which i didn't post here.
I have used that method if(billResultSet.next()) to get resultset from MS SQL DB,& it runs well in my previous method.
OK,if i use while(billResultSet.next()),if no values can be retrieved,i don't get any message to let me no there is no value in my DB.
I have tried if(resultSet.next()) in my other methods,all runs well,except this billResultSet method.
So two of you said that probably my mistake is scrollingResultSetTableModel or cachingResultSetTableModel.But i copy these two methods from advance edition of java swing book.
The problem is that you are calling ResultSet.next() twice before reading the data. You test for data by calling next before you hand the resultset to the table models. When you give the table models the resultset, the first thing the do is call next(), so you can see where you are missing the first row. What you need to do is set the table models and then call getRowCount() to see if any results were returned.
By the way, I modified the resultSetTableModels my self, I'll post one here, you can make similar modifications to the other, it just makes the data look prettier:
public class ScrollingResultSetTableModel extends ResultSetTableModel {
public ScrollingResultSetTableModel(ResultSet rs) {
super(rs);
}
public Object getValueAt(int r, int c) {
try {
ResultSet rs = getResultSet();
rs.absolute(r+1);
Object o = new Object();
// This is the added code, it makes dates look nicer and does the correct things for currency, etc....
if(rsmd.isCurrency(c+1))
o = NumberFormat.getCurrencyInstance().format(rs.getDouble(c+1));
else if(rsmd.getColumnClassName(c+1).equals("java.lang.Integer"))
o = new Integer(rs.getInt(c+1));
else if(rsmd.getColumnClassName(c+1).equals("java.lang.Boolean"))
o = new Boolean(rs.getBoolean(c+1));
else if(rsmd.getColumnClassName(c+1).equals("java.lang.Byte"))
o = new Integer(rs.getInt(c+1));
else if(rsmd.getColumnClassName(c+1).equals("java.sql.Timestamp")) {
java.sql.Date d = (java.sql.Date)rs.getDate(c+1);
if(d != null)
o = DateFormat.getDateInstance(DateFormat.SHORT).format(d);
else
o = "";
}
else
o = rs.getString(c+1);
return o;
}
catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public int getRowCount() {
try {
ResultSet rs = getResultSet();
rs.last();
return rs.getRow();
}
catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
}
