strange problem...help!!!!!

[kriti sharma]

the table transfer has a text field which stores sql queries. There are three queries in the table.
example-- insert into parul values('neeraj','27','male')
When the program runs only first two rows are entered in the table parul.why???

import java.sql.*;
class forest
{
Connection con=null;
forest()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc dbc:ForestDev","forestdev","forestdev");
String sql="select * from transfer";
Statement s=con.createStatement();
ResultSet rs=s.executeQuery(sql);
String query=null;

while(rs.next())
{
query=rs.getString(2);
Statement s1=con.createStatement();
s1.executeUpdate(query);
}
}
catch(Exception e)
{
e.printStackTrace();
}

}
public static void main(String str[])
{
forest f=new forest();
}
}

[Avi Abrami]

Kriti,
I'm not sure if this will solve your problem, but you should close the "Statement" objects, for example:

while(rs.next()) {
  query=rs.getString(2);
  Statement s1=con.createStatement();
  s1.executeUpdate(query);
  // CLOSE the 'Statement'!
  s1.close();
}

Good Luck,
Avi.

[Sanjeev Kaushik]

This is a good practice to close the Statement, ResultSet and Connection object. In large applications, connection leakage is a big problem if you don't close all these objects.

[Jeanne Boyarsky]

Kriti,
Does the sql in all three rows have a different value for the primary key of your other table?

[kriti sharma]

my other table does not have a primary key.
after closing statement,connection and resultset i got all three entries inserted.i tested,actually the closing of connection mattered.
I have another doubt.both of my tables(the one that holds the sql and the one into which values have to be inserted) have a primary key which is autonumber type (in MS Access).now i am not inserting the value of primary key,i want it to increment on its own.how can i do it?currently SQLException is being thrown that the number of values and destination fields are not same.will i have to explicitly write the value of key as well?

[Jeanne Boyarsky]

Kriti,
No, you don't have to explicitly write the value of the key. Just take its field name out of the list.

[kriti sharma]

to Jeanne: i inserted only those values which i wanted to and skipped the autonumber field it says...
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Number of query
values and destination fields are not the same.

[Blake Minghelli]

It's probably because it looks like you are writing the insert like this:

insert into mytable values (some_id,some_value,another_value)

When you do not provide a column list immediately following the table name, then values for ALL columns must be present in the "values" clause. But in your case, you do not want to provide a value for the id column since Access will auto-generate it. When that is the case, you must explicitly specify the columns you are providing values for, like this:

insert into mytable [B](col1, col2)[/B] values (some_value, another_value)

. By the way, I think it is always a good idea to explicity add the columns that you are providing values for (e.g. (col1,col2)) in your insert statement. Otherwise, if you add a new column to your table later, your code will not work without modification. Of course, you may want to modify the code when you add the new column, but you may not have to if the new column allows nulls or has a default value and it is not critical to provide a value at the time of your insert.

[kriti sharma]

thank you,my problem is solved!