Result Set problems

[Jennifer Sohl]

Hi guys. Can anyone tell me why I am only getting the last row when I pass my ResultSet object to another class??
Here's my code:

  while(rs.next()) {
count++;
if(rs.isLast()) {
city.setText(rs.getString("city"));
state.setText(rs.getString("state"));
}
else {
SelectCity sc = new SelectCity(rs);
sc.pack();
sc.setVisible(true);
}
}

I printed out the result set to test if it was working.(When I did this, I had the code that is passing my resultSet object to another class commented out.) I got all my records. I put back the code that passes the resultSet and tried printing my result set again. Now it only show the last record??? Am I missing something?
Thanks in advance!!

[Dave Vick]

Jennifer
I dont know if this is it or not but could it have anything to do with the type of resultset your getting back from the query?
I mean are you creating a default resultset which would be non-scrollable? I dont know how the internal cursor is maintained by theResultSet so maybe once you've started traversing it the cursor goes to the end if it is passsed into a method ?!? I dont know.
Or, is there code that you didn't post and the resultset only gets passed once you're at the end of it.

[Jennifer Sohl]

Thanks for the reply. I think I've got it figured out. Before I pass my ResultSet object, I had to do a rs.beforeFirst() to set the cursor to the beginning of the result set. Now I am seeing all of my records.
Thanks!!

[Jennifer Sohl]

Hi. I ran across another problem. Not sure what's going on. Here is my SELECT statement:
SELECT * FROM ZIP WHERE ZIP =" + s;
String s = zip.getText();
This executes great until I enter a zip code that starts with a 0. Then nothing comes back. What am I missing. Why can't my statement find the zip codes that begin with zero, but can find all the others? Does getText() strip off zeros? If so, how do I solve this problem??
Thanks in advance!!

[Dave Vick]

Jennifer
How are the zipcodes defined in the DB? I had a huge problem with that once until I realized the zip column was defined as an integer so whenever a new zip got added to the db it stripped off the leading zero.
As far as I know getText doesn't do anthing to the String it returns - can you print you query to a log file or to standard out before running it to see what it looks like.
[ March 26, 2002: Message edited by: Dave Vick ]

[Jennifer Sohl]

Hi Dave. The zip codes are defined as a nVarChar data type in SQL. That shouldn't strip zeros, should it??

[Dave Vick]

No it shouldn't. The easiest way to check is to make sure the zipcodes in the database have their zeros in front of them.
Can you print the query out before you execute it? To make sure it looks correct.
It looks like you are concatenating the zipcode your searching for to '' to make it a string. Try to do that before the query then just add the one string to the query.
Are you putting quotes around the string your looking for?
If none of those work post some more of the code and maybe I can spot something - what DB are you using by the way?
hope one of those helps

[Jennifer Sohl]

Hi Dave. I printed the query out to standard out before I ran it. It looked OK. I tried your other suggestions. Still didn't work. I am using an SQL database and the datatype of the zip codes is nVarChar. Heres some more of my code. Maybe you can see something I don't!

// Method to retrieve city and state based on what zip code was keyed.
public void getCityState() {
con = new SQLConnection("Master", "");
rs = null;
String s = zip.getText();
stmt = con.getStatement();
try {
StringBuffer sb = new StringBuffer();
String s1 = ("SELECT * FROM ZIP WHERE ZIP =" + s);

rs = stmt.executeQuery(s1);
int count = 0;
while(rs.next()) {
count++;
if(rs.isLast()) {
city.setText(rs.getString("city"));
state.setText(rs.getString("state"));
}
else {
rs.beforeFirst();
SelectCity sc = new SelectCity(rs);
sc.pack();
sc.setVisible(true);
}
}
if(count == 0) {
JOptionPane.showMessageDialog(null, "Postal code not found.", "Information", JOptionPane.INFORMATION_MESSAGE);
}
}
catch(SQLException sqle) {
JOptionPane.showMessageDialog(null, sqle, "Error!",JOptionPane.ERROR_MESSAGE);
}
}
}
  

Thanks for taking the time to help. I really appreciate it!
[ March 26, 2002: Message edited by: Jennifer Sohl ]

[Dave Vick]

Jennifer
The only thing that jumps out at me right away is that there are no quotes surrounding the zip your looking for.
Try this:
String s1 = ("SELECT * FROM ZIP WHERE ZIP ='" + s + "'");
That might do the trick for you. If not let me know. Also, I'm not familiar with the SQLConnection class, are there docs for it I can check out?
hope that helps

[Jennifer Sohl]

The quotes did the trick!! Thank you so much for your help! I REALLY appreciate it!

[Dave Vick]

Jennifer
Glad to have helped.
Happy coding
Dave