I am trying to retieve the total salaries from a table where the customer name is X. I have the code below however there seems to be an error.

    public void total(String name){
  double totals=0;
  Properties conProps = new Properties();
          conProps.setProperty("user", "user");
        conProps.setProperty("password", "pass");
     try {
             con = DriverManager.getConnection("jdbc:mysql://91.208.99.2:3379/link", conProps);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            } catch (SQLException ex) {
                Logger.getLogger(AddProduct.class.getName()).log(Level.SEVERE, null, ex);
            }

    String query=("SELECT SUM(salary) FROM orders WHERE cus_name = ?");

   try {   
          con.setAutoCommit(false);

                st = (com.mysql.jdbc.PreparedStatement) con.prepareStatement(query);
    st.setString(1, name);
    ResultSet salaries=st.executeQuery();
    while (salaries.next())    
       totals+=salaries.getDouble("salary");


  }catch (  SQLException e) {
    e.printStackTrace();
  }
 System.out.println("Sum of column = " + totals);
}

I am getting Error:
java.sql.SQLException: Column 'salary' not found. I can confirm that the column salary is in the table..

apparently, no, it is not.
either you are pointing to an other database, or a wrong table, or you misspelled the name of the table.

Column 'salary' not found.

means just that.

ALright I have changed my code a bit:

public void total(){


        Properties conProps = new Properties();
          conProps.setProperty("user", "user");
        conProps.setProperty("password", "pass");
     try {
             con = DriverManager.getConnection("jdbc:mysql://91.208.99.2:3379/link", conProps);
                con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

            } catch (SQLException ex) {
                Logger.getLogger(AddProduct.class.getName()).log(Level.SEVERE, null, ex);
            }

    String query=("SELECT SUM(salary) FROM orders WHERE cus_name = ?");

   try {   
          con.setAutoCommit(false);
         st = (com.mysql.jdbc.PreparedStatement) con.prepareStatement(query);
            st.executeQuery();
            rs = st.getResultSet();
              while (rs.next()) {
    st.setString(1,customer.getSelectedItem().toString());   
         String tot = rs.getString("salary");

                jLabel3.setText(tot);

   }
  }catch (  SQLException e) {
    e.printStackTrace();
  }

}

the error I am getting is java.sql.SQLException: No value specified for parameter 1

I have sepecified the value for parameter one.

have you? what if rs is empty, and that iteration is never actually executed?
Also, you executed the query before you specified the value for the parameter.

String query=("SELECT SUM(salary) FROM orders WHERE cus_name = ?"); // CREATION OF QUERY
   try {   
          con.setAutoCommit(false);
         st = (com.mysql.jdbc.PreparedStatement) con.prepareStatement(query);
            st.executeQuery(); // EXECUTION OF QUERY
            rs = st.getResultSet();
              while (rs.next()) {
    st.setString(1,customer.getSelectedItem().toString()); // BASED ON THE RESULT OF THE QUERY, HERE YOU SET THE VALUE FOR THE PARAMETER IN THE QUERY (that is, if rs has a next)

Check my last comment. that is not just an error against your code (no, the parameter is not filled when you execute the query), but it's also logically very wrong. The question can not depend on the answer.

stultuske,

I have now specified the value for the parameter and changed the code again. However I am getting the same error : Column 'salary' not found.
I have checked my table names as well as the columns. I can't see anything wrong.

The table name is orders, the column salary...and the other colunm is cus_name

Edited 1 Year Ago by london-G

This question has already been answered. Start a new discussion instead.