Win a copy of Java 9 Revealed this week in the Features new in Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

editing records  RSS feed

 
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
Posts: 22203
38
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
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!