Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

editing records

 
Brandi Love
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have an access database, in which I have a table customers. There are columns - customer id, first name, last name, address line 1, address line 2, city, state, and zip code. The records are identified by the primary key, which is customer ID. I need to be able to access a record using the customer ID and change any information in the rest of the record. I'm having a bit of trouble finding the right sql statement to select and edit a record, can anyone help?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select and edit are two statements. If you only want to edit the record and know the customerId:

[ May 10, 2006: Message edited by: Paul Sturrock ]
 
Brandi Love
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you
 
Brandi Love
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, I executed this update string:



and I got the following error when it tried to execute the update

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Any idea what that means?
 
Paul Clapham
Sheriff
Pie
Posts: 20771
30
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It means you used a table name that doesn't exist in the database, or a column name that doesn't exist in the table. In your case it's the latter. If you had printed out your SQL you might have noticed that right away: it would look something like
update customers set firstName = Moose where customerId = 5
And the name you put in there -- Moose in my example -- is not the name of a column in the table.

In SQL you have to put 'apostrophes' around string literals, so one correction would be to add those apostrophes to your string. But a better correction would be to use a PreparedStatement, which lets you use parameters. Like this:That takes care of the apostrophes for you, and also deals with problem cases like where FirstNameText contains apostrophes itself. And when you decide that customerId needs to be a parameter too, replace your "5" by a question mark and call
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are updating a textual data type (which it would appear you are), remember text needs to be quoted (unless you change to using PreparedStatements).

(Wooft! - that was quick Paul! Look how much more you managed to type in the space of a couple of minutes - I'm going to have to learn to type with more than one finger)
[ May 10, 2006: Message edited by: Paul Sturrock ]
 
Brandi Love
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I switched to prepared statements and it works wonderfully. Thanks for the help!
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Brandi Love:
I switched to prepared statements and it works wonderfully. Thanks for the help!


Once again java.sql.PreparedStatement solves a problem caused by java.sql.Statement. I've never seen the reverse. Perhaps when people learn the JDBC they should only be taught about PreparedStatements
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic