How to store values into single cells in Ms Access without creating new rows?

Viewed 31

Basically what I'm trying to do is I'm creating a cost calculator program that calculates monthly cost of a business and stores the value of the cost based on the month. So I created an Ms Access database table with all the months as the column headers. However, whenever I attempt to store values in singular months, it ends up creating entire rows in which the value that I've entered will appear, but all the other months will just show '0'. So, for example, if I enter the total cost of January as 600, it will appear under the column of January as 600 but the rest of the row under the other months will show '0'. What I want to be able to do is to be able to input data into individual columns without creating new rows every time, is this possible?

The only thing I could think of to try was to use an 'If/Else' condition, basically I created a JComboBox and if the user selected a particular month, I would compare the inputted string with the string of that month and then using PrepareStatement I would input the value into that particular column, however this didn't seem to work out so well as I got errors regarding parameter and in the event that it did work out, it still filled the entire row.

private void stcostActionPerformed(java.awt.event.ActionEvent evt) {
    String mbox = mntbox.getSelectedItem().toString(); //JComboBox in which the user can select the months
    String tctxt = tcosttxt.getText(); //JTextField which displays the cost, I converted it into string to use for storing in database. 
    try{
        con = Connections.getConnection(); //Database connection variable created in a separate class
        if(mbox == "January"){
           pst = con.prepareStatement("insert into MonthlyCosts(January) values (?)");
           pst.setString(1, tctxt);
        }
        else if(mbox == "February"){
            pst = con.prepareStatement("insert into MonthlyCosts(February) values (?)");
            pst.setString(2, tctxt);
        }
        else if(mbox == "March"){
            pst = con.prepareStatement("insert into MonthlyCosts(March) values (?)");
            pst.setString(3, tctxt);
        }
        else if(mbox == "April"){
        pst = con.prepareStatement("insert into MonthlyCosts(April) values (?)");    
            pst.setString(4, tctxt);
        }
        else if(mbox == "May"){
            pst = con.prepareStatement("insert into MonthlyCosts(May) values (?)");
            pst.setString(5, tctxt);
        }
        else if(mbox == "June"){
            pst = con.prepareStatement("insert into MonthlyCosts(June) values (?)");
            pst.setString(6, tctxt);
        }
        else if(mbox == "July"){
            pst = con.prepareStatement("insert into MonthlyCosts(July) values (?)");
            pst.setString(7, tctxt);
        }
        else if(mbox == "August"){
            pst = con.prepareStatement("insert into MonthlyCosts(August) values (?)");
            pst.setString(8, tctxt);
        }
        else if(mbox == "September"){
            pst = con.prepareStatement("insert into MonthlyCosts(September) values (?)");
            pst.setString(9, tctxt);
        }
        else if(mbox == "October"){
            pst = con.prepareStatement("insert into MonthlyCosts(October) values (?)");
            pst.setString(10, tctxt);
        }
        else if(mbox == "November"){
            pst = con.prepareStatement("insert into MonthlyCosts(November) values (?)");
            pst.setString(11, tctxt);
        }
        else if(mbox == "December"){
            pst = con.prepareStatement("insert into MonthlyCosts(December) values (?)");
            pst.setString(12, tctxt);
        }
        else{
            JOptionPane.showMessageDialog(rootPane, "You have not enterred a month to store");
        }
        int conf = JOptionPane.showConfirmDialog(rootPane, "Confirm to Store", "Are You Sure?", JOptionPane.YES_NO_OPTION);
        if(conf==0){
            pst.execute();
            JOptionPane.showMessageDialog(rootPane, "Data is Saved", "Done", 1);
            clearFields();
        } else{
            JOptionPane.showMessageDialog(rootPane, "Data Not Saved");
        }
        pst.execute();
    }catch(Exception e){
        System.out.println(""+e);
    }

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 Invalid argument in JDBC call: parameter index out of range: 2

0 Answers
Related