Java sql update error : data type mismatch

Question

I have created a table in ms access. I have set the data type of ID to Auto Number in MS-access. In java when I try to update a record. the netBeans IDE gives me the error of " data type missmatch in criteria expression". But when I changed the ID number that was not in the table already it works well. The code is below.

String sql = "Update table1 set price ='" + txtPrice.getText() + "', quantity='" + txtQuantity.getText() + "', description='" + txtDescription.getText() + "' where id= " + txtid.getText() + "";
        try {
             pst = conn.prepareStatement(sql);
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Updated");
            UpdateJTable();
        } catch (Exception e) {

            JOptionPane.showMessageDialog(null, e);
        }

It's good to post the stacktrace, if you are receiving error. — Bhesh Gurung
– Bhesh Gurung, Commented Jun 3, 2012 at 5:45

abehrens · Accepted Answer · 2012-06-03 14:30:29Z

1

I think you may be confusing a regular Statement and PreparedStatement objects, too. PreparedStatement gives you the opportunity to use placeholders (the ? you might see in a lot of sql statements) and let the PreparedStatement do a bit more work for you.

Try writing your sql statement like this:

update table1 set price = ?, quantity = ?, description = ? where id = ?

Then with your PreparedStatement object, you would do:

pStmt.setInteger(1, Integer.valueOf(txtPrice.getText())

for each parameter. It has been a few years since I used PreparedStatement directly, but if memory serves, parameters are 1-based.

But your basic problem is one of casting. It also might make your code cleaner (and more secure) to use a PreparedStatement for this.

answered Jun 3, 2012 at 14:30

abehrens

1851 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanath · Accepted Answer · 2012-06-03 10:36:15Z

0

I am assuming the price and quantity columns are integers, hence you dont need to quote them.

Try the following sql:

String sql = "Update table1 set price =" + txtPrice.getText() + ",
              quantity=" + txtQuantity.getText() + ", description='" +
              txtDescription.getText() + "' where id= " + txtid.getText() + "";

Note the single-quotes are missing from price and quantity

edited Jun 3, 2012 at 10:36

Sanath

4,91211 gold badges58 silver badges88 bronze badges

answered Jun 3, 2012 at 5:43

Suraj Chandran

24.9k12 gold badges67 silver badges96 bronze badges

1 Comment

codo Over a year ago

Suraj chandran there is another error after doing that the error is of "Too few parameters" no whats this.

Sanath · Accepted Answer · 2012-06-03 10:37:13Z

0

Try to use the type casting in your id field first. like:

int id = Integer.parseInt(txtid.getText());

Then execute query :

String sql = "Update table1 set price ='" + txtPrice.getText() + "', quantity='" +
              txtQuantity.getText() + "', description='" + txtDescription.getText() + "' 
              where id= " + id + "";

edited Jun 3, 2012 at 10:37

Sanath

4,91211 gold badges58 silver badges88 bronze badges

answered Jun 3, 2012 at 5:48

Ravi

4012 gold badges8 silver badges12 bronze badges

Comments

FSP · Accepted Answer · 2012-06-03 16:57:00Z

0

for the too few parameters error that you are getting - double check the field names in you query.

answered Jun 3, 2012 at 16:57

FSP

4,8572 gold badges21 silver badges19 bronze badges

Collectives™ on Stack Overflow

Java sql update error : data type mismatch

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related