CODEWITHSUNDEEP
mysql
user7786182
user7786182

Reputation:

Calculating the quantity and price from 2 different tables in MYSQL

I have two tables, ordertable (which the user uses to add orders and calculate) and product which contains the price per unit for certain products. What should happen in theory when a user presses a calculate button is the quantity which the user entered will be multiplied against the cost of the product they are selling. The query can work on the mysql console so it's to do with how i'm trying to input it into the textfield.

Below is my query.

public void calculateOrder(ActionEvent event){

String total = totalBox.getText();

String queryUpdate="UPDATE ordertable o JOIN product p ON p.productID = o.productID set o.total = o.amount * p.cost";
 String updateBox="SELECT total FROM ordertable";
try{

    query=c.prepareStatement(queryUpdate);
    query=c.prepareStatement(updateBox);
    query.setString(1, total);
    //query.setString(2, productID);
    query.execute();


       query.close();
        rs.close();

    Alert confirmation = new Alert(Alert.AlertType.CONFIRMATION, "Calculated");

    confirmation.show();

}

 catch(SQLException e){

     System.out.println(e);
 }

}

Below is the error. java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

Upvotes: 0

Views: 837

Answers (1)

DarbyM
DarbyM

Reputation: 1203

I believe your Query has an issue. If I recall correctly in your usage, o.quantity is not known within the nested query. Try reformatting it as follows:

UPDATE ordertable o 
INNER JOIN products p 
        ON p.productionID = o.productionID
set o.cost =  o.quantity * p.price

Upvotes: 1

Related Questions