asked on

How to Update a Record from a database?

hie, i have a very simple question, i am a java beginner, i am able to add a record to a database from my java which i am using MS Access as my database, i am able to add a record by using this code...
public void add() { // Add New Guest to Database

       String a = super.getLastName();
       String b = super.getFirstName();
       String c = address.getAddress();
       String d = address.getCity();
       String e = address.getPostCode();
       String f = address.getState();
       String g = address.getCountry();
       String h = super.getPhone();
       String i = super.getFax();
       String j = super.getRemarks();

       openConnection();

       try
           {
                 Statement statement = connection.createStatement();

                  String query = "INSERT INTO guest (guest_ID,lastname,firstname,address," +
                                      " city,postcode, state, country, phone," +
                                    " fax, remarks) VALUES ('" +
                                             a +"','" +
                                             b +"','" +
                                             c +"','" +
                                             d +"','" +
                                             e +"','" +
                                             f +"','" +
                                             g +"','" +
                                             h +"','" +
                                             i +"','" +
                                             j +"')";

                  int result = statement.executeUpdate(query);

                  if (result == 1) {
                       JOptionPane.showMessageDialog(null, "Insertion succesfull");
                 } else {
                       JOptionPane.showMessageDialog(null, "Insertion Not succesfull");
                 }

                 statement.close();
       }
       catch(SQLException sqlex) {

       }
     }

and now i want to retrieve back my record and i am searching by the guest_id, so when i want to update the cord, what should i write ?please give me some guide

it should be write as this
public void update() {

//the code
}

InNoCenT_Ch1ld

something like:

String query = "UPDATE addresses SET " +
"firstname='" + fields.first.getText() +
"', lastname='" + fields.last.getText() +
"', address='" + fields.address.getText() +
"', city='" + fields.city.getText() +
"', stateorprovince='" +
fields.state.getText() +
"', postalcode='" + fields.zip.getText() +
"', country='" + fields.country.getText() +
"', emailaddress='" +
fields.email.getText() +
"', homephone='" + fields.home.getText() +
"', faxnumber='" + fields.fax.getText() +
"' WHERE id=" + fields.id.getText();
output.append( "\nSending query: " +
connection.nativeSQL( query ) + "\n" );

int result = statement.executeUpdate( query );

if ( result == 1 )
output.append( "\nUpdate successful\n" );

ASKER CERTIFIED SOLUTION

InNoCenT_Ch1ld

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KarthikAnandan

Hi,
First you have to search whether the given guest_id is present or not. Then only u can update a record.

String a = super.getLastName();
String b = super.getFirstName();
String c = address.getAddress();
String d = address.getCity();
String e = address.getPostCode();
String f = address.getState();
String g = address.getCountry();
String h = super.getPhone();
String i = super.getFax();
String j = super.getRemarks();

Now to Search for the given guest_id

the query will be
String query = "select 1 from guest where guest_ID = '"+a+"'

If this query returns 1. then the Guestid is present. Now you have to update the table. for this
the query will be

"UPDATE guest set firstname = '"+a+"', lastname = '"+b+"', address = '"+c+"',city='"+d+"',
stateorprovince='"+e+"', postalcode = '"+f+"',country = '"+g+"',
emailaddress = '"+h+"',homephone = '"+i+"', faxnumber = '"+j+"' WHERE
guest_id = a;

this query will update the table with the specified guest id.

Now, If the first query, that is checking for guest_id in the table, if this fails then that means that the record is not present. The guest id is not present. So we have to go for inserting a new Guest rather than updating. The insert query is the same as what you said.
If you have any doubt over this then reply plz..

Mayank S

>> First you have to search whether the given guest_id is present or not. Then only u can update a record.

He says:

>> i want to retrieve back my record and i am searching by the guest_id,

Anyway, kennykoid, is it simply the update part which is troubling you or something else? If you're being able to insert a row, then I don't see a problem with updating it - merely, the query will change in your add () code. Is it something else that you want to know?

Mayank.

InNoCenT_Ch1ld

KarthikAnandan>> hello, you're just repeating my comment...but more complete. so i will hate you for the rest of my life if i dont get the pts.. hehehee ;p (just kidding)

-Child-