This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Can't Update Record ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can Watch "Can New topic
Author

Can't Update Record ?

Rosewell
Greenhorn

Joined: Jul 22, 2002
Posts: 8
I have enconterd a problem while trying to update my sample data base program doesnt update an existing record. Can anyone help me pls!, thanks !
/*Program Made By: Jan - Michael Soan
4-D Project Study T-F 3:00 - 4:30 */
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.text.*;
public class Database extends JFrame {
private String url;
private Connection connect;
private Statement statement;
public Database()
{
super( "Timeport . . ." );
try {
url = "jdbc dbc:Employees";
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
connect = DriverManager.getConnection( url );
JOptionPane.showMessageDialog(null,"Connection Successful !",
"Database . . .",JOptionPane.PLAIN_MESSAGE);
}
catch ( ClassNotFoundException cnfex ) {
cnfex.printStackTrace();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
catch ( Exception ex ) {
ex.printStackTrace();
}
Container c = getContentPane();
JPanel p1 = new JPanel();
JPanel p2 = new JPanel();
JPanel p3 = new JPanel();
JPanel p4 = new JPanel();
JPanel p5 = new JPanel();
JPanel p6 = new JPanel();
JPanel p7 = new JPanel();
// Sub Panel's s1
JPanel s1 = new JPanel();
JPanel s2 = new JPanel();
JPanel s3 = new JPanel();
JPanel s4 = new JPanel();
JPanel s5 = new JPanel();
JPanel s6 = new JPanel();
JLabel l1 = new JLabel("Lastname :");
JLabel l2 = new JLabel("Firstname :");
JLabel l3 = new JLabel("Employee No :");
JLabel l4 = new JLabel("SSS Number :");
final JTextField t1 = new JTextField(20);
final JTextField t2 = new JTextField(20);
final JTextField t3 = new JTextField(20);
final JTextField t4 = new JTextField(20);
p1.add(l1); p1.add(t1);
p1.add(l2); p1.add(t2);
p1.add(l3); p1.add(t3);
p1.add(l4); p1.add(t4);
p1.setLayout( new GridLayout( 4, 2 ) );
p1.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
s1.add(p1);
c.add(s1, BorderLayout.NORTH);
JTabbedPane x1 = new JTabbedPane();
String name1 = "Identification";
JLabel l5 = new JLabel("Gender :");
JLabel l6 = new JLabel("Status :");
JLabel l7 = new JLabel("Birthdate :");
JLabel l8 = new JLabel("Address :");
JLabel l9 = new JLabel("City :");
JLabel l10 = new JLabel("Province :");
JLabel l11 = new JLabel("Country :");
final JTextField b1 = new JTextField(20);
s4.add(b1);

s4.setLayout( new GridLayout( 1, 2 ) );
final JTextField t5 = new JTextField(20);
final JTextField t6 = new JTextField(20);
final JTextField t7 = new JTextField(20);
final JTextField t8 = new JTextField(20);
final JTextField t9 = new JTextField(20);
final JTextField t10 = new JTextField(20);
p3.add(l5); p3.add(s4);
p3.add(l6); p3.add(t5);
p3.add(l7); p3.add(t6);
p3.add(l8); p3.add(t7);
p3.add(l9); p3.add(t8);
p3.add(l10); p3.add(t9);
p3.add(l11); p3.add(t10);
p3.setLayout( new GridLayout( 9, 2 ) );
p3.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
x1.add(name1, p3);
// End of 1st x1
String name2 = "Contacs";

JLabel l12 = new JLabel("Telephone :");
JLabel l13 = new JLabel("E-Mail :");
JLabel l14 = new JLabel("Spouse :");
JLabel l15 = new JLabel("Contact No :");
final JTextField t11 = new JTextField(20);
final JTextField t12 = new JTextField(20);
final JTextField t13 = new JTextField(20);
final JTextField t14 = new JTextField(20);
s2.add(l12); s2.add(t11);
s2.add(l13); s2.add(t12);
s2.add(l14); s2.add(t13);
s2.add(l15); s2.add(t14);
s2.setLayout( new GridLayout( 4, 2 ) );
s2.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
JLabel l16 = new JLabel("Special Contact :");
final JTextField t15 = new JTextField(20);
s3.add(l16); s3.add(t15);
s3.setLayout( new GridLayout( 4, 2 ) );
s3.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
p4.add(s2); p4.add(s3);
p4.setLayout( new GridLayout( 2, 1 ) );
x1.add(name2, p4);
// End of 2nd x1
String name3 = "Employment";
JLabel l17 = new JLabel("Job Title :");
JLabel l18 = new JLabel("Department :");
JLabel l19 = new JLabel("Date hired :");
JLabel l20 = new JLabel("Institution :");
JLabel l21 = new JLabel("Course Attiend :");
JLabel l22 = new JLabel("Awards / GPA :");
final JTextField t16 = new JTextField(20);
final JTextField t17 = new JTextField(20);
final JTextField t18 = new JTextField(20);
final JTextField t19 = new JTextField(20);
final JTextField t20 = new JTextField(20);
final JTextField t21 = new JTextField(20);
p5.add(l17); p5.add(t16);
p5.add(l18); p5.add(t17);
p5.add(l19); p5.add(t18);
p5.add(l20); p5.add(t19);
p5.add(l21); p5.add(t20);
p5.add(l22); p5.add(t21);
p5.setLayout( new GridLayout( 9, 2 ) );
p5.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
x1.add(name3, p5);
// End of 3rd x1
String name4 = "Deductions";
JLabel l23 = new JLabel("Tax Deduction :");
JLabel l24 = new JLabel("SSS Deduction :");
JLabel l25 = new JLabel("Loans :");
JLabel l26 = new JLabel("Medicare :");
JLabel l27 = new JLabel("Others :");
final JTextField t22 = new JTextField(20);
final JTextField t23 = new JTextField(20);
final JTextField t24 = new JTextField(20);
final JTextField t25 = new JTextField(20);
final JTextField t26 = new JTextField(20);
p6.add(l23); p6.add(t22);
p6.add(l24); p6.add(t23);
p6.add(l25); p6.add(t24);
p6.add(l26); p6.add(t25);
p6.add(l27); p6.add(t26);
p6.setLayout( new GridLayout( 9, 2 ) );
p6.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
x1.add(name4, p6);
// End of 4th x1
String name5 = "Incomes";
JLabel l28 = new JLabel("Basic Pay :");
JLabel l29 = new JLabel("Overtime Pay :");
JLabel l30 = new JLabel("Holiday Pay :");
JLabel l31 = new JLabel("Others :");
JLabel l32 = new JLabel("Net Pay :");
JLabel l33 = new JLabel();
JLabel l34 = new JLabel();
JLabel l35 = new JLabel();
final JTextField t27 = new JTextField(20);
final JTextField t28 = new JTextField(20);
final JTextField t29 = new JTextField(20);
final JTextField t30 = new JTextField(20);
final JTextField t31 = new JTextField(20);
JButton com = new JButton("Compute");
s5.add(l28); s5.add(t27);
s5.add(l29); s5.add(t28);
s5.add(l30); s5.add(t29);
s5.add(l31); s5.add(t30);
s5.setLayout( new GridLayout( 4, 2 ) );
s5.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
s6.add(l32); s6.add(t31);
s6.add(l33); s6.add(com);
s6.add(l34); s6.add(l35);
s6.setLayout( new GridLayout( 4, 2 ) );
s6.setBorder(new javax.swing.border.
TitledBorder(new javax.swing.border.EtchedBorder()));
p7.add(s5); p7.add(s6);
p7.setLayout( new GridLayout( 2, 1 ) );

x1.add(name5, p7);
// End of 5th x1
JPanel f1 = new JPanel();
JLabel c1 = new JLabel();
JLabel c2 = new JLabel();
JButton y1 = new JButton("Add");
JButton y2 = new JButton("Find");
JButton y3 = new JButton("Update");
JButton y4 = new JButton("Cancel");
f1.add(c1); f1.add(y1);
f1.add(y2); f1.add(y3);
f1.add(y4); f1.add(c2);
f1.setLayout( new GridLayout() );
c.add(x1, BorderLayout.CENTER);
c.add(f1, BorderLayout.SOUTH);
Dimension sd = java.awt.Toolkit.getDefaultToolkit().getScreenSize();
setLocation(sd.width / 2 - 480 / 2,
sd.height / 2 - 400 / 2);
setResizable(false);
setSize(480, 400);
show();
com.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)
{
float a1 = Float.parseFloat(t22.getText());
float a2 = Float.parseFloat(t23.getText());
float a3 = Float.parseFloat(t24.getText());
float a4 = Float.parseFloat(t25.getText());
float a5 = Float.parseFloat(t26.getText());
float a6 = Float.parseFloat(t27.getText());
float a7 = Float.parseFloat(t28.getText());
float a8 = Float.parseFloat(t29.getText());
float a9 = Float.parseFloat(t30.getText());
float sal;
float ded;
float sum;

sal = a6 + a7 + a8 + a9;
ded = a1 + a2 + a3 + a4 + a5;
sum = sal - ded;
DecimalFormat change = new DecimalFormat("0.00");
String ans = change.format(sum);
t31.setText("P "+ans);
}
});
y1.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)
{
try {
Statement statement = connect.createStatement();
String query = "INSERT INTO Records (" +
"Lastname, Firstname, EmpNo, SSSNo, Gender, Status, " +
"Birthdate, Address, City, Province, Country, Telephone, Email, Spouse, Contact, " +
"Special, JobTitle, Department, DateHired, Institution, Course, " +
"AwardsGPA, Tax, SSS, Loan, Medicare, OthersDed, BasicPay, OvertimePay, " +
"HolidayPay, OthersSal, NetPay " +
") VALUES ('" +
t1.getText() + "', '" + t2.getText() + "', '" + t3.getText() + "', '" +
t4.getText() + "', '" + b1.getText() + "', '" + t5.getText() + "', '" +
t6.getText() + "', '" + t7.getText() + "', '" + t8.getText() + "', '" +
t9.getText() + "', '" + t10.getText() + "', '" + t11.getText() + "', '" +
t12.getText() + "', '" + t13.getText() + "', '" + t14.getText() + "', '" +
t15.getText() + "', '" + t16.getText() + "', '" + t17.getText() + "', '" +
t18.getText() + "', '" + t19.getText() + "', '" + t20.getText() + "', '" +
t21.getText() + "', '" + t22.getText() + "', '" + t23.getText() + "', '" +
t24.getText() + "', '" + t25.getText() + "', '" + t26.getText() + "', '" +
t27.getText() + "', '" + t28.getText() + "', '" + t29.getText() + "', '" +
t30.getText() + "', '" + t31.getText() + "')";

int result = statement.executeUpdate( query );

statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println(sqlex.toString());
}
}
});
y2.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)
{
if( t1.getText().equals("") ) {
JOptionPane.showMessageDialog(null,"Enter Lastname & Press Find !",
"Database . . .",JOptionPane.WARNING_MESSAGE);
t1.setText( "" ); t2.setText( "" );
t3.setText( "" ); t4.setText( "" );
b1.setText( "" ); t5.setText( "" );
t6.setText( "" ); t7.setText( "" );
t8.setText( "" ); t9.setText( "" );
t10.setText( "" ); t11.setText( "" );
t12.setText( "" ); t13.setText( "" );
t14.setText( "" ); t15.setText( "" );
t16.setText( "" ); t17.setText( "" );
t18.setText( "" ); t19.setText( "" );
t20.setText( "" ); t21.setText( "" );
t22.setText( "" ); t23.setText( "" );
t24.setText( "" ); t25.setText( "" );
t26.setText( "" ); t27.setText( "" );
t28.setText( "" ); t29.setText( "" );
t30.setText( "" ); t31.setText( "" );
}
else
try {
Statement statement = connect.createStatement();
String query = "SELECT * FROM Records " +
"WHERE Lastname = '" + t1.getText() + "'";

ResultSet rs = statement.executeQuery( query );
display( rs );
statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
public void display( ResultSet rs )
{
try {
rs.next();
t1.setText( rs.getString( 1 ) );
t2.setText( rs.getString( 2 ) );
t3.setText( rs.getString( 3 ) );
t4.setText( rs.getString( 4 ) );
b1.setText( rs.getString( 5 ) );
t5.setText( rs.getString( 6 ) );
t6.setText( rs.getString( 7 ) );
t7.setText( rs.getString( 8 ) );
t8.setText( rs.getString( 9 ) );
t9.setText( rs.getString( 10 ) );
t10.setText( rs.getString( 11 ) );
t11.setText( rs.getString( 12 ) );
t12.setText( rs.getString( 13 ) );
t13.setText( rs.getString( 14 ) );
t14.setText( rs.getString( 15 ) );
t15.setText( rs.getString( 16 ) );
t16.setText( rs.getString( 17 ) );
t17.setText( rs.getString( 18 ) );
t18.setText( rs.getString( 19 ) );
t19.setText( rs.getString( 20 ) );
t20.setText( rs.getString( 21 ) );
t21.setText( rs.getString( 22 ) );
t22.setText( rs.getString( 23 ) );
t23.setText( rs.getString( 24 ) );
t24.setText( rs.getString( 25 ) );
t25.setText( rs.getString( 26 ) );
t26.setText( rs.getString( 27 ) );
t27.setText( rs.getString( 28 ) );
t28.setText( rs.getString( 29 ) );
t29.setText( rs.getString( 30 ) );
t30.setText( rs.getString( 31 ) );
t31.setText( rs.getString( 32 ) );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println( sqlex.toString() );
}
}
});
y3.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)
{
try {

Statement statement = connect.createStatement();
// - - - - - - This is the Update Problem ??? - - - - - - //
if ( t1.getText().equals( "" ) ) {
String query = "UPDATE Records SET " +
" Firstname='" + t2.getText() +
"', EmpNo='" + t3.getText() +
"', SSSNo='" + t4.getText() +
"', Gender='" + b1.getText() +
"', Status='" + t5.getText() +
"', Birthdate='" + t6.getText() +
"', Address='" + t7.getText() +
"', City='" + t8.getText() +
"', Province='" + t9.getText() +
"', Country='" + t10.getText() +
"', Telephone='" + t11.getText() +
"', Email='" + t12.getText() +
"', Spouse='" + t13.getText() +
"', Contact='" + t14.getText() +
"', Special='" + t15.getText() +
"', JobTitle='" + t16.getText() +
"', Department='" + t17.getText() +
"', DateHired='" + t18.getText() +
"', Institution='" + t19.getText() +
"', Course='" + t20.getText() +
"', AwardsGPA='" + t21.getText() +
"', Tax='" + t22.getText() +
"', SSS='" + t23.getText() +
"', Loan='" + t24.getText() +
"', Medicare='" + t25.getText() +
"', OthersDed='" + t26.getText() +
"', BasicPay='" + t27.getText() +
"', OvertimePay='" + t28.getText() +
"', HolidayPay='" + t29.getText() +
"', OthersSal='" + t30.getText() +
"', NetPay='" + t31.getText() +
"' WHERE Lastname=" + t1.getText();

int result = statement.executeUpdate( query );
if ( result == 1 )
System.out.println( "\nUpdate successful\n" );

else {
System.out.println( "\nUpdate failed\n" );
t1.setText( "" ); t2.setText( "" );
t3.setText( "" ); t4.setText( "" );
b1.setText( "" ); t5.setText( "" );
t6.setText( "" ); t7.setText( "" );
t8.setText( "" ); t9.setText( "" );
t10.setText( "" ); t11.setText( "" );
t12.setText( "" ); t13.setText( "" );
t14.setText( "" ); t15.setText( "" );
t16.setText( "" ); t17.setText( "" );
t18.setText( "" ); t19.setText( "" );
t20.setText( "" ); t21.setText( "" );
t22.setText( "" ); t23.setText( "" );
t24.setText( "" ); t25.setText( "" );
t26.setText( "" ); t27.setText( "" );
t28.setText( "" ); t29.setText( "" );
t30.setText( "" ); t31.setText( "" );
}
statement.close();
}
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println( sqlex.toString() );
}
}
});
y4.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent e)
{
t1.setText( "" ); t2.setText( "" );
t3.setText( "" ); t4.setText( "" );
b1.setText( "" ); t5.setText( "" );
t6.setText( "" ); t7.setText( "" );
t8.setText( "" ); t9.setText( "" );
t10.setText( "" ); t11.setText( "" );
t12.setText( "" ); t13.setText( "" );
t14.setText( "" ); t15.setText( "" );
t16.setText( "" ); t17.setText( "" );
t18.setText( "" ); t19.setText( "" );
t20.setText( "" ); t21.setText( "" );
t22.setText( "" ); t23.setText( "" );
t24.setText( "" ); t25.setText( "" );
t26.setText( "" ); t27.setText( "" );
t28.setText( "" ); t29.setText( "" );
t30.setText( "" ); t31.setText( "" );
}
});
}
public static void main( String args[] )
{
Database app = new Database();
app.addWindowListener(
new WindowAdapter() {
public void windowClosing( WindowEvent e )
{
System.exit( 0 );
}
}
);
}
}
Help Pls !.
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Well, I'm not sure exactly what you are trying to do, but this code:

Looks suspicious because it results in your WHERE clause being "WHERE Lastname = ''". I don't think that's your intent?
Maybe it should be like:

If that's not the answer, then you may need to post more of a description of what you're trying to do.
Also, whenever you're having trouble with a particular SQL statement string, always print it out (e.g. System.out.println) right before you execute it. Then you can at least verify that the values set in the string are correct.
[ February 02, 2003: Message edited by: Blake Minghelli ]

Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"Rosewell",
Your name does not comply with the JavaaRanch naming standard. http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name.
Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I think it's more likely to be the common JDBC-ODBC insert/update problem. You could help us out by only posting the relevant code rather than everything...
To test it out, try doing a dummy read after an update. That is, do a select on the same connection that returns a single row. You don't have to read this data.
It appears that queries get cached by the driver and so you have to do 'one more' to get the previous one commited.
Also, it doesn't appear that you close the database connection. This is a bad practice and may be contributing to the error you are seeing. It is a practice that will certainly cause you other problems in the future.
I hope this solves your problem.
Dave
Laura Brown
Greenhorn

Joined: Feb 04, 2003
Posts: 2
What version of Access are you using? What version of Windows?
I have been struggling with problems updating existing records as well. New records were fine, just existing ones were not being updated even though the jdbc calls returned like they were updated. I'm stuck using Access, so I couldn't get around it! I tried the dummy query to no avail. Due to major time crunch on my project I resorted to the RmiJdbc server "adapter" to bypass this problem so I could continue development at least. Finally today I fixed the update problem.
From what I have found, there is a bug in the odbc driver that comes with windows 2000 Service Pack 2. I don't know if this applies to any other setup but I upgraded to Service Pack 3 and while I still have to force the update with a dummy query, it does work. You can download the latest MDAC package (free on microsoft at the URL below) to get the driver from there although I don't know if this will cause other problems (highly likely considering it's Microsoft).
http://www.microsoft.com/data/download.htm
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Can't Update Record ?
 
Similar Threads
Exception in thread "main" java.lang.IllegalArgumentException: illegal component position
Adding Record Problem in JDBC ?
comparison methods
Can't Update Record ?
Problem in viewing records ?