Solved

Why is my uprs.UpdateRow not working?

Posted on 2008-10-22
20
573 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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
 

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
 

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

623 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