problem with sql statement in my setValueAt method

[randy butler]

The following sql statement changes every entry in the current column of the cell selected by the user. I want only the current cell being edited to change.

String sql = "update Parts set "+this.getColumnName(column)+" = "+dbRepresentation(column, value)+" where id = " + this.getColumnName(0);

[Bauke Scholtz]

Sorry, your question is too vague. I have no idea what you mean with "setValueAt method" and with "current cell". Please elaborate the technical problem. What's the functional requirement? What happens? What happens not?

[randy butler]

functional requirement
for the user to be able to edit a cell in the JTable
JTable gets its data from mysql database and I am able to view the data from the database with no problem
I use the setValueAt() method in my tablemodel to update the database



public void setValueAt(Object value, int row, int column) {
((String[]) ResultSets.get(row)) [column] = (String) value;
mdbc = new MyDBConnectiion();
mdbc.init();
Connection con = mdbc.getMyConnection();
try{
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
}catch(SQLException e){
e.printStackTrace();
}

try{

String sql = "update Parts set "+this.getColumnName(column)+" = "+dbRepresentation(column, value)+" where id = " + this.getColumnName(0);
int update = stmt.executeUpdate(sql);

}catch(SQLException e){
e.printStackTrace();
System.err.println("Update failed");
}

}

What happens

is when the user edits one cell the entire column of the edited cell changes to the edit.

What I want to happen

is when the user selects a cell is being edited only that cell value changes in the database.

I hope this is clearer and thanks

[Paul Clapham]

Yes, that's right, the UPDATE statement you have there will update every row of the table. If you want to update specific rows, use a WHERE clause to specify which rows.

Edit: and you should really use a PreparedStatement so you don't have to mess with string concatenations (and later with escaping of quotes when you find they are a problem).

[randy butler]

I'm trying to use a where clause on the id field but I am coming up short somewhere.

[randy butler]

what if I use the ResultSet to update a row in my database something like this but I'm not sure were to go from here

   mdbc = new MyDBConnectiion();
        mdbc.init();
        Connection con = mdbc.getMyConnection();
        try{
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM Parts");
        while (resultSet.next()){
            resultSet.moveToInsertRow(); 

[Paul Clapham]

You're coming up short by just plain failing to use a WHERE clause. At least in that last post, you don't have any WHERE in your SQL.

The first thing that needs to happen is for each row in your table to correspond to a row in the database. If you don't have that connection then your design is broken. So let's assume you have that. The next thing you need is to be able to find the primary key for the row in the database table, given the row in the GUI table. If you can't do that then, again, your design is broken. A common way to do that is to have the key field (or fields) be a column of your GUI table.

Now generate an SQL update which looks roughly like this:

UPDATE table SET column2=(value from column2 of GUI) WHERE column1=(value from column1 of GUI)

Modify that to suit your actual data. And do use a PreparedStatement, you'll find it a lot easier to deal with.

[randy butler]

Ok so
PreparedStatement updateP = con.prepareStatement("update Parts set column2 = value where id = currentrow);

is this what your talking about?

connection does work i am able to insert and delete rows from the database.

thank you