Home.... | log in.... | Pub my question! | Sign-up....
Java-index >> Database Connectivity >> Java Database Connectivity (JDBC)

having problem to update database

i have a database which contains schedule table with employeeno & date as the primary key with the other column names as :9 AM, 10 AM etc....

when a record exist with the date and employeeno in the table, the save buton is suppose to update the schedule table..but instead it uses the insert statement. can anyone correct me if there's soemthing wrong with my coding? or does UPDATE statement only works when we have to update all the columns.. cos mine only updates like 1 column

here's the error message

java.sql.SQLException: General error

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)

at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)

at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)

at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)

at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:288)

at Schedule.saveRecord(Schedule.java:656)

at Schedule.verifyField(Schedule.java:570)

at Schedule.access$1700(Schedule.java:25)

at Schedule$ButtonFunction.actionPerformed(Schedule.java:850)

at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)

at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)

at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)

at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)

at java.awt.Component.processMouseEvent(Component.java:5488)

at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)

at java.awt.Component.processEvent(Component.java:5253)

at java.awt.Container.processEvent(Container.java:1966)

at java.awt.Component.dispatchEventImpl(Component.java:3955)

at java.awt.Container.dispatchEventImpl(Container.java:2024)

at java.awt.Component.dispatchEvent(Component.java:3803)

at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)

at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)

at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)

at java.awt.Container.dispatchEventImpl(Container.java:2010)

at java.awt.Window.dispatchEventImpl(Window.java:1778)

at java.awt.Component.dispatchEvent(Component.java:3803)

at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)

at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)

at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

//i have printout the statement used:

INSERT INTO Schedule (EmployeeNo, [Date], [8 AM]) VALUES ('E000008',#21 January 2007#,'A000002');

SELECT EmployeeNo, Date FROM Schedule WHERE EmployeeNo='E000008' AND Date=#21 January 2007#

//update statement:

null

and here's the code

//method: verify fields

privatevoid verifyField(){

if(employeeNoField.getText().equals("" ) || scheduleDateField.getText().equals("" ) ||

timeComboBox.getSelectedObjects().equals("Select") || appointCustNoField.getText().equals("")){

JOptionPane.showMessageDialog(null,

"Please check if compulsory fields have been filled",

"Unable to save record",

JOptionPane.ERROR_MESSAGE);

}

else saveRecord();

}

//***************************************************************************************

//method: save record

privatevoid saveRecord(){

// Append the text from fields to String

String eNo = employeeNoField.getText();

String sdate = scheduleDateField.getText();

String time = (String)timeComboBox.getSelectedItem();

String appcust = appointCustNoField.getText();

String empno =null;

String schedate =null;

String check1=null;

String check2=null;

String check3=null;

//Check: if data exist in database using the employeeNo

try{

if ( !employeeNoField.getText().equals("" ) && !scheduleDateField.getText().equals("" )){

statement = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

String query ="SELECT EmployeeNo, Date FROM Schedule " +

"WHERE EmployeeNo='" + employeeNoField.getText() +

"' AND Date=#" + scheduleDateField.getText() +"#";

check2=query;

ResultSet rs = statement.executeQuery( query );

rs.next();

//append the employeeNo to a string

empno = rs.getString("EmployeeNo" );

java.util.Date date = rs.getDate("Date");

schedate = formatter.format(date);

statement.close();

}

else empno =""; schedate="";

}

catch ( SQLException sqlex ){

sqlex.printStackTrace();

System.out.println(check2);

}

// end of check

//Update data if employeeNo exist in database

if ( employeeNoField.getText().equals( empno ) && scheduleDateField.getText().equals(schedate)){

try{

statement = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

String query ="UPDATE Schedule SET [" + time +"] = '" + appcust +

"' WHERE EmployeeNo='" + empno +

"' AND Date=#" + schedate +"#";

check3=query;

int result = statement.executeUpdate( query );

if ( result == 1 ){

JOptionPane.showMessageDialog(null,"Record saved");

statement.close();

saveButton.setEnabled(false);

}

else{

JOptionPane.showMessageDialog(null,

"Please check if compulsory fields have been filled",

"Unable to save record",

JOptionPane.ERROR_MESSAGE);

}

}

catch ( SQLException sqlex ){

sqlex.printStackTrace();

JOptionPane.showMessageDialog(null,

"Failed\n" + sqlex.toString(),

"Error Message",

JOptionPane.ERROR_MESSAGE);

System.out.println(check3);

}

catch (Exception ex ){

ex.printStackTrace();

JOptionPane.showMessageDialog(null,

"Failed\n" + ex.toString(),

"Error Message",

JOptionPane.ERROR_MESSAGE);

}

}

//Insert data into table

else{

try{

statement = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

String insert ="INSERT INTO Schedule (EmployeeNo, [Date], ["+time+"]) VALUES " +"("+"'"+eNo+"'"+"," +"#"+sdate+"#"+"," +"'"+appcust+"'"+")" +";";

check1=insert;

int result = statement.executeUpdate( insert );

if ( result == 1 ){

JOptionPane.showMessageDialog(null,"Record saved");

statement.close();

saveButton.setEnabled(false);/* disable save button */

}

else{

JOptionPane.showMessageDialog(null,

"Please check if compulsory fields have been filled",

"Unable to save record",

JOptionPane.ERROR_MESSAGE);

saveButton.setEnabled(true);/* enable save button */

}

}

catch ( SQLException sqlex ){

sqlex.printStackTrace();

JOptionPane.showMessageDialog(null,

"Failed\n" + sqlex.toString(),

"Error Message",

JOptionPane.ERROR_MESSAGE);

System.out.println(check1);

System.out.println(check2);

System.out.println(check3);

}

catch ( Exception ex ){

ex.printStackTrace();

JOptionPane.showMessageDialog(null,

"Failed\n" + ex.toString(),

"Error Message",

JOptionPane.ERROR_MESSAGE);

}

}

}

[11639 byte] By [Buma] at [2007-11-26 16:02:34]
«« Re: adding new components to designed JPanel
»» How to refresh my Window
# 1

It's not the verify() method, but the save() method that is failing. It is hard to read with all the escape characters you have in your SQL. Consider refactoring this to use a PreparedStatement. My guess is the error is in your SQL string or the escaping of it. PreparedStatement offers many benefits over statement, including avoiding the need to escape your parameters. Also, please highlight or let us know the line number that is failing (look at the stack trace).

- Saish

Saisha at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 2

opps i'm so sorry..

the error is at

at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:288)

at Schedule.saveRecord(Schedule.java:658)

int result = statement.executeUpdate( insert ); //<-the statement

this error occur as the primary key existed in the ms access table but i'm not sure why the save function use the insert statement instead of update statement.

in the save record() method,

first i get the employeeno and date from schedule table to check if the record exist

String query = "SELECT EmployeeNo, Date FROM Schedule " + "WHERE EmployeeNo='" + employeeNoField.getText() + "' AND Date=#" + scheduleDateField.getText() + "#";

if exist { empno = rs.getString( "EmployeeNo" );

java.util.Date date = rs.getDate("Date");

}

else empno = ""; schedate="";

then if they do exist i'll update the table by

if ( employeeNoField.getText().equals( empno ) && scheduleDateField.getText().equals(schedate)) {

String query = "UPDATE Schedule SET [" + time + "] = '" + appcust +

"' WHERE EmployeeNo='" + empno +

"' AND Date=#" + schedate + "#";

else

String insert = "INSERT INTO Schedule (EmployeeNo, [Date], ["+time+"]) VALUES " + "("+"'"+eNo+"'"+"," +"#"+sdate+"#"+"," +"'"+appcust+"'"+")" +";";

Buma at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 3
phew.. i've just manage to solve this prob..just incase anyone err will like to know...i've change altered the schedate code a little and it works now..schedate = formatter.format(date).toString();
Buma at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 4

I'd recommend that you start by switching from Statement to PreparedStatement and let the JDBC driver bind variables like String and Date.

You're writing SQL that's very specific to Access. This might be okay if you never, ever plan to use anything else, but just be warned that your code isn't ANSI SQL.

I think anybody who names a column "Date" is asking for trouble. It's a SQL type AND it's not very informative. Is that the employee's birth date? Employment date? A more descriptive name would improve your schema.

%

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 5

"this error occur as the primary key existed in the ms access table"

You're not using an auto increment column for your primary key?

"but i'm not sure why the save function use the insert statement instead of update statement."

Must be a coding error or bad assumption on your part. You think one part of the code should be called, but it's not. Check your assumptions.

%

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 6

> It's not the verify() method, but the save() method

> that is failing. It is hard to read with all the

> escape characters you have in your SQL. Consider

> refactoring this to use a PreparedStatement. My

> guess is the error is in your SQL string or the

> escaping of it. PreparedStatement offers many

> benefits over statement, including avoiding the need

> to escape your parameters. Also, please highlight or

> let us know the line number that is failing (look at

> the stack trace).

>

> - Saish

Saish, what a pleasure it is to see your name here. I haven't seen you around for a while. How's work? How's life?

%

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 7
I'd also recommend to the OP that s/he move the database code out of this class and into a separate persistence class that can be tested independent of the UI. There should never be any Swing code in a method that works with a database.%
duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 8
hi duffymo .... thanks for ur recommendation..but sigh.. i doubt i have the time to move all database coding to another class..my project submission date is due soon 6th feb....... just hope that i can complete it in time..thanks again....sigh...
Buma at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 9

> hi duffymo .... thanks for ur recommendation..but

> sigh.. i doubt i have the time to move all database

> coding to another class..

That's too bad.

> my project submission date is due soon 6th feb.......

> just hope that i can complete it in time..

That's a long time from now. You'd be able to move it to another class in that time, if you worked at it.

> thanks again....sigh...

Good luck.

%

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 10

Like so many students (and even experienced programmers), you're writing with Java, an object-oriented language, but you're thinking procedurally. I don't see ANY real objects in your solution.

Why not start your solution with objects first, then add on persistence later?

It's the wrong way to go about solving problems with Java.

%

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 11
thanks a lot for your luck!!! really need lots of luck...
Buma at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 12

> Saish, what a pleasure it is to see your name here. I haven't seen you around for a while. How's work? How's life?

Duffy! Great to be back and to see your posts as well. Congrats on passing 20,000. :^)

I have been off in the world of WS (WSDL, WS-security, WS-transactions, etc.) for the past few months. I've had to dust off my Java. But now that things have calmed down a little bit, it's good to be back at the fora.

- Saish

Saisha at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 13

> Duffy! Great to be back and to see your posts as

> well. Congrats on passing 20,000. :^)

Too much time on my hands, I guess. 8)

> I have been off in the world of WS (WSDL,

> WS-security, WS-transactions, etc.) for the past few

> months.

I'd be interested in hearing what you think. I'm a bit gun-shy about the complexity of it all. I'm leaning more towards REST. What's your take on it?

> I've had to dust off my Java.

It'll be as good as ever.

> But now that

> things have calmed down a little bit, it's good to be

> back at the fora.

>

> - Saish

I'm glad to have you back. Sincerely, %

duffymoa at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
# 14

> > Duffy! Great to be back and to see your posts as

> > well. Congrats on passing 20,000. :^)

>

> Too much time on my hands, I guess. 8)

>

I had that problem before I started school last fall. Now, I never worry about having too much time! :^)

>

> > I have been off in the world of WS (WSDL,

> > WS-security, WS-transactions, etc.) for the past

> few

> > months.

>

> I'd be interested in hearing what you think. I'm a

> bit gun-shy about the complexity of it all. I'm

> leaning more towards REST. What's your take on it?

>

I started a thread in the OO Patterns forum so this thread would not get too hijacked. http://forum.java.sun.com/thread.jspa?threadID=5130083

> > I've had to dust off my Java.

>

> It'll be as good as ever.

>

> > But now that

> > things have calmed down a little bit, it's good to

> be

> > back at the fora.

> >

> > - Saish

>

> I'm glad to have you back. Sincerely, %

Likewise amigo. Good to be back, and we can stay in touch easier now. :^)

- Saish

Saisha at 2007-7-8 22:24:22 > top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...
Java Database Connectivity (JDBC)

Database Connectivity New Topic

Database Connectivity Hot Topic

Database Connectivity

All Classified