• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Can't Update Record ?

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 !.
 
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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'm just a poor boy, I need no sympathy, because I'm easy come, easy go, little high, little low, little ad
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic