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?
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
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 ]
Joined: Sep 19, 2003
I switched to prepared statements and it works wonderfully. Thanks for the help!