This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Set multiple columns using prepare statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Set multiple columns using prepare statement" Watch "Set multiple columns using prepare statement" New topic
Author

Set multiple columns using prepare statement

Mayur Gosalia
Ranch Hand

Joined: Jan 03, 2010
Posts: 54
Hi,

I have JTable, where the user sets the data and clicks update. What i want to do is update multiple columns in my database using prepare statement, i can update 1 column no problem but how do i update more then 1?



This is what i tried to do but it says Optional feature not included..any ideas?
Thank You
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You are missing a comma between your columns. Also, 'Part Number' looks odd - do you have columns with spaces in the name? I would expect a database to interpret a value in single quotes as a literal string value, not a column identifier.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mayur Gosalia
Ranch Hand

Joined: Jan 03, 2010
Posts: 54
right ok..so each column is seperated by comma?

yes i have spaces in my column name but if you add ` ` to the column name it works fine with access.

PS: they are sinlge quotes instead they are backtips.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30361
    
150

Mayur Gosalia wrote:right ok..so each column is seperated by comma?

Yes.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mayur Gosalia
Ranch Hand

Joined: Jan 03, 2010
Posts: 54


Tried the above gives me a syntax error. Also tried inserting the word set in front of each column name with and without space after comma no joy..i dont know what i am doing wrong.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Don't put a comma before the WHERE clause.

You're using Access? Then it comes with documentation on the version of SQL it supports. In the long run you might find it easier to track down that documentation and find out the correct syntax for yourself, rather than waiting for answers on a forum.
Joe McTigue
Ranch Hand

Joined: Mar 05, 2012
Posts: 52
try this:

public void updateprinterConsumableColumns(String stockLevel,
int recievedStock,
String averageUsage,
int partNumber) {

String queryString = "update PrinterConsumable set 'Stock Level' = ? 'Received Stock' = ? 'Average Usage' = ? where 'Part Number' = ?";
try {
conn = this.getDatabaseConnection();
PreparedStatement preparedStmt = conn.prepareStatement(queryString);
preparedStmt.setString(1, stockLevel);
preparedStmt.setInt(2, recievedStock);
preparedStmt.setString(3, averageUsage);
preparedStmt.setInt(4, partNumber);

preparedStmt.executeUpdate();

} catch (SQLException sqe) {
//System.out.println("SQLException: updateprinterConsumableColumns return -> " + sqe.getMessage());
sqe.printStackTrace();
} catch (NullPointerException npe) {
//System.out.println("NullPointerException: updateprinterConsumableColumns return -> " + npe.getMessage());
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Set multiple columns using prepare statement