Solved

Why is my uprs.UpdateRow not working?

Posted on 2008-10-22
20
569 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to parse custom JSON to POJO java 4 75
servlet doXXX methods 3 50
hibernate example using maven 12 42
How to configure empty element in XML Document parser? 15 16
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

831 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