How to insert values along with auto generated field in msaccess

Question

I create a jForm in Netbeans which has 8 columns. I want to insert values of 2 to 7 fields at run time and the 1st field value should be auto incremented through the database. I create a table in ms access, where the first field has auto number datatype.

when i write the insert query, if i take 7 question marks , and pass values from 2 , it shows the error of " database fields values are not same". How to do this, that frst field is autoincremented and next fields will insert the values.. Here is my code...

Connection myconnection= null;
        String mydriver= "sun.jdbc.odbc.JdbcOdbcDriver";
        if((jTextField2.getText().length()==0)||(jTextArea1.getText().length()==0)||(jRadioButton1.getText().length()==0)||(jRadioButton2.getText().length()==0)||(jFormattedTextField1.getText().length()==0)||(jTextField3.getText().length()==0)||(jComboBox1.getSelectedIndex()==0)||(jFormattedTextField3.getText().length()==0)) {
            JOptionPane.showMessageDialog(rootPane, "Please fill all the details");
        }
        else
        {
            try
            {
                Class.forName(mydriver);
                myconnection=DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq= C:\\Documents and Settings\\Administrator\\My Documents\\NetBeansProjects\\DAV REHABILITATION\\Davdb.accdb; Uid=Admin; Pwd=;");
                try
                {
                    String myquery= "insert into Patients values(?,?,?,?,?,?,?)";
                    PreparedStatement mystatement= myconnection.prepareStatement(myquery);
                    mystatement.setString(2,jTextField2.getText());
                    mystatement.setString(3,jTextArea1.getText());
                    if(jRadioButton1.isSelected()) {
                        mystatement.setString(4, "Male");
                    } else if(jRadioButton2.isSelected()) {
                        mystatement.setString(4, "Female");
                    }
                    mystatement.setString(5, jFormattedTextField1.getText());
                    mystatement.setString(6, jTextField3.getText());
                    mystatement.setString(7, jComboBox1.getSelectedItem().toString());
                    mystatement.setString(8, jFormattedTextField3.getText());
                    mystatement.executeUpdate();
                    JOptionPane.showMessageDialog(rootPane,"Data Saved");
                    mainform obj=new mainform();
                    this.setVisible(false);
                    obj.setVisible(true);

                } 
             catch (Exception e)
             {
                    JOptionPane.showMessageDialog(rootPane, "Error in Query. " + e.getMessage());
                }


        }
            catch(Exception e)
            {
                JOptionPane.showMessageDialog(rootPane, " Error Occured.Connection wrong" + e.getMessage());
            }


        }



    }

specify the fieldnames to insert to : `insert into Patients(field2,field3,...` — Flakes, Jul 27 '15 at 07:05

score 1 · Answer 1 · answered Jul 27 '15 at 07:03

How to do this, that frst field is autoincremented and next fields will insert the values

You don't, instead, specify the columns you want to insert into (and the order)

String myquery= "insert into Patients (column1, column2, column3, column4, column5, column6) values(?,?,?,?,?,?,?)";

mystatement.setString(1,jTextField2.getText());
mystatement.setString(2,jTextArea1.getText());
if(jRadioButton1.isSelected()) {
    mystatement.setString(3, "Male");
} else if(jRadioButton2.isSelected()) {
    mystatement.setString(3, "Female");
}
mystatement.setString(4, jFormattedTextField1.getText());
mystatement.setString(5, jTextField3.getText());
mystatement.setString(6, jComboBox1.getSelectedItem().toString());
mystatement.setString(7, jFormattedTextField3.getText());
mystatement.executeUpdate();

Where columnX is the name of the column

The number use use in the setX method indicates the value for the placeholder in the query

See The SQL INSERT INTO statement for more details

SatyaTNV · Answer 2 · 2015-07-27T07:10:53.977

0

your table is containing total 8 columns.

your query is wrong

String myquery= "insert into Patients values(?,?,?,?,?,?,?)";//only 7 indexes

You must write respective column names since you are not entering all the column values here.

   String myquery= "insert into Patients(col1,col2,col3,col4,col5,col6,col7) values(?,?,?,?,?,?,?)";

Since you are passing values to only 7 columns and you are not entering a value to your first column in your table. Here col1, col2, etc are your table column names whose values you are suppose to insert.

edited Jul 27 '15 at 07:10

answered Jul 27 '15 at 07:02

SatyaTNV

4,137
3
15
31

it works, but it is starting the auto number field by 3. – Shaina Gupta Jul 27 '15 at 07:14
previously you may entered some values – SatyaTNV Jul 27 '15 at 07:16
see this link http://stackoverflow.com/questions/10753505/mysql-id-auto-increment-doesnt-starts-from-0 @ShainaGupta – SatyaTNV Jul 27 '15 at 07:18

How to insert values along with auto generated field in msaccess

2 Answers2