Solved

Why is my uprs.UpdateRow not working?

Posted on 2008-10-22
20
571 Views
Last Modified: 2012-05-05
These three lines is not working well, database table "salestable" is not adding records.There are no errors.

uprsSales.moveToInsertRow();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

import java.sql.*;
public class InsertRows {
public static void main(String args[]) {
    
            try { 
            String url = "jdbc:mysql://localhost:3306/test2"; 
            Connection conn = DriverManager.getConnection(url,"root","root"); 
            
            PreparedStatement stmtInventory = conn.prepareStatement("SELECT * FROM inventorytable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            PreparedStatement stmtSales = conn.prepareStatement("SELECT * FROM salestable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            ResultSet uprsInventory; 
            ResultSet uprsSales;
            uprsInventory=stmtInventory.executeQuery();
            uprsSales=stmtSales.executeQuery();
            ResultSetMetaData uprsInventoryMetaData = uprsInventory.getMetaData();
            ResultSetMetaData uprsSalesMetaData = uprsSales.getMetaData();
 
 
    int numberOfColumns = uprsInventoryMetaData.getColumnCount();
    System.out.println("resultSet MetaData column Count=" + numberOfColumns);
 
 
            
 
 int columnnumber = 2;
float columnsold;
int rownumber;
while(columnnumber < numberOfColumns) {
  uprsInventory.absolute(2);
  
   int idvar1 = uprsInventory.getInt("id");
  System.out.print("id " + idvar1 + " ");
  
rownumber = uprsInventory.getRow();
float columnpresent = uprsInventory.getInt(columnnumber);
System.out.println("PRESENT. row=" + rownumber + " column=" + columnnumber + " value=" + columnpresent);
 
uprsInventory.previous();
rownumber = uprsInventory.getRow();
float columnprevious = uprsInventory.getInt(columnnumber);
System.out.println("PREVIOUS. row=" + rownumber + " column=" + columnnumber + " value=" + columnprevious);
 
 
columnsold = 0;
columnsold = columnprevious - columnpresent;
System.out.println("column sold " + columnsold);
 
uprsSales.moveToInsertRow();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();
 
 
columnnumber = columnnumber + 1;
 
}
uprsInventory.close();
stmtInventory.close();
uprsSales.close();
stmtSales.close();
conn.close();
 
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}

Open in new window

0
Comment
Question by:glynco
  • 13
  • 7
20 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774315
What is the error you are getting
0
 

Author Comment

by:glynco
ID: 22774322
There are no errors. Build was successful.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774327
i mean when you run this example are you getting any errors.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:glynco
ID: 22774339
There are no errors running the program.
Compiling 1 source file to C:\Documents and Settings\ALCO\My Documents\NetBeansProjects\JavaApplication60\build\classes
compile:
run:
resultSet MetaData column Count=4
id 2 PRESENT. row=2 column=2 value=90.0
PREVIOUS. row=1 column=2 value=100.0
column sold 10.0
column number to write salestable: 2
id 2 PRESENT. row=2 column=3 value=45.0
PREVIOUS. row=1 column=3 value=50.0
column sold 5.0
column number to write salestable: 3
BUILD SUCCESSFUL (total time: 1 second)

Open in new window

0
 

Author Comment

by:glynco
ID: 22774350
I just tried putting a value directly to
uprsSales.updateFloat(2,10);
but the record not added to the database.

System.out.println("column number to write salestable: " + columnnumber);
uprsSales.moveToInsertRow();
uprsSales.updateFloat(2, 10);
uprsSales.updateRow();
0
 

Author Comment

by:glynco
ID: 22774376
I tried
uprsSales.insertRow();
and the records added but got this error because I have to loop 3 times to write 3 column records.

SQLException: Field 'id' doesn't have a default value
0
 

Author Comment

by:glynco
ID: 22774423
I put autoincrement on key column ID and the uprsSales.insertRow(); works but the records are on 3 different rows instead it should be on the same row.
So uprsSales.updateRow(); should do the trick but I cant make it work.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774437
use this method moveToCurrentRow.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774444
0
 

Author Comment

by:glynco
ID: 22774452
I've changed this line:
            PreparedStatement stmtSales = conn.prepareStatement("INSERT INTO VALUES(?,?,?); salestable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);

got this error:
SQLException: Can not issue data manipulation statements with executeQuery().
0
 

Author Comment

by:glynco
ID: 22774507
Attached a copy of the salestable I use insertRow() instead of updateRow()
salestable.JPG
0
 

Author Comment

by:glynco
ID: 22774518
I tried putting uprsSales.previous(); so that it goes back 1 row after each while loop

uprsSales.moveToInsertRow();
uprsSales.previous();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.insertRow();

but I got this error:
SQLException: Not on insert row.
0
 

Author Comment

by:glynco
ID: 22774538
tried this:
uprsSales.last();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

got this error:
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1499)
        at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java:2065)
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774548
in which line number  NullPointerException came
0
 

Author Comment

by:glynco
ID: 22774568
I dont know. I've been trying changing the 3 lines  only.

Tried:
uprsSales.absolute(1);
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

error:
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1499)
        at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java:2065)
        at InsertRows.main(InsertRows.java:53)
Java Result: 1
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774588
0
 

Author Comment

by:glynco
ID: 22774745
I am using mySQL not Oracle so I dont have problem with that bug you referring.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774766
wait for sometime so that other experts can look into ur problem cos i hav n't worked in the advanced JDBC.
0
 

Author Comment

by:glynco
ID: 22775135
When I insert a row and enter a value 1 of column "id" using HeidiSql then it works.

So I try this to put this to insert a new row and put id value 1
uprsSales.moveToInsertRow(  );
uprsSales.updateInt(1,1);
uprsSales.insertRow();

then try to update
uprsSales.absolute(1);
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

the result is
one column gets the value but the other 2 column did not and got this error
SQLException: Duplicate entry '1' for key 1
I think this is because of the while loop for the 2nd and 3rd loop trying to overwrite "id"
0
 

Accepted Solution

by:
glynco earned 0 total points
ID: 22775153
I got it. I put the insertRow above the while loop.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Should localization be done inside spring controller 5 36
ejb entity bean example issue 2 25
servlet and mdb, jms error 1 54
Java syntax, or is it Selenium 6 37
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question