while(rs1.next()) not returning any values

Question

public void addPhoneInvoice(String IMEI,String price,String name, 
  String pNumber) throws SQLException {

    con = GetConnection.GetConnection();
    stmt = con.createStatement();

    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    Date date = new Date();
    String date1 = dateFormat.format(date);

    this.price = Double.parseDouble(price);


    String updatePhn = "DELETE FROM phonestock where IMEI = '"+IMEI+"'";
    int j = stmt.executeUpdate(updatePhn);
    if(j!=0){

    String getDescriptionQuary = "SELECT PhoneName,WarrantyPeriod,YearORMonth FROM      phonestock WHERE IMEI = '"+IMEI+"'";
    ResultSet rs1 = stmt.executeQuery(getDescriptionQuary);

    while(rs1.next()){
        this.phoneName = rs1.getString("PhoneName");
        this.warranty = rs1.getInt("WarrantyPeriod");
        this.yearOrMonth = rs1.getString("YearORMonth");
    }   

    String addPhnTemp = "INSERT INTO invoiceTemp(Description,Date,IMEI,Price,Amount,ContactName,ContactNumber,Warranty,YearOrMonth) VALUES ('"+phoneName+"','"+date1+"','"+IMEI+"',"+price+","+price+",'"+name+"','"+pNumber+"',"+warranty+",'"+yearOrMonth+"')";
    String addPhnPer = "INSERT INTO invoicepermanent(Description,Date,IMEI,Price,Amount,ContactName,ContactNumber,Warranty,YearOrMonth) VALUES ('"+phoneName+"','"+date1+"','"+IMEI+"',"+price+","+price+",'"+name+"','"+pNumber+"',"+warranty+",'"+yearOrMonth+"')";

    int i = stmt.executeUpdate(addPhnTemp);
    i = stmt.executeUpdate(addPhnPer);           

    if(i != 0)
        JOptionPane.showMessageDialog(null,"Success");
    }
    else
        JOptionPane.showMessageDialog(null,"Error");
}

}

this shows Success, but phoneName, warranty, and yearOrMonth didn't return from the database.

Well, how do you know your query actually returns the results you expect? Did you run it outside of the app? — OldProgrammer, Aug 10 '14 at 17:53
Just a suggestion, you should not write queries like `String updatePhn = "DELETE FROM phonestock where IMEI = '"+IMEI+"'";` it makes the code prone to SQLinjection resulting in unauthorized access to your database, use `preparedstatement` instead. — user63762453, Aug 10 '14 at 18:12

score 0 · Accepted Answer · answered Aug 10 '14 at 17:53

0

Because executeUpdate returns the number of rows affected. If it's greater than 0, nothing was deleted and you're checking the same IMEI, which is gone. Therefore, if you say `System.err.println(rs1.isLast()); it will return true, because there are no rows found by your select statement.

answered Aug 10 '14 at 17:53

hd1

33,938
5
80
91

Now be a good netizen and vote up all the answers that helped? – hd1 Aug 10 '14 at 19:50

score 0 · Answer 2 · answered Aug 10 '14 at 17:57

0

Don't think these INSERT queries executed successfully. Date is reserved word for Mysql. Put some quotes around it.

Here is the list of all Reserved Words in MySQL 5.5.40

answered Aug 10 '14 at 17:57

LHristov

1,103
7
16

**TIP** Add *trailing underscore* on all your database names. Example: `date_`. The SQL spec explicitly promises to never use the trailing underscore. So appending it to all your names means you’ll never have to worry about a collision with over a thousand key words and reserved words used by standard SQL and all the various extensions in database systems. See this [partial list of several hundred](http://docs.oracle.com/javase/tutorial/essential/io/scanning.html). More info in [my answer](http://stackoverflow.com/a/19758863/642706) on another question. – Basil Bourque Aug 10 '14 at 18:59
Whoops, that URL in my comment should be https://www.drupal.org/node/141051. Also, [this question](http://stackoverflow.com/q/8339396/642706) discusses various list of reserved words in SQL and the various database implementations. – Basil Bourque Aug 10 '14 at 19:11

while(rs1.next()) not returning any values

2 Answers2