• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Set multiple columns using prepare statement

 
Mayur Gosalia
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mayur Gosalia
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mayur Gosalia wrote:right ok..so each column is seperated by comma?

Yes.
 
Mayur Gosalia
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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();
}
}
}
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic