Solved

Why is my uprs.UpdateRow not working?

Posted on 2008-10-22
20
568 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
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…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now