prepareStatement Help

[Mayur Gosalia]

Hi,

Updating Prepared statement takes two criteria's. Both column names of the JTable. The problem i am having is as follows:

 	columnName = cvTable.getColumnName(columnSelected);
		
		try
		{
			results.absolute(rowSelected);
			update = connection.prepareStatement("update CV60s set"+columnName+" = ? where CV no = ?");
			
			update.setString(1, updatedValue);
			update.setString(2, cvNum);
			update.executeUpdate();
			
		}
		catch(SQLException sqlEx)
		{
			JOptionPane.showMessageDialog(null,"Unable to retrieve data"+sqlEx);
		}  

The problem i am having is inserting the columnName within the preparedstatement. I am trying get value from a cell and just insert that updated value into the database using these statement (i have everything but this working). Please advise.

Thanks

[Paul Clapham]

You'll need some space between the word "set" and the column name.

[Mayur Gosalia]

  update = connection.prepareStatement("update CV60s set "+columnName+" = ? where CV no = ?");  

doing the above (adding space) still throws a syntax error..i just want to store the update data in 1 cell..any ideas?

[Paul Sturrock]

You have a column called "CV no"? Normally spaces are not allowed in column names.

[Mayur Gosalia]

yes i have a column by that name..Ah right ok i will give that a try..thanks

[Costi Ciudatu]

The column name in the WHERE clause is most likely the cause for the syntax error: "where CV no = ?".
Even if your DBMS allows column names to contain spaces, these column names must be escaped in the SQL queries.
The escape char/sequence depends on the DBMS that you're using; you'll need to type something like <pre>`CV no`</pre> or <pre>[CV no]</pre> or whatever.

[Mayur Gosalia]

ok i have changed the column name to CV wasnt an issue.

 update = connection.prepareStatement("update CV60s set "+columnName+" = ? where CV = ?");   

Still i get a syntax error. I googled this and somewhere it said you can't pass variable into prepare statement so i am not sure. If thats case is there an alternative?

[Costi Ciudatu]

1) What's your DBMS ?
2) What is the value of columnName when you get to the prepareStatement() call.
3) What's the stack-trace ?

[Mayur Gosalia]

Ok my fault i have fixed this..it was referring to an incorrect table that i was testing, which has exactly the same info, sincere apologies.

I am using access, so if i wanted to include spaces in my column name how would i do that? I mean which 1 would i use? `Cv no` or [Cv no] ?

[Costi Ciudatu]

I'm pretty sure it's the [...] in Microsoft products.

However, I'm almost positive you already found it yourself by now -- even if you had to iterate through all ASCII codes and stop when no exception is thrown.

[Mayur Gosalia]

you work wonders mate..its wrks gd..
A slight prblem though, wen i update the 1st value in table its throw a invaliid cursor state error (0)..i can update any cell except cells in 1st row..any ideas?

Ps its backtips in ms prducts for space in column name..

[Paul Clapham]

At a guess, your code is written as if the first row is row zero? The first row is row 1.

[Mayur Gosalia]

Paul Clapham wrote:At a guess, your code is written as if the first row is row zero? The first row is row 1.

I havent changed any of the numbering..All i am doign is getting the selected row and column value and then storing that in database. This works fine apart from the 1st one because 1st row is row 0. As getselectedRow returns 0, which is causing this problem i think..anyway to overcome this? I can post the code if you wish!

[Mayur Gosalia]

i have fixed it thanks..you guys are great..

resultSet cannot be on position 0..that was the problem.