How do I connect to multiple tables in JSP?

I have connected successfully to a database table and executed select statments on the table. I now want to do an insert into a different table but i get a SQL error which says: "SQLException: Result set not positioned properly, perhaps you need to call next(). "
How do I do what I am trying to do?
VbMonkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
PreparedStatement stmt = null ;
try
{
    stmt = conn.prepareStatement( "UPDATE tablename SET field=? where idfield=?" ) ;
    stmt.setString( 1, "TimYates" ) ;
    stmt.setInt( 2, id ) ;
    int rslt = stmt.executeUpdate() ;
    System.out.println( rslt + " lines updated" ) ;
}
catch( SQLException ex )
{
  ex.printStackTrace() ;
}
finally
{
    try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VbMonkAuthor Commented:
what does this do? Does it connect to another different table?
0
reginabCommented:
this statment assumes a generic connection method, which is reusable then for however many sql call to any number of tables you need to make.  conn, is either out of the api or you can define your own if you require specailization. it would be something like this:
try {
      Class.forName(driver);
           conn = DriverManager.getConnection(connString, userName, password);
        }catch(java.lang.ClassNotFoundException e) {
        }
        catch(java.sql.SQLException e){
        }
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

VbMonkAuthor Commented:
I have something like what reginab has outlined but i get the erro above. The PreparedStatement suggested byTimYates is like a template statement which can be executed a number of times when values are supplied. What if I want to execute a different statment from the one I executed before? for example,
Query1 = "insert into table1 (field1, field2) values (1, 2)"; and the second query statement is:
Query2 = "select * from table2 where id=2"
These are different queries. how can they be executed once?
0
reginabCommented:
import java.sql.*;
String Query1 = new String;
String Query2= new String;

Query1 = "insert into table1 (field1, field2) values (1, 2)"; and the second query statement is:
Query2 = "select * from table2 where id=2"

try {
   DriverManager.registerDriver(Class.forName("your-driver-class"));
   Connection con = DriverManager.getConnection("your-connect-string");
   Statement stmt = con.createStatement();
   stmt.execute(Query1);
   stmt.execute(Query2);
   stmt.close();
   con.close();
}
catch(ClassNotFoundException ex) {
   System.err.println("Could not load database driver: " + ex.getMessage());
}
catch(SQLException ex) {
   System.err.println("Database error: " + ex.getMessage());
}
0
VbMonkAuthor Commented:
I'll give you feedback when i test your code tomorrow reginab
0
VbMonkAuthor Commented:
Sorry my query statements need a result table. It doesn't work
0
reginabCommented:
well you have to have a result set to hold your results or a bean or something and you have to have a for loop to load it.  I guess I am not sure what it is you are actually trying to do here.  I went back and read your first post and it is confusing to me now, is the problem the result set or the problem query to multiple tables.
0
reginabCommented:
here is something you can play around with.
here are some details for using RowSet


http://www.javaworld.com/javaworld/jw-02-2001/jw-0202-cachedrow.html
http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html


here is an example of what to with a returned recordset when you need to work through a set of records.

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_10316891.html?query=java+fill+a+recordset&clearTAFilter=true

 public void executeQuery(String sQuery)
{
//For result returning SQL statements    
          this.theQuery = sQuery;
          ResultRows = new Vector();
          try
          {
            Statement queryStatement = databaseConnection.createStatement();
            ResultSet theResults = queryStatement.executeQuery(theQuery);
            ResultSetMetaData rsmeta = theResults.getMetaData();

            int numColumns = rsmeta.getColumnCount();
            String resultsText = new String("");
            while(theResults.next())
            {
              numRows++;
               Cols = new Vector();
               for(thisCol = 1; thisCol <= numColumns; thisCol++){
                    resultsText = theResults.getString(thisCol).trim();
                    Cols.addElement(resultsText);
               }
               ResultRows.addElement(Cols);
            }
          }
          catch(SQLException sqle)
          {
            System.out.println(sqle.toString());
          }    
     }
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.