gispe -4 Junior Poster in Training

Hi,

Im trying to insert to a table in sql that is related to another by a foreign key. The first table has an ID, a suppliers id and a date; and the other table has an ID (that for some records should have the same id from the other table), supply id, and some more supply things (like type, weight, price, etc).
so basically is one table with suppliers and when i bought the supplies that are in the second table. i dont use just one table, because there are different things stored in each of them.

What im doing is firstly insert in the table that has the suppliers, and then in another method get the id of that last inserted row, and pass it to the insert command to pass it to the other table; thing is tha its giving me an error with the ID, that for some reason does not allow me to insert same value that is in table 1.

Here i paste the code for both methods:
this is method for insert in table one

private boolean insertarComprasEncabezado(Compras_clase compraActual)
{
    Statement sql;
    ResultSet datos;

    try
    {
        sql = conexiones.cnn.createStatement();
        String query;

        query="INSERT INTO compras_encabezado (id_proveedor,fecha_compra) VALUES (" + compraActual.getProveedor().getId_proveedor() + ",'" + compraActual.getFecha_compra() + "')";

        sql.executeUpdate(query);

        JOptionPane.showMessageDialog(null, "SE CARGARON LOS DATOS CORRECTAMENTE" );

        listCompras.removeAll();
        return true;

    }
    catch (Exception e)
    {
        JOptionPane.showMessageDialog (null,e.getMessage() + "ERROR EN insertarComprasEncabezado") ;
        e.printStackTrace();
        return false;
    }
}

and here is for inserting in the other table:
it has two parts, the first one, gets the last inserted id of the first table, and then passes that value to the second one.
The insert for the first table works ok.

private boolean insertarComprasLinea(agregarInsumosAcompra_Clase insumo)
{
    Statement sql;
    ResultSet datos;

    int ultimacompra=-1;

    try
    {
        conexiones.conectaralabase();

        comand = conexiones.cnn.createStatement();
        rsCompras = comand.executeQuery("SELECT MAX(id_compra) FROM compras_encabezado"); // HERE I GET THE LAST ID

        ultimacompra = rsCompras.getInt(1);  
    }
    catch (Exception e)
    {
        JOptionPane.showMessageDialog (null,e.getMessage());
    }        

    try
    {
        sql = conexiones.cnn.createStatement();
        String query;

        query= "INSERT INTO compras_linea (id_compra,nombre_insumo,id_tipo_insumo,id_modo_compra,cantidad,largo,ancho,peso,precio)VALUES (" + ultimacompra + ",'" + insumo.getNombre_insumo() + "'," + insumo.getTipo_insumo().getId_insumo() + "," + insumo.getModo_compra().getId_mododecompra() + "," + insumo.getCantidad() + "," + insumo.getLargo() + "," + insumo.getAncho()+ "," + insumo.getPeso() + "," + insumo.getPrecio() + ")";

        sql.executeUpdate(query);

        JOptionPane.showMessageDialog(null, "SE CARGARON LOS DATOS CORRECTAMENTE" );

        listCompras.removeAll();
        return true;

    }
    catch (Exception e)
    {
        JOptionPane.showMessageDialog (null,e.getMessage() + "ERROR EN insertarCompraslinea") ;
        e.printStackTrace();
        return false;
    }
}   

private void guardarenenlinea()
{
    if (validarancho() && validarcantidad() && validarlargo() && validarmododecompra() && validarnombreinsumo() && validarpeso() && validarprecio() && validartipoinsumo())
    {
        agregarInsumosAcompra_Clase compralinea = null;
        compralinea = datosInsumos();

        insertarComprasLinea(compralinea);
    }
}    

Im not completly sure if thats done correctly. The other thing i do with the data i have to pass to the db table is this:

   private Compras_clase datosCompras()
{
    Compras_clase compraMod = new Compras_clase();

    compraMod.setFecha_compra(txtFechaCompra.getText());
    compraMod.setProveedor(getProveedor(cmbProveedores.getSelectedItem().toString()));

    return compraMod;
}


private agregarInsumosAcompra_Clase datosInsumos()
{
    agregarInsumosAcompra_Clase insumoMod = new agregarInsumosAcompra_Clase();

    insumoMod.setAncho(Float.parseFloat(txtPeso.getText()));
    insumoMod.setCantidad(Integer.parseInt(txtCantidad.getText()));
    insumoMod.setId_compra(ultimacompra); //------------------------------------------->>IDK IF ITS OK TO DO IT THIS WAY.
    insumoMod.setLargo(Float.parseFloat(txtLargo.getText()));
    insumoMod.setModo_compra(getModocompra(cmbModoCompra.getSelectedItem().toString()));
    insumoMod.setNombre_insumo(txtNombreInsumo.getText());
    insumoMod.setPeso(Float.parseFloat(txtPeso.getText()));
    insumoMod.setPrecio(Float.parseFloat(txtPrecio.getText()));
    insumoMod.setTipo_insumo(getTipoInsumo(cmbTipoInsumo.getSelectedItem().toString()));

    return insumoMod;
}    

the first method is for the table with suppliers and dates, and the other one for the supplies
this 2 methods are suppoused to get the information i typed and then create an object of that type of class and then that object can be used for the inserts.
i dont know if in the second one, the value to set the ID (insumoMod.setId_compra(ultimacompra);) is ok to put it this way; i have a method that returns the last value of the suppliers-date table. but does not seem to work that way either

hope my problem is clear.. if not, ask for clarification :)
thanks in advanced :)

ps: is there any way to pass values added to an arraylist directly to a table? i mean, with this the system is suppoused (besides inserting values to purchase table) to insert supplies value into supplies table in db. or is easier to insert 1by1 and then delete everything in case the purchase is canceled?

thanks! :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.