CODEWITHSUNDEEP
javasqldatabasesqlitesql-insert
PhantomKid
PhantomKid

Reputation: 187

How to insert in database sqlite3

I'm having trouble inserting data inside my database..this is my codes looks like..

rs = stat.executeQuery("Select * from students;");
while (rs.next()) {
    idNum = rs.getString("idNum");
    stat.executeUpdate(
        "INSERT INTO record VALUES (null,'" + idNum + "','" + descript +
             "'," + value + ",'" + user.getText() + "','" + timeStamp + "')"
                            );
}//while

As you can see I want to insert a data for every student rs = stat.executeQuery("Select * from students;"); and get all their student number idNum = rs.getString("idNum"); this is what inside the students table:

idNum..............Name

11000001.........Leonardo

11000002.........David

11000003.........Robert

11000004.........Anna

11000005.........May

now when I get all their idNum I want them to be inserted inside the table record that will looks like this:

idNum.........descript.........amount........blablablabla

11000001.......Fee...............30

11000002.......Fee...............30

11000003.......Fee...............30

11000004.......Fee...............30

11000005.......Fee...............30

the problem is only the first idNum is being inserted inside the table record like this:

idNum.........descript.........amount........blablablabla

11000001.......Fee...............30

Upvotes: 0

Views: 87

Answers (3)

CL.
CL.

Reputation: 180162

You can use a single statement to copy the data. (Using parameters avoids formatting problems with strings containing special characters.)

PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO record SELECT NULL, idNum, ?, ?, ?, ? FROM students");
ps.setString(1, descript);
ps.setInt   (2, value);
ps.setString(3, user.getText());
ps.setString(4, timeStamp);
ps.execute();

Upvotes: 0

Md. Abdul Bari
Md. Abdul Bari

Reputation: 116

Use an ArrayList to store all idNum from students table. Then loop through the list to insert into record table.

Upvotes: 0

GerritCap
GerritCap

Reputation: 1626

You shoulkd not use the same statement object stat twice: once you are reusing is to perform the update (in your case the insert) it closes the resultset you are looping over.

Upvotes: 2

Related Questions