My problem is that I have to update the TOTAL_IMPORT field in the Customers table with the sum of the delivery notes (that match the client code, from the Customers table, that the user puts in) that are found in the Lines table. The client code that the user enters first I have to see if it exists in the database. And when I run the program it gives me the error of Before start of result set. All this in the Warehouse Database . Here I leave the code:
package BBDD;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Ejer9 {
public static void main(String[] args) {
Scanner sc = new Scanner (System.in);
String cliente;
int suma=0;
System.out.println("Introduce el cod de cliente al que quieras que se le actualice el importe:");
cliente=sc.nextLine();
try {
Connection primeraConexion=DriverManager.getConnection("jdbc:mysql://localhost/almacen","root","");
Statement a=primeraConexion.createStatement();
ResultSet segundo=a.executeQuery("SELECT count( * ) as BINGO FROM 'clientes' WHERE cliente LIKE "+"'"+cliente+"'");
while(segundo.next()) {
if (Integer.parseInt(segundo.getString("BINGO"))>0) {
//2. Crear objeto statement y metemos la sentencia SQL en un string para luego mediante el .executeUpdate insertarlo en la BBDD
Statement b=primeraConexion.createStatement();
Statement c=primeraConexion.createStatement();
ResultSet loQueActualiza=c.executeQuery("SELECT sum( precio ) as suma FROM lineas WHERE albaran = (SELECT albaran FROM albaranes WHERE cliente = (SELECT cliente FROM clientes WHERE cliente ="+"'"+cliente+"' ) )");
suma = Integer.parseInt(loQueActualiza.getString("suma"));
String actualizar="UPDATE 'clientes' SET 'TOTAL_FACTURA'="+"'"+suma+"' WHERE cliente LIKE "+"'"+cliente+"'";
//3. Comprobamos si se inserto la nueva tabla
b.executeUpdate(actualizar);
System.out.println("Datos insertados correctamente");
} else {
System.out.println("El cliente introducido no existe");
}
}
}catch(Exception e) {
System.out.println("No es posible conectar con la BBDD.");
System.out.println(e.getMessage());
}
}
}