insert into db problem

Question

k2k 5 Posting Whiz

15 Years Ago

hi,
please assume it is already connected to the access database( it is connected) since i could get all the data out and put them onto a JTable.

What is wrong now ... is that i am trying to insert new data to the access db and i got

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 12.
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
	at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
	at DataHolder.addRecord(DataHolder.java:84)
	at Screen.actionPerformed(Screen.java:261)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)

please see part of my codes here:

////////////   in my database class /////////////// 
public void addRecord(int id, String fn, String ln, String phone, String add, String city, String st, String zip, int age, String sex, double hr, double hrs)
	{
		try
		{
			String query = "   INSERT INTO employees(id, firstName, lastName, phone, address, city, state, zip, age, sex, hourlyRate, hours) " +
						   "VALUES (id, fn, ln, phone, add, city, st, zip, age, sex, hr, hrs)        ";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(query);
			
		}
		
		catch(SQLException sqlex)
		{
			sqlex.printStackTrace();
		}
	}// end addRecord

/////////////////   let's say in the main class ///////// 

else if(e.getSource() == saveButton)
		{
			int id = new Integer(idField.getText());
			String fn = fnField.getText();
			String ln = lnField.getText();
			String phone = phoneField.getText();
			String add = addField.getText();
			String city= cityField.getText();
			String state = stateField.getText();
			String zip = zipField.getText();
			String sex = sexField.getText();
			int age = new Integer(ageField.getText());
			double hourly = new Double(hourlyField.getText());
			double hours = new Double(hoursField.getText());
			
		
			dh.addRecord(id, fn, ln, phone, add, city, state, zip, age, sex, hourly, hours);
	
		}

i double and triple checked my access that all the column names are matched to insert() ...

thanks very much.

java

5 Contributors
12 Replies
126 Views
8 Months Discussion Span
Latest Post 14 Years Ago Latest Post by javaAddict

peter_budo 2,532 Code tags enforcer

15 Years Ago

It is complaining it did not get all 12 parameters as declared in query. Do you have any sort of validation for entered data to handle empty fields?

Ezzaral 2,714 Posting Sage

15 Years Ago

It's complaining because you sent this exact string as your values

"VALUES (id, fn, ln, phone, add, city, st, zip, age, sex, hr, hrs)        "

You didn't concatenate them in or use a PreparedStatement to set them, you just sent that string to the db and those are not valid values for the insert.

Ezzaral 2,714 Posting Sage

15 Years Ago

Based on your code, I'm assuming you want to put those parameters from your method into the 'values()' clause of your insert. To do that, you need to build that sql string by concatenating the actual parameter values into a sql string "VALUES ("+id+","+ ... You current code has the variable names themselves as the values to insert into the table: VALUES (id, fn... and that isn't even a valid sql statement.

Reply to this topic

Be a part of the DaniWeb community

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

k2k 5 Posting Whiz · Answer 1 · 2009-04-08T01:24:28+00:00

It is complaining it did not get all 12 parameters as declared in query. Do you have any sort of validation for entered data to handle empty fields?

no, the codes i showed from last post is all i did. would you please give an example? but in my access file i don't have any empty field in between though.

was the compiler actually complaining about it doesn't have 12 field in the database? my access db has exactly 12 columns as follow:
id | firstName | lastName | ..... hours |

thanks

k2k 5 Posting Whiz · Answer 2 · 2009-04-08T07:48:50+00:00

It's complaining because you sent this exact string as your values
"VALUES (id, fn, ln, phone, add, city, st, zip, age, sex, hr, hrs)        "
You didn't concatenate them in or use a PreparedStatement to set them, you just sent that string to the db and those are not valid values for the insert.

huh, this is my first time trying sql class. thanks for the insight, i at lease know what to look at now.

i tried to google it, and i didn't find any very helpful example. if you may give a quick example, that would be great. thanks.

k2k 5 Posting Whiz · Answer 3 · 2009-04-08T08:22:25+00:00

opps, just found a good one, guess i'll be okay. thanks very much

JBeginer7891 0 Junior Poster in Training · Answer 4 · 2009-12-29T13:54:59+00:00

Hi! Can sum1 plz help?!!!
here is the prob I'm having

SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SQLState:     07001
VendorError:  -3010

that's when I click the insert button.

insertBtn.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        try {
          Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
          int i = statement
              .executeUpdate("INSERT INTO PatientTable VALUES("
                  + jtfPnum.getText() + ", " + "'"
                  + jtfFname.getText() + "', " + "'"
                  + jtfSname.getText() + "', " + "'"
                  + jtfLocation.getText() + "',"+"'"
                  + jtfDOB.getText() + "'," + "'"
                  + jtfDOR.getText() + "'," + "'"
                  + jtfRace.getText() + "',"+"'"
                  + jtfGender.getText()+"',"+"'"
                  + jtfStat.getText() + "',"+ "'"
                  + jtfInit.getText() + "',"+"'"
                  + jtfID.getText()+ "')"+";");
          errorText.append("Inserted " + i + " rows successfully");
          PatientNumberList.removeAll();
          loadAccounts();
        } catch (SQLException insertException) {
          displaySQLErrors(insertException);
        }
      }
    });

Please help! don't get it:-/

javaAddict 900 Nearly a Senior Poster Team Colleague Featured Poster · Answer 5 · 2009-12-29T14:57:56+00:00

Hi! Can sum1 plz help?!!!
here is the prob I'm having
SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters.

First of all Next Time start a new Thread.

My first guess would be that the table PatientTable has more columns than the ones you have at your INSERT query. Personally I always use this syntax:

INSERT into PatientTable (col1, col2, ..) values ('a', 'b', ...);

In that way you know what value goes where and you can omit some columns. I think that with your coding all the columns of the table need to have a value at the "insert" query, but with my way the table can have 10 columns (for example) and you can insert a new row by giving only value to one of the columns. Of course the columns that you leave empty must not have any NOT NULL constraints.

INSERT into PatientTable (col1, col2, col3) values ('a', 'b', 'c');

INSERT into PatientTable (col1) values ('a');

Of course knowing the structure of your table would help

JBeginer7891 0 Junior Poster in Training · Answer 6 · 2009-12-30T14:51:34+00:00

First of all Next Time start a new Thread.
My first guess would be that the table PatientTable has more columns than the ones you have at your INSERT query. Personally I always use this syntax:
INSERT into PatientTable (col1, col2, ..) values ('a', 'b', ...);
In that way you know what value goes where and you can omit some columns. I think that with your coding all the columns of the table need to have a value at the "insert" query, but with my way the table can have 10 columns (for example) and you can insert a new row by giving only value to one of the columns. Of course the columns that you leave empty must not have any NOT NULL constraints.
INSERT into PatientTable (col1, col2, col3) values ('a', 'b', 'c');

INSERT into PatientTable (col1) values ('a');
Of course knowing the structure of your table would help

Thanks JavaAddict but I already figured out the prob.
I left out the single quote around the first value.
here is the correct one

insertBtn = new JButton("Insert");
    insertBtn.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        try {
          Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
          int i = statement
              .executeUpdate("INSERT INTO PatientTable VALUES('"
                  + jtfPnum.getText() + "', " + "'"
                  + jtfFname.getText() + "', " + "'"
                  + jtfSname.getText() + "', " + "'"
                  + jtfLocation.getText() + "',"+"'"
                  + jtfDOB.getText() + "'," + "'"
                  + jtfDOR.getText() + "'," + "'"
                  + jtfRace.getText() + "',"+"'"
                  + jtfGender.getText()+"',"+"'"
                  + jtfStat.getText() + "',"+ "'"
                  + jtfInit.getText() + "',"+"'"
                  + jtfID.getText()+ "')");
          errorText.append( i + " Row(s) inserted  successfully");
          PatientNumberList.removeAll();
          loadAccounts();
        } catch (SQLException insertException) {
          displaySQLErrors(insertException);
        }
      }
    });

Now every buttons are working except the Get Info button, the prob is when I click on the patientNo in the textAreafield it gives me only for certain record. In other words when the prog first load and I select the first, second or third patientno it gives nothing , but the selection of the last record gives result and then backwards.
here is the code snippet for this button

//Do Get Account Button
    getBtn = new JButton("Get PatientInfo");
    getBtn.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        try {
          rs.first();
          while ((rs != null) && (rs.next())) {
            if (rs.getString("patientNo").equals(
                PatientNumberList.getSelectedValue()))
              break;
          }
          if (!rs.isAfterLast()) {
            jtfPnum.setText(rs.getString("patientNo"));
            jtfFname.setText(rs.getString("fname"));
	    jtfSname.setText(rs.getString("sname"));
	    jtfLocation.setText(rs.getString("location"));
	    jtfDOB.setText(rs.getString("dob"));
	    jtfDOR.setText(rs.getString("dor"));
	    jtfRace.setText(rs.getString("race"));
	    jtfGender.setText(rs.getString("gender"));
	    jtfStat.setText(rs.getString("status"));
	    jtfInit.setText(rs.getString("initials"));
	    jtfID.setText(rs.getString("idnum"));

          }
        } catch (SQLException selectException) {
          displaySQLErrors(selectException);
        }
      }
    });

Thanks Again and next time I'll start a new thread.:icon_smile:

javaAddict 900 Nearly a Senior Poster Team Colleague Featured Poster · Answer 7 · 2009-12-30T15:48:43+00:00

Thanks Again and next time I'll start a new thread.:icon_smile:

Then start a new thread. Also what you are doing is wrong at the actionPerformed.

You need to get the PatientNumberList.getSelectedValue() and run a "select" query based on that value every time you click the button.
Create an object with properties all those values that you want. In a separate method, open the connection, run the query, get the results and put them into that object. If PatientNumberList.getSelectedValue() is the primary key you will get only one row. Then close the connection, statement, resultset and return the object.
Then call the method in the actionPerformed with argument the PatientNumberList.getSelectedValue().

JBeginer7891 0 Junior Poster in Training · Answer 8 · 2009-12-30T16:01:10+00:00

Then start a new thread. Also what you are doing is wrong at the actionPerformed.
You need to get the PatientNumberList.getSelectedValue() and run a "select" query based on that value every time you click the button.
Create an object with properties all those values that you want. In a separate method, open the connection, run the query, get the results and put them into that object. If PatientNumberList.getSelectedValue() is the primary key you will get only one row. Then close the connection, statement, resultset and return the object.
Then call the method in the actionPerformed with argument the PatientNumberList.getSelectedValue().

Sorry don't think you got me right, the selection of certain patientNo in the TextArea gives result but when first loaded and then I click the first records it gives this exception

SQLException: No data found
SQLState: null
VendorEerror: 0

javaAddict 900 Nearly a Senior Poster Team Colleague Featured Poster · Answer 9 · 2009-12-30T16:13:40+00:00

javaAddict 900 Nearly a Senior Poster

14 Years Ago

Start a new thread