• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

editing records

 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you
 
Brandi Love
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I switched to prepared statements and it works wonderfully. Thanks for the help!
 
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Time flies like an arrow. Fruit flies like a banana. Steve flies like a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic