Solved

Result set won't return value

Posted on 2006-07-16
6
222 Views
Last Modified: 2010-03-31
Hi all

I have a really weird problem.

I have a java file that I'm trying to instantiate from values in a SQL Server database table.

I can connect to the database ok and start getting values from the view that I'm interested:

        try
        {      
              String strSQL = "SELECT * FROM qryCompliance_Officers WHERE Name_User = '" + strUsername + "'";
              Statement stmt = null;
              stmt = dbConnLogin.createStatement();
              ResultSet rs = null ;
              rs = stmt.executeQuery(strSQL);                
                  
                  rs.next();                        
                  
                  this.strName_User = strUsername;
                  this.strF_Name = rs.getString("F_Name");             
                  this.strL_Name = rs.getString("L_Name");
                  this.strFull_Name = rs.getString("Full_Name");
                  this.lngPosition_ID = rs.getLong("Position_ID");
                  this.strPosition = rs.getString("Position");

As you can see I have read a value from the resultset a total of 6 times.

The weird thing is as soon as I try to read another value:

this.lngOffice_ID = rs.getLong("Office_ID");

it throws a SQLException error with an error value of 0.  The column I am interested in exists, I even used the findColumn method of resultset to confirm it is there.  Similarly if I place:

this.lngOffice_ID = rs.getLong("Office_ID");

at the top of the list i.e.

                                                this.lngOffice_ID = rs.getLong("Office_ID");
                  this.strName_User = strUsername;
                  this.strF_Name = rs.getString("F_Name");             
                  this.strL_Name = rs.getString("L_Name");
                  this.strFull_Name = rs.getString("Full_Name");
                  this.lngPosition_ID = rs.getLong("Position_ID");
                  this.strPosition = rs.getString("Position");

that statement executes ok but then an error is thrown at the last line:

this.strPosition = rs.getString("Position");

It is as if all I can do is call something from the recordset a total of six times before it refuses to do any more.

Any help appreciated, I am completely at a loss.

Phil
0
Comment
Question by:pmccar06
6 Comments
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 20 total points
ID: 17117145
You need to call rs.next in a loop and only retrieve values if it returns true:


ResultSet rs = stmt.executeQuery(strSQL);              
while (rs.next()) {
    // Now get them
}                    
0
 
LVL 8

Accepted Solution

by:
koppcha earned 50 total points
ID: 17117517
Please try this
1> Instead  of "SELECT * FROM qryCompliance" try SELECT F_Name,L_Name,...
list all the columns you want to retrieve.
2>When the SQLException is thrown could you try to get the exact message it is throwing
catch(SQLException e) {
System.out.println(e.getMessage());
}
0
 
LVL 10

Assisted Solution

by:mukundha_expert
mukundha_expert earned 35 total points
ID: 17119711

Use ResultSetMetaData to find the number of columns its returning.
also check whether all the columns are returning,
ResultSetMetadata rms = rs.getMetaData () ;
for ( int i = 0 ; i < rms.getColumnCount() ; i ++ )
System.out.println (  rms.getColumnLabel ( i )  );


0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 92

Assisted Solution

by:objects
objects earned 20 total points
ID: 17120641
> You need to call rs.next in a loop and only retrieve values if it returns true:

unnecessary, and a row is already being returtned anyway

you could add an if, but it won't fix your probl;em.

               if (rs.next()) {
                  this.strName_User = strUsername;
                  this.strF_Name = rs.getString("F_Name");          
                  this.strL_Name = rs.getString("L_Name");
                  this.strFull_Name = rs.getString("Full_Name");
                  this.lngPosition_ID = rs.getLong("Position_ID");
                  this.strPosition = rs.getString("Position");

Not sure whats the cause of your problem, perhaps try a different driver. your code looks fine
0
 

Author Comment

by:pmccar06
ID: 17128357
Hi all

It turned out that my problem was something to do with the order that I was getting the column values.  If I changed by code so that the values were read in the order in which they appear in the query (view), then the problem dissapeared.  Don't ask me why, still weird, but solved the issue.

I tried to split the points the best way I could.  koppcha you post helped me the most of all, especially using the getMessage method from the error object.

Thanks all for your help.

Cheers Phil
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17128405
:-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
even odd program using while loop 3 31
eclipse apache tomcat admin console 52 73
use lov values 2 38
tomcat startup error 5 21
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now