aspose file tools*
The moose likes JDBC and the fly likes Updating Access Db **Please Help** Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating Access Db **Please Help**" Watch "Updating Access Db **Please Help**" New topic
Author

Updating Access Db **Please Help**

shane ocon
Greenhorn

Joined: Jan 25, 2007
Posts: 4
Hi,
Were doing a hospital admin system where the hospital admin can enter a new patients details and save them to a ms access DB.
For this we created Data Access Objects.

We must have the ability to SELECT, ADD, DELETE and UPDATE the Db.
So far we have managed 3 but cannot figure out how to Update the DB.
Heres some of my ADD code:
public class PatientDAO
{

/** Creates a new instance of AdminADO */
public PatientDAO() {
}

public void select()
{
Connection con = null;
Patient patient = null;
Vector<Patient> patients = new Vector();

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbcdbcriver={Microsoft Access Driver (*.mdb)};DBQ=hospital.mdb";
con = DriverManager.getConnection(database,"","");
Statement stmt = con.createStatement();
ResultSet results = stmt.executeQuery("Select * from Patient");


while (results.next())
{
patient = new Patient();
patient.setPatientID(results.getString("Patient_ID"));
patient.setFName(results.getString("PF_Name"));
patient.setSName(results.getString("PS_Name"));
patient.setAddress(results.getString("Address"));
patient.setContact(results.getString("ContactNo"));
patient.setBP(results.getString("BloodPressure"));
patient.setTemp(results.getString("Temperature"));
patient.setBedNo(results.getString("BedNumber"));
patient.setWardID(results.getString("Ward_ID"));
patient.setDoctorID(results.getString("Doctor_ID"));
patient.setObservations(results.getString("Observations"));
patient.setMedicalFile(results.getString("MedicalFile"));

patients.add(patient);
}

for(Patient p :patients)
{
System.out.print(p.getPatientID()) ;
System.out.print(p.getFName()) ;
System.out.print(p.getSName()) ;
System.out.print(p.getAddress()) ;
System.out.println(p.getContactNo()) ;
System.out.print(p.getBP()) ;
System.out.print(p.getTemp()) ;
System.out.print(p.getBedNo()) ;
System.out.println(p.getWardID()) ;
System.out.println(p.getDoctorID()) ;
System.out.println(p.getObservations()) ;
System.out.println(p.getMedicalFile()) ;
}
}

catch(ClassNotFoundException e)
{
System.out.println(e.toString());
}

catch(SQLException e)
{
System.out.println(e.toString());
}

}
public void insert(Patient p)
{
Connection con1=null;

try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String database = "jdbcdbcriver={Microsoft Access Driver (*.mdb)};DBQ=hospital.mdb";
con1 = DriverManager.getConnection(database,"","");

PreparedStatement newpatient = con1.prepareStatement("INSERT INTO Patient(Patient_ID, PF_Name, PS_Name ,Address, ContactNo, BloodPressure, Temperature, BedNumber, Ward_ID, Doctor_ID, Observations, MedicalFile,)VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");

newpatient.setString(1,p.getPatientID());
newpatient.setString(2,p.getFName());
newpatient.setString(3,p.getSName());
newpatient.setString(4,p.getAddress());
newpatient.setString(5,p.getContact());
newpatient.setString(6,p.getBP());
newpatient.setString(7,p.getTemp());
newpatient.setString(8,p.getBedNo());
newpatient.setString(9,p.getWardID());
newpatient.setString(10,p.getDoctorID());
newpatient.setString(11,p.getObservations());
newpatient.setString(12,p.getMedicalFile());


newpatient.executeUpdate();
System.out.println("***Patient Added***");
//CLOSE
con1.close();

}

catch(SQLException e)
{
System.out.println(e.toString());
}

catch(ClassNotFoundException e)
{
System.out.println(e.toString());
}

}
}

Then, here is the main method*****************

public class PatientTest {

/** Creates a new instance of Main */
public PatientTest() {
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here

PatientDAO patientDAO = new PatientDAO();


Patient p = new Patient();
p.setAddress("Glanmire");
p.setContact("021-4822236");
p.setFName("Daniel");
p.setSName("Whelan");
p.setPatientID("1");
p.setWardID("Ward 1");
p.setTemp("56 degrees");
p.setBP("12");
p.setBedNo("Bed 2");
p.setDoctorID("1");
p.setObservations("Head Ache and Sore throat");
p.setMedicalFile("this is a medical file...input here");

patientDAO.insert(p);
patientDAO.select();

}

}

Any ideas on how i could update the DB now
PLEASE, PLEASE, please help

Thanks alot
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Shane,
What do you have so far for the update statement? What part are you stuck on: the update sql syntax, the prepared statement, etc?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I recommend you search this forum (using the search link above) for 'Update access database', as there is a common bug in the JDBC-ODBC bridge that may be relevant.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39409
    
  28
Do you really want to have the patient details in Access? I was speaking to somebody who has maintained a hospital database yesterday and he said there were all sorts of problems caused by people downloading part of the database into Access and then updating their little parts. You end up with lots of little databases with slightly different and slightly inaccurate data in each. And you don't want different or inaccurate databases in all sorts of places.
Les Hayden
Greenhorn

Joined: Jun 19, 2002
Posts: 28
Careful how you handle the connections...

I notice there is at least one place where you null the object before closing the connection object. I would be concerned about how a simple system like access will handle that occurrence. It might not present a problem, but I would not take the risk on something critical like a hospital system.


Les Hayden
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Updating Access Db **Please Help**