File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

strange problem...help!!!!!

 
kriti sharma
Ranch Hand
Posts: 160
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1141
1
Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kriti,
I'm not sure if this will solve your problem, but you should close the "Statement" objects, for example:

Good Luck,
Avi.
 
Sanjeev Kaushik
Ranch Hand
Posts: 105
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33700
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kriti,
Does the sql in all three rows have a different value for the primary key of your other table?
 
kriti sharma
Ranch Hand
Posts: 160
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33700
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 160
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 331
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's probably because it looks like you are writing the insert like this: 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:. 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
Ranch Hand
Posts: 160
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you,my problem is solved!
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic