Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Updating Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating Problem" Watch "Updating Problem" New topic
Author

Updating Problem

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Hello Ranchers....
I've a problem with updating the table..
the code here i've give is correct i think..

please check it....

what i want to do is
i want to update the datas in the data field...

the data field is like this!!

name pass1 pass2 addr email phone

now i want to update name.. addr email and phone where the name = ?

how can i .. please check the code for me!!


code is here!!


String dbURL = "jdbc dbc:li";

Connection dbCon;

dbCon = DriverManager.getConnection(dbURL);//,n,p);

PreparedStatement ps = dbCon.prepareStatement("UPDATE userde set name=?,add=?,email=?,phone=? where name=?");

ps.setString(1, name);
//ps.setString(2, pass1);
//ps.setString(3, pass2);
ps.setString(4, add);
ps.setString(5, email);
ps.setString(6, phone);

ps.setString(5,name);

ps.executeUpdate();


it is not updating...
why.. please help me!!!


Akin Demir
Greenhorn

Joined: Jul 08, 2005
Posts: 9
In your query
UPDATE userde set name=?,add=?,email=?,phone=? where name=?
you have 5 fields(?)
but
in the code
ps.setString(1, name);
//ps.setString(2, pass1);
//ps.setString(3, pass2);
ps.setString(4, add);
ps.setString(5, email);
ps.setString(6, phone);
you try to set 6 fields.

try this

ps.setString(1, name);
//ps.setString(2, pass1);
//ps.setString(3, pass2);
ps.setString(2, add);
ps.setString(3, email);
ps.setString(4, phone);

And check type compatibility. Are all of the columns in the table string?


-Akin
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Sebo!!!

Now the problem its showing is this

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect


what can i do now..
Akin Demir
Greenhorn

Joined: Jul 08, 2005
Posts: 9
Your table is
name pass1 pass2 addr email phone

but in the query

UPDATE userde set name=?,add=?,email=?,phone=? where name=?

change add with addr
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Sebo it was not addr..

it is add only...

my table is

name pass1 pass2 add email phone

now when i update
i want the query like this

update userde set name='rony',add='India',email='rony@rony.co',phone='2333' where name='rony';

for this i'm using the prepared statement..
update userde set name=?,add=?,email=?,ph=? where name=?;

when i set the string for each ?
it is
ps.setString(1,name);
ps.setString(4,add);
ps.setString(5,email);
ps.setString(6,phone);

ps.setString(1,name);

ps.executeUpdate();

this is what i've given..

but this is showing the error
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

please fix my problem.. and help me..
Akin Demir
Greenhorn

Joined: Jul 08, 2005
Posts: 9
try this
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Sebo!!!
After giving this.. this is not showing errors...
and showing the message "updated successfully!!" which i've given in the code.

but it is not really updated in the database..
is there any problem with my query..
please check it out.. sebo..

PreparedStatement ps = dbCon.prepareStatement
("UPDATE userde set name=?,add=?,email=?,phone=? where name=?");

i think this is ok.. but this is not updating actully..

please help me!!


Akin Demir
Greenhorn

Joined: Jul 08, 2005
Posts: 9
The query is fine but in your preparedstatement you give the name parameter same as the one you used in the where clause. This has no effect. The name field is not updated.

Also you might try

Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Akin
it is not updating the database
i think there is some problem with the.. query
do u find any problem with the query?

but still it is not updating!!

please help me!!
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

1. What exception are you getting.
2. Post the stack trace of exception
3. Which database are you using.
4. You have a column named as "add", just a suggestion to rename this column because add is a key word in many database. I wonder that your database didn't stop you at the time of table creation


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

It's possible that the UPDATE statement updates zero rows because your "name" variable doesn't correspond to anything in the table. The ps.executeUpdate() method returns an int value that you can use to see how many rows were updated.
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Sebo...
Here using the table field "add" i can insert..create.. and search..
but now i'm not able to update..
this may the problem with.. the query.. is that so?

i've given ps.executeUpdate and also.. dbCon.commit()

i'm using the database now is MS Access..
but this is not updating...

can u please send me.. the query for updating the table in the database

please
Amitav Anand
Ranch Hand

Joined: Sep 22, 2003
Posts: 46
Hello Arvind,

I think your code is absolutely fine...

U just need to make sure that the value of the name field(i.e. "rony") that u have specified in the where clause should be present in the table.

And also if the executeUpdate() method is returning an integer value other than Zero, that means the table is updated successfully.



Cheers!!!

Amitav
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
Hello Ranchers..
I've printed the executeUpdate()...
it is returning .. the value.. 0..

it seems.. it is not updating..the table.. right..

but now .. what i want to do..

my query is correct..
my database is up...
my webserver (tomcat is ok)
it is showing.. the message "Updated Successfully"

but the thing is returning that value 0
and not updating...

please help me.. to update the data..
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 263
hello ranchers..
please help me..

this is my problem


PreparedStatement ps = dbCon.prepareStatement("UPDATE userde SET name =?,add=?,email=?,phone=? where name=?");

ps.setString(1, name);
ps.setString(2, add);
ps.setString(3, email);
ps.setString(4, phone);
ps.setString(1,name);

int x = ps.executeUpdate();
out.println(x);

please help me...

thank u
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
You are not doing a commit, so I presume that auto-commit mode has been enabled (the default).

Also, close your Statement and Connection objects and see if that makes a difference.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Gaurav Manral
Greenhorn

Joined: Jun 29, 2012
Posts: 17

i had the the same problem earlier...
but somehow the exception has been changed to---

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


my code is---



Please help...


Forgive your enemies, but never forget their names.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

The UPDATE statement doesn't work like that. You have to specify the columns you want to set the values for:
Of course you need to add the actual column names.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Gaurav Manral
Greenhorn

Joined: Jun 29, 2012
Posts: 17

OK ROB...

now i changed my code to--

but the error is same...

sailaja usha
Greenhorn

Joined: Jun 23, 2012
Posts: 13

is it required to enclose the string contents in single quotes..?


regards
Sailaja
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
Gaurav Manral wrote:

but the error is same...

It should be
update user_registration set firstname=?,lastname=?,username=?,password=?,confirm=?,date=?,month=?,year=?,gender=?,mobile=?,email=?,image=?,status=? where username=?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

Correct. Gaurav, I have no idea why you put that "values" in there. Where did you read about that?
Gaurav Manral
Greenhorn

Joined: Jun 29, 2012
Posts: 17


I have tried with & without "VALUES". there is no effect.
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
>I have tried with & without "VALUES". there is no effect.
Checked. You can try [date]=? since ODBC MS Access driver maybe fail to support such a sql reserved word. If you still fail, you can try commercial MS Access driver, which supports both of "date=?" and "[date]=?".
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Updating Problem