problem in having two ResultSet in single statement.

[mits28]

I am using two ResultSet in a single statement..this code is a part of servlet..

String temp=request.getParameter("cid");
 int cid= Integer.parseInt(temp);

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Mydatasource");
PreparedStatement ps = con.prepareStatement("select * from Products where cid=?");
ps.setInt(1, cid);
ResultSet rs = ps.executeQuery();
while(rs.next())
{

out.println("<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "rs.getString(2)" + "</a>");
//rs.getString get the name of the bike which has to be made a hyperlink
}

but when i run this.it give java.sql.SQLEXCEPTION (No Data found)..but when i use only single ResultSet it Displays correctly..

[Majestics]

Problem lies in the column name, which you are using in the rs.getString() method. Correct your column name.

[mits28]

i am giving index numb in rs.getString...what problem in that

[Majestics]

First of All "rs.getString(indexno)" is treated as an string , it has no effect... You must write rs.getString(indexno). and is this complete code?

[mits28]

sory for that mistake..
No this is not comlete code..i am trying to show you where i am getting problem..i can't post complete application here;..

[jahanruhi3@gmai]

package com.batch;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import DBConnection.DBConnect;
public class Batch {
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public ArrayList BatchAction(String ik) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs;
        ResultSet rs1;
        ArrayList al = new ArrayList();
        conn = DBConnect.getConnection();
        final SimpleDateFormat monthDayYearformatter = new SimpleDateFormat(
                "dd MM yyyy HH:mm:ss");
        System.out.print("is it really working....");
        try {
            st = conn.createStatement();
            rs=st.executeQuery("select * from batch order by start_date DESC");
            System.out.println("select * from batch order by start_date DESC");
            while (rs.next()) {
                ArrayList<String> aL = new ArrayList<String>();
                aL.add(Integer.toString(rs.getInt(1)));
                rs1=st.executeQuery("select name from project_stage");
                {
                    ArrayList<String> aL1 = new ArrayList<String>();
                    while(rs1.next()){
                        aL1.add(rs1.getString(5));
                    }al.add(aL1);
                }

                aL.add(Integer.toString(rs.getInt(2)));
                java.util.Date d = rs.getTimestamp(3);
                if (d != null) {
                    String date = monthDayYearformatter.format(d);
                    aL.add(date);
                }
                else{
                    aL.add("No Start Date Found ");
                }
                java.util.Date d1 = rs.getTimestamp(4);
                if (d1 != null) {
                    String date1 = monthDayYearformatter.format(d1);
                    aL.add(date1);
                }
                else{
                    aL.add("No End Date Found");
                }
                String stageType = rs.getString(5);
                if(stageType != null)

                {
                    aL.add(stageType);
                }
                else
                {

                aL.add("No Stage Type Data Found ");
                }
                aL.add(Integer.toString(rs.getInt(6)));
                al.add(aL);
            }

            conn.setAutoCommit(true);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return al;

    }
}

Error:

14:52:34,377 ERROR [STDERR] java.sql.SQLException: Invalid column index
14:52:34,377 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
14:52:34,377 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
14:52:34,377 ERROR [STDERR]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
14:52:34,377 ERROR [STDERR]     at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:379)
14:52:34,377 ERROR [STDERR]     at com.batch.Batch.BatchAction(Batch.java:29)
14:52:34,377 ERROR [STDERR]     at org.apache.jsp.user.batch_jsp._jspService(batch_jsp.java:136)

[mits28]

what is this??

[jahanruhi3@gmai]

i am having the problem with resul set could u pls help me out

[zeroliken]

i am having the problem with resul set could u pls help me out

@jahanruhi3@gmai
Start your own thread if you need help with yours

[mits28]

hey someone please solve my problem..

[jahanruhi3@gmai]

after creating the table Products in database
SQL> COMMIT;
TRY TO RUN THE PROGRAM,IT WILL BE SOLVED

[jahanruhi3@gmai]

After creating table products do commit in sql

[mits28]

sorry i didn't get..how to commit..i am creating table in Microsoft office access

[reubenj]

I think it was suggested by majestics but you need to check your columnIndex. I am not certain about how MS access handles sql queries, if you could post the layout and contents of your products table I may get a clearer picture.

[mits28]

in products table..there are 12 columns
they are..(in order)(name and its type)
pid - Autonumber,
BikeName - Text,
Price - Number,
Mileage - Number,
BHP - Number,
TopSpeed -Number,
Acceleration - Number,
Displacement - Number,
Weight - Number,
TubeLess - Text,
DiscBrakes - Text,
Image - Text

contain information about different bikes

[Philippe.Lahaie]

may i ask where the second RecordSet is?

also i suggest using the column name as a string instead of column number like many others did, you put 2 but do you know if the columns are zero based or not? and it might be the second column now but if your database gets any modification over time and the tabel changes and it ends up in column #6 are you gonna feel like going through your code and replacing all the ".getString(2)" by ".getString(6")" ?

[mits28]

i will use column name from next time..but for this time..problem is not in column name or no...

[Philippe.Lahaie]

alright then would you mind giving me details about the problem, where is that second resultset? i only see 1 in your original post

what is the line that fires the SQL Exception?

[mits28]

it was not two resultset actualy..what i mean using rs.getInt and rs.getString in a single statement...
when i use

out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "Details" + "</a></td>");
it work properly

but when i use
out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "rs.getString(2)" + "</a></td>");
it give SQlException

[Philippe.Lahaie]

it was not two resultset actualy..what i mean using rs.getInt and rs.getString in a single statement...
when i use

out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "Details" + "</a></td>");
it work properly

but when i use
out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "rs.getString(2)" + "</a></td>");
it give SQlException

this is kinda weird because :

"'>" + "rs.getString(2)" + "</a></td>"

would just print out "'>rs.getString(2)</a></td>" literally, like someone mentioned earlier.

do me a favor and put your line in a comment for now, and paste this instead , tell me which fires what Exception specificly if any...

out.println("first attempt :" + rs.getInt(1));
out.println("second attempt :" + rs.getString(2));
out.println("third attempt :" + rs.getString(1));

also :

Throws:
SQLException - if a database access error occurs

so please tell us , which id you are trying to access and what is currently saved in the table for that row, exactly, for each column.

[mits28]

i tried for cid=1..
content of the table is

pid BikeName Price Mileage BHP TopSpeed Acceleration Displacement Weight Tubeless DiscBrakes Image cid
1 pulsar220 84000.00 30 21 144 2.84 220 165 n/a yes pulsar220.jpg 1

2 discover150 58000.00 55 12.8 115 5.3 150 121 no yes discover150.jpg 1

when i pasted your code..
output :-
pulsar 220 first attempt :1 java.sql.SQLException: No data found

instead of displaying both the bike deatails. it is giving exception

[Philippe.Lahaie]

k please try this :

try{
    out.println(rs.getString("BikeName")); //try this first , if it crashes
    //out.println(rs.getObject(2).toString()); //then remove the other print and uncomment this one
}catch(Exception e){e.printStackTrace();}

[mits28]

when i tried first one..it display both bikes name

[Philippe.Lahaie]

so no more exception when using column name huh? :P

[mits28]

sir..problem is not lying in using column name or no...as i explained before..when i use rs.getInt and rs.getString together i am getting exception..but when i use only getInt it is working properly..

[Philippe.Lahaie]

so what you are saying is

out.println(rs.getInt(1));
out.println(rs.getString("BikeName"));

worked but this :

out.println("id[" + rs.getInt(1) + "] name : " + rs.getString("BikeName"));

generates an exception? I highly doubt that using them in the same expression causes the error. please tell me what happens when you put this last line in your code.

[mits28]

no.. i am trying to create BikeName a hyperLink by this statment

out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + rs.getString(2) + "</a></td>");

but this creates an exception

when instead of retrieving BikeName from table..i create a simple Hyperlink by this statement

out.println("<td>" + "<a href = 's_BikeServlet?pid="+ rs.getInt(1) + "'>" + "Details" + "</a></td>");

it is working properly