SQL WHERE issue 2

[styx218]

This is a simple issue but ive been staring at it so long i cant get it and its driving me mad

statement = _connection.prepareStatement("INSERT INTO users (option1,option2,option3,option4,option5,option6,option7,option8,option9,option10,option11) VALUES (?,?,?,?,?,?,?,?,?,?,?) WHERE userName= 'heggy' ");

"WHERE userName= 'heggy' " does not like this part.I have heggy stored in table users under field userName. What am i doing wrong?

 

[ilyad]

Did you try to use HEGGY (in upper case) instead?

 

[monksnake]

If you already have the values, you don't need to add

WHERE userName= 'heggy'

at all.

Otherwise, you grab the info in a Select statement. Something like:

INSERT INTO users [!]select[/!] col1, col2, col.... [from tableName[/!] WHERE userName= 'heggy'

<.

 

[monksnake]

mistype in my code

remove

[from tableName[/!]

<.

 

[AlexCuse]

Are you looking to change the values where the user name is 'heggy'?

If so, you need to use an UPDATE not an INSERT.

If you are looking to insert, monksnake is right, you can't have a WHERE unless you also have FROM.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[styx218]

Im getting the values from a bean which i need to return

statement.setString(1, registerTeam.getGoalkeeper());
statement.setString(2, registerTeam.getRightBack());
statement.setString(3, registerTeam.getLeftBack());

and so forth for all the options...

So it would be something like

INSERT INTO users select option1,option2..... WHERE username = 'heggy'??

 

[styx218]

I dont have the values im trying to add to option1,option2 fields for the specific user 'heggy'

 

[ilyad]

I didn't notice that you were using insert into (field1, field2,..) values (...) syntax. If this is the case, everyone here is right, you can not use where. Otherwise your last statement looks correct to me.

 

[AlexCuse]

NO!

You can't use a WHERE in that fashion.

It looks to me like you are trying to update an existing row. Maybe this will work for you (you need to fill in the rest of course)?

[COLOR=blue]update[/color] users
[COLOR=blue]set[/color] option1 = ?
, option2 = ?
, option3 = ?
...
...[COLOR=blue]WHERE[/color] userName = [COLOR=red]'Heggy'[/color]

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[styx218]

Ok i have implemented above method which has made a difference but however,is still not adding to db

my print stack trace is showing that data is being passed to option1... but is still throwing an error

...syntax to use near '(option1 ='D James 3.0',option2 ='F Quedrue 2.5',option3 ='M Ferrari 2.9',option' at line 1

is it something to do with the 3.0 part of the string? i have name and vlaue all in one string,did not think it would be a problem to do so?? the option fields are of char() data type

thanks for all your help guys im quite stuck without you

 

[AlexCuse]

why do you have a parentheses?

Can you post the whole query?

Ignorance of certain subjects is a great part of wisdom

 

[styx218]

The values are being submitted from a form via a bean TeamRegisterBean.I have changed the query around,having tried with above solution from alex,i have changed data type to varchar and entered a new userName called styx.



public boolean registerTeamtoSystem( TeamRegisterBean registerTeam) throws SQLException
{
System.out.println("got to team register statement");
PreparedStatement statement = null;
boolean result = false;

try
{
statement = _connection.prepareStatement("UPDATE users SET (option1,option2,option3,option4,option5,option6,option7,option8,option9,option10,option11) VALUES (?,?,?,?,?,?,?,?,?,?,?) WHERE userName= 'styx' ");
statement.setString(1, registerTeam.getGoalkeeper());
statement.setString(2, registerTeam.getRightBack());
statement.setString(3, registerTeam.getLeftBack());
statement.setString(4, registerTeam.getCentreBack1());
statement.setString(5, registerTeam.getCentreBack2());
statement.setString(6, registerTeam.getWingerLeft());
statement.setString(7, registerTeam.getCentreMid1());
statement.setString(8, registerTeam.getCentreMid2());
statement.setString(9, registerTeam.getWingerLeft());
statement.setString(10, registerTeam.getStriker1());
statement.setString(11, registerTeam.getStriker2());



result = statement.execute();
}
catch (SQLException unexpected)
{
throw new SQLException(unexpected.getMessage());
}
finally
{
safeClose(statement);
}
return result;
}

 

[SQLDenis]

your stetement should be something like this

statement = _connection.prepareStatement("UPDATE users SET option1=?,option2=?,option3=?,option4=?,option5=?,option6=?,option7=?,option8=?,option9=?,option10=?,option11=?
WHERE userName= 'styx' ");

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ilyad]

You're mixing UPDATE-SQL syntax and INSERT-SQL syntax.

update table set field1=value1, field2 = value2, etc. where MyCondition

insert into table (field1, field2,...) values (val1, val2, etc.)

insert ito table field1, field2, ... select field1, field2 from table2 where myCond

 

[ca8msm]

You haven't amended the query as Alex suggested. Re-read his example and see how he has set each field as opposed to how you have set them...


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[styx218]

Phew!!!!

My goodness im glad i got that sorted. Thank you all for your amazing (and swift) response!!