aspose file tools*
The moose likes JDBC and the fly likes editing records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "editing records" Watch "editing records" New topic
Author

editing records

Brandi Love
Ranch Hand

Joined: Sep 19, 2003
Posts: 133
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

Joined: Apr 14, 2004
Posts: 10336

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Brandi Love
Ranch Hand

Joined: Sep 19, 2003
Posts: 133
thank you
Brandi Love
Ranch Hand

Joined: Sep 19, 2003
Posts: 133
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
Bartender

Joined: Oct 14, 2005
Posts: 18902
    
    8

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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Sep 19, 2003
Posts: 133
I switched to prepared statements and it works wonderfully. Thanks for the help!
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
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


There is no emoticon for what I am feeling!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: editing records