Trying to retrieve patient Info

[JBeginer7891]

Ok here is the new thread now could you help me JavaAddict? Please:icon_sad:

[javaAddict]

Yes I could

[JBeginer7891]

Yes I could

OK here is the problem:
When the Registration form load all the patient numbers are loaded in a textArearfield. The user should be able to choose any patient number, click the getInfo button and get his/her information in the specific textField. but when I click on the first, second, sometimes even third record it gives the exception: No data Found
SQLState: null
VendorError:0
can you please help? here is the code for the Get Info button

//Do Get Account Button
    getBtn = new JButton("Get PatientInfo");
    getBtn.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        try {
          rs.first();
          while ((rs.next())) {
            if (rs.getString("patientNo").equals(
                PatientNumberList.getSelectedValue()))
              break;
          }
          if (!rs.isAfterLast()) {
            jtfPnum.setText(rs.getString("patientNo"));
            jtfFname.setText(rs.getString("fname"));
	    jtfSname.setText(rs.getString("sname"));
	    jtfLocation.setText(rs.getString("location"));
	    jtfDOB.setText(""+ rs.getDate("dob"));
	    jtfDOR.setText(""+ rs.getDate("dor"));
	    jtfRace.setText(rs.getString("race"));
	    jtfGender.setText(rs.getString("gender"));
	    jtfStat.setText(rs.getString("status"));
	    jtfInit.setText(rs.getString("initials"));
	    jtfID.setText("" + rs.getLong("idnum"));

          }
        } catch (SQLException selectException) {
          displaySQLErrors(selectException);
        }
      }
    });

Thanks very much for the support, I am very grateful!

[javaAddict]

As I said in previous post, don't create a ResultSet and leave it open.
Have an object like this:

class Patient {
  private int patientNo;
  private String fName = "";
  private String lName = "";
.....

  public Patient() {

  }

 // have methods for getting, setting those values:
 public int getPatientNo() {
    return patientNo;
  }

 public void getPatientNo(int patNo) {
    this.patientNo = patNo;
  }
}

Then have a method like this:

public Patient getPatient(int patientNo) throws SQLException {
  // do the things I told you in your other post about how to read data from DataBase

  // if results found create a Patient object and return it
}

Then call the above method, get the Patient objetc and get its values. Then you can do whatever you want with those values.

[JBeginer7891]

Hey Sorry I sent the same question in the other post!
you meanty something like this

public class Patient
{
	private String patientNo,fname,initials,sname,location,dob,dor,status,gender,race;
	private long idnum;

      // default constructor
      public Patient()
      {  
	 patientNo="";     
         fname = "";
         initials="";
	 sname="";
	 dor="";
         location = "";
         dob = "";
	 status = "";
	 gender="";
         race="";
	 initials="";
         idnum = 0;  
        
      }

	public Patient( String patientNO, String name, String sname, String location,String dateObirth,
			   String dateOreg,String race,String gender,String status,String initials, long id)
	
	{
                this.idnum = id;
		this.fname = name;
		this.sname = sname;
		this.location = location;
		this.patientNo=patientNO;
		this.initials= initials;
		this.gender=gender;
		this.race=race;
		this.status=status;
		this.dob=dateObirth;
		this.dor=dateOreg;
	}
 

	public Patient(String patientNO, String name, String sname, String location,String dateObirth,
			   String dateOreg,String race,String gender,String status,String initials)
	{
		this.fname = name;
		this.sname = sname;
		this.location = location;
		this.patientNo=patientNO;
		this.initials= initials;
		this.gender=gender;
		this.race=race;
		this.status=status;
		this.dob=dateObirth;
		this.dor=dateOreg;
	}

      // setters
	public void setId(long i)
	{
		idnum = i;
	}

	public void setFirstName(String n)
	{
		fname=n;		
	}
	public void setSurname(String sn)
	{
		sname=sn;
	}
	public void setLocation(String loc)
	{
		location=loc;
	}
	public void setGender(String gen)
	{
		gender=gen;
	}
	public void setRace(String ra)
	{
		race=ra;		
	}
	public void setStatus(String sta)
	{
		status=sta;
	}
	public void setInitials(String ini)
	{
		initials=ini;
	}
	public void setPatientNo(String Pnum)
	{
		patientNo=Pnum;
	}
	public void setDateOBirth(String db)
	{
		dob=db;
	}
	public void setDateOReg(String dr)
	{
		dor=dr;
	}
      // getters
	
	public String getPatientNo()
	{
		return patientNo;
	}
        public String getInitials()
	{
		return initials;
	}
	public double getId( )
	{
		return idnum;
        }
	public String getFirstName()
	{
		return fname;
	}

	public String getLastName()
	{
		return sname;
	}

	public String getLocation()
	{
		return location;
	}

	public String getStatus()
	{
		return status;

	}	
	public String getDateOBirth()
	{
		return dob;
	}

	public String getDateOReg()
	{
		return dor;
	}

	public String getGender()
	{
		return gender;
	}

	public String getRace()
	{
		return race;
	}

}

But please explain further these

Then have a method like this:

public Patient getPatient(int patientNo) throws SQLException {
  // do the things I told you in your other post about how to read data from DataBase

  // if results found create a Patient object and return it
}

Then call the above method, get the Patient objetc and get its values. Then you can do whatever you want with those values.

How could you alter this code of mine with the new ideas you stated?

//Do Get Patient Info Button
    getBtn = new JButton("Get PatientInfo");
    getBtn.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e) {
        try {
          rs.first();
          while ((rs.next())) {
            if (rs.getString("patientNo").equals(
                PatientNumberList.getSelectedValue()))
              break;
          }
          if (!rs.isAfterLast()) {
            jtfPnum.setText(rs.getString("patientNo"));
            jtfFname.setText(rs.getString("fname"));
	    jtfSname.setText(rs.getString("sname"));
	    jtfLocation.setText(rs.getString("location"));
	    jtfDOB.setText(""+ rs.getDate("dob"));
	    jtfDOR.setText(""+ rs.getDate("dor"));
	    jtfRace.setText(rs.getString("race"));
	    jtfGender.setText(rs.getString("gender"));
	    jtfStat.setText(rs.getString("status"));
	    jtfInit.setText(rs.getString("initials"));
	    jtfID.setText("" + rs.getLong("idnum"));

          }
        } catch (SQLException selectException) {
          displaySQLErrors(selectException);
        }
      }
    });

Thanks and sorry again for double-posting!

[javaAddict]

public Patient getPatient(int patientNo) throws SQLException {
// Connection, statement, ResultSet

// Call query

// get the info

Patient pt = new Patient();
pt.set...(..)

// close Connection, statement, ResultSet

return pt;
}

If there is no patient found, you can return null, and after you call the method you check that and print the right message:

Patient pt = null;

..

if (rs.next()) {
 pt = new Patient();  

 pt.set...(..)
}

return pt;

Patient pt = getPatienNifo(ptNo);
if (pt==null) {
  // show message 
} else {
  use the pt values to set them wherever you want
}

If you want it to be added to a JTable check the setModel method of the JTable class. You will use a DefaultTableModel instance. Check the API and you will find methods that can help you.

[JBeginer7891]

Hello! this is what I've done so far.

public Patient getPatient(String patientNo) throws SQLException {
    Patient pt = new Patient();
     // Connection, statement, ResultSet
    Connection conn =null;
    Statement st = null;
    ResultSet rset = null;
    String query = "SELECT * FROM PatientTable" +
            " WHERE patientNo='"+PatientNumberList.getSelectedValue()+"'";
      try{
    //opening the connection
       conn = DriverManager.getConnection(url, userid, password);
    // create the statement
       st =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);             
    // Call query 
    rset =st.executeQuery(query);  
    if (rs.next()){
    // get the info  
   pt.setPatientNo(rset.getString("patientNo"));
   pt.setFirstName(rset.getString("fname"));
   pt.setSurname(rset.getString("sname"));
   pt.setLocation(rset.getString("location"));
   pt.setDateOBirth(rset.getString("dob"));
   pt.setDateOReg(rset.getString("dor"));
   pt.setRace(rset.getString("race"));
   pt.setGender(rset.getString("gender"));
   pt.setInitials(rset.getString("initials"));
   pt.setStatus(rset.getString("status"));
   pt.setId(rset.getLong("idnum"));
   }
 }catch(SQLException exc){
          throw exc;   
      }
    finally{
   // close Connection, statement, ResultSet
        if(conn!=null) conn.close();
        if(st!=null) st.close();
        if(rset!=null) rset.close();
    }
 return pt;       }

And the GetInfo button that call the method GetPatient is the one that I didn't get
here is how I structured it

//Do Get Patient Info Button
getBtn = new JButton("Get PatientInfo");
getBtn.addActionListener(new ActionListener() {
  public void actionPerformed(ActionEvent e) {
    String ptNo=null;
    try {          
    Patient pt = getPatient(ptNo);
      if(pt==null){

      }else{

      }
    } catch (SQLException selectException) {
      displaySQLErrors(selectException);
    }
  }
});

based on this

Patient pt = getPatienNifo(ptNo);
if (pt==null) {
  // show message 
} else {
  use the pt values to set them wherever you want
}

Please just comment on it a bit further to allow me to carry on.
Thanks !

[javaAddict]

From what I see you just copied my code blindly. You didn't even bother to understand anything. How do I know this?
For starters, the method takes as argument the patienNo, but instead of using that, you used this: "PatientNumberList.getSelectedValue()"

And when you call the method you call it with null:

String ptNo=null;
Patient pt = getPatient(ptNo);

So you need to decide, do you want an argument, or you will take the value directly with the getSelectedValue() ?

Once you get the Patient you can do whatever you want with its values. Set them to text fields, open a new JFrame and display them, ...

[JBeginer7891]

From what I see you just copied my code blindly. You didn't even bother to understand anything. How do I know this?
For starters, the method takes as argument the patienNo, but instead of using that, you used this: "PatientNumberList.getSelectedValue()"

And when you call the method you call it with null:

String ptNo=null;
Patient pt = getPatient(ptNo);

So you need to decide, do you want an argument, or you will take the value directly with the getSelectedValue() ?

Once you get the Patient you can do whatever you want with its values. Set them to text fields, open a new JFrame and display them, ...

Ok, you right!I just pasted your code but I did understand it, only the argument part escaped me.
Here is the code now

public Patient getPatient(String patientNo) throws SQLException {
    Patient pt = new Patient();
     // Connection, statement, ResultSet
    Connection conn =null;
    Statement st = null;
    ResultSet rset = null;
    String query = "SELECT * FROM PatientTable" +
            " WHERE patientNo= '"+patientNo+"'";
      try{
    //opening the connection
       conn = DriverManager.getConnection(url, userid, password);
    // create the statement
       st =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);             
    // Call query 
    rset =st.executeQuery(query);
    if (rset.next()){
    // get the info  
   pt.setPatientNo(rset.getString("patientNo"));
   pt.setFirstName(rset.getString("fname"));
   pt.setSurname(rset.getString("sname"));
   pt.setLocation(rset.getString("location"));
   pt.setDateOBirth(rset.getString("dob"));
   pt.setDateOReg(rset.getString("dor"));
   pt.setRace(rset.getString("race"));
   pt.setGender(rset.getString("gender"));
   pt.setInitials(rset.getString("initials"));
   pt.setStatus(rset.getString("status"));
   pt.setId(rset.getLong("idnum"));
   }
 }catch(SQLException exc){
          throw exc;   
      }
    finally{
   // close Connection, statement, ResultSet
        if(rset!=null) rset.close();
        if(conn!=null) conn.close();
        if(st!=null) st.close();
    }
    return pt;}

//Do Get Patient Info Button
    getBtn = new JButton("Get PatientInfo");
    getBtn.addActionListener(new ActionListener(){
       public void actionPerformed(ActionEvent e){    
        try { 
        String ptNo=null;
        Patient pt = getPatient(ptNo);
          if(pt==null){
              JOptionPane.showMessageDialog(null, "The Oject is empty!");
          }else{
            jtfPnum.setText(rs.getString("patientNo"));
            jtfFname.setText(rs.getString("fname"));
	    jtfSname.setText(rs.getString("sname"));
	    jtfLocation.setText(rs.getString("location"));
	    jtfDOB.setText(""+ rs.getDate("dob"));
	    jtfDOR.setText(""+ rs.getDate("dor"));
	    jtfRace.setText(rs.getString("race"));
	    jtfGender.setText(rs.getString("gender"));
	    jtfStat.setText(rs.getString("status"));
	    jtfInit.setText(rs.getString("initials"));
	    jtfID.setText("" + rs.getLong("idnum"));
          }     
        } catch (SQLException selectException){
          displaySQLErrors(selectException);
        }
      }  
    });

But when I select a patientNo and click the button it gives this exception

SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
SQLState:     24000
VendorError:  0

[javaAddict]

You wrote this:

String ptNo=null;
Patient pt = getPatient(ptNo);

You passed as argument null. It is in front of your eyes!

With every error you got you didn't made a single attempt ot try and fix it. You just posted and expected others to tell you the asnwer so you can continue until you make the next error.

You got an SQLException. Did you try to print the query and run it in order to see what is executed. The above has nothing to do with java. It is common sense.

There is a difference between being a beginner in java and not being able to think for yourself. I have told you everything you need to know to fix this. If you can't put some simple code together then I am not going to write the code for you in order to get your intership.

Fix this and if you have another question that has nothing to do with this, then start a new thread and someone will answer it. I have wasted my time here.

[JBeginer7891]

You wrote this:

String ptNo=null;
Patient pt = getPatient(ptNo);

You passed as argument null. It is in front of your eyes!

With every error you got you didn't made a single attempt ot try and fix it. You just posted and expected others to tell you the asnwer so you can continue until you make the next error.

You got an SQLException. Did you try to print the query and run it in order to see what is executed. The above has nothing to do with java. It is common sense.

There is a difference between being a beginner in java and not being able to think for yourself. I have told you everything you need to know to fix this. If you can't put some simple code together then I am not going to write the code for you in order to get your intership.

Fix this and if you have another question that has nothing to do with this, then start a new thread and someone will answer it. I have wasted my time here.

Heydon't get angry, I'm sorry!
Here is what I figured out but it still gives me the same exception

String ptNo=(String)PatientNumberList.getSelectedValue();
    Patient pt = getPatient(ptNo);

Please assist I'm sorry!

[javaAddict]

In my previous post I suggested for you to print the query and try to run it at the database in order to see if it will run. Try that, and tell me if it brought correct results.

Also search your code again in case you have something open. Remember:

// open connection, Statement, ...
//run query
// close everything in the finally

I searched the web for that error. You should have done the same.

Can you try and using this command in order to create the statement:
>> st =conn.createStatement()

Also from what I have found, can you check your code if you are using any commands like setAutoCommit() ?

If the above suggestion don't solve your problem can you post the query you used to create the table. Also the data types of the columns would be helpful.

[JBeginer7891]

In my previous post I suggested for you to print the query and try to run it at the database in order to see if it will run. Try that, and tell me if it brought correct results.

Also search your code again in case you have something open. Remember:

// open connection, Statement, ...
//run query
// close everything in the finally

I searched the web for that error. You should have done the same.

Can you try and using this command in order to create the statement:
>> st =conn.createStatement()

Also from what I have found, can you check your code if you are using any commands like setAutoCommit() ?

If the above suggestion don't solve your problem can you post the query you used to create the table. Also the data types of the columns would be helpful.

Sorry, JavaAddict but those suggestion didn't work
here is the SQL statement that create my table PatientTable.

CREATE TABLE PatientTable(
patientNo varchar(255),
fname varchar(255),
sname varchar(255),
location varchar(255),
dob Date,
dor Date,
race varchar(255),
gender varchar(255),
initials varchar(255),
Status varchar(255),
idnum INT
)

Hope that could help!

[javaAddict]

In a few hours I will give you a more detailed suggestion. For the time being, can you run the code again, and post the entire exception stack trace.:

catch(SQLException exc){
  exc.printStackTrace();
  throw exc;
}

Also there would be some line numbers. Go to those files the lines refer and post those lines as well. Of course you will post only the lines of your classes, because the stack trace will print and other class lines as well.

[javaAddict]

Fist of all print the query and run it directly at the database and see if it runs.

Then, create a new class with one main method. Inside do this:

public static void main(String [] args) {
  Connection conn = null;
  Statement st= null;
  ResultSet rs = null; 

  String query = "select * from PatientTable where patientNo = '123'";
  // select * from PatientTable where patientNo = '123'

  try {
       Class.forName("your driver");
       conn = DriverManager.getConnection(url, userid, password);
       st = conn.createStatement();
       rs =st.executeQuery(query);
  
       if (rs.next()) {
           System.out.println("Found");
       } else {
           System.out.println("Not Found");
       }
  } catch (Exception e) {
     System.out.println("Exception e: "+e.getMessage());
     System.out.println("----------------------------");
     e.printStackTrace();
  } finally {
     try { if (rs!=null) rs.close(); } catch (Exception e) {e.printStackTrace();}
     try { if (st!=null) st.close(); } catch (Exception e) {e.printStackTrace();}
     try { if (conn!=null) conn.close(); } catch (Exception e) {e.printStackTrace();}
  }
}

Try to keep the finally the way it is; close them in that order. (Actually I don't know if the order that you close them makes a difference but just in case follow that order.)

[JBeginer7891]

Hello! I did this

catch(SQLException exc){
  exc.printStackTrace();
  throw exc;
}

Here are StackTrace it gives

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
        at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3907)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5698)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:354)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:411)
at PatientReg$2.actionPerformed(PatientReg.java:151)

This refers to this line

jtfPnum.setText(rs.getString("patientNo"));

this line is part of this method

//Do Get Patient Info Button
    getBtn = new JButton("Get PatientInfo");
    getBtn.addActionListener(new ActionListener(){
       public void actionPerformed(ActionEvent e){    
        try { 
        String ptNo=(String)PatientNumberList.getSelectedValue();
        Patient pt = getPatient(ptNo);
          if(pt==null){
              JOptionPane.showMessageDialog(null, "There are no Record in the Table!");
          }else{
            jtfPnum.setText(rs.getString("patientNo"));
            jtfFname.setText(rs.getString("fname"));
	    jtfSname.setText(rs.getString("sname"));
	    jtfLocation.setText(rs.getString("location"));
	    jtfDOB.setText(""+ rs.getDate("dob"));
	    jtfDOR.setText(""+ rs.getDate("dor"));
	    jtfRace.setText(rs.getString("race"));
	    jtfGender.setText(rs.getString("gender"));
	    jtfStat.setText(rs.getString("status"));
	    jtfInit.setText(rs.getString("initials"));
	    jtfID.setText("" + rs.getLong("idnum"));
          }     
        } catch(SQLException exc){
            exc.printStackTrace();
            }
      }  
    });

Thanks for the support!

[JBeginer7891]

I also tried this:

Fist of all print the query and run it directly at the database and see if it runs.

Then, create a new class with one main method. Inside do this:

public static void main(String [] args) {
  Connection conn = null;
  Statement st= null;
  ResultSet rs = null; 

  String query = "select * from PatientTable where patientNo = '123'";
  // select * from PatientTable where patientNo = '123'

  try {
       Class.forName("your driver");
       conn = DriverManager.getConnection(url, userid, password);
       st = conn.createStatement();
       rs =st.executeQuery(query);
  
       if (rs.next()) {
           System.out.println("Found");
       } else {
           System.out.println("Not Found");
       }
  } catch (Exception e) {
     System.out.println("Exception e: "+e.getMessage());
     System.out.println("----------------------------");
     e.printStackTrace();
  } finally {
     try { if (rs!=null) rs.close(); } catch (Exception e) {e.printStackTrace();}
     try { if (st!=null) st.close(); } catch (Exception e) {e.printStackTrace();}
     try { if (conn!=null) conn.close(); } catch (Exception e) {e.printStackTrace();}
  }
}

Try to keep the finally the way it is; close them in that order. (Actually I don't know if the order that you close them makes a difference but just in case follow that order.)

It works fine showing

Not found

for patientNo ='123'
And

found

when I use a patientNo from the Table.

[javaAddict]

You have a method: Patient pt = getPatient(ptNo); which executes correctly, you call it and you get the patient info.
You are calling the method that brings you the data you want.

Then you call the rs.getString("..") again, outside the method, after you acquired the data
Why?

Delete all the global declarations of Connection, ResultSet and Statement.

[JBeginer7891]

You have a method: Patient pt = getPatient(ptNo); which executes correctly, you call it and you get the patient info.
You are calling the method that brings you the data you want.

Then you call the rs.getString("..") again, outside the method, after you acquired the data
Why?

Delete all the global declarations of Connection, ResultSet and Statement.

But those global connection, statement and resultset are used by other methods in the class.

[javaAddict]

But those global connection, statement and resultset are used by other methods in the class.

Are you closing them? Because in your code, you call the getPatient, inside you have a ResultSet which you declare and close, and outside you are using another ResultSet which has no data inside, even though you already got the data.
My suggestion is to have methods that Declare the above (connection, statement and resultset), "open" them, "close" them and return the results.

You are not gaining anything by having them global. They shouldn't be in the same class. You should have another class with those methods, that return only results

[JBeginer7891]

Are you closing them? Because in your code, you call the getPatient, inside you have a ResultSet which you declare and close, and outside you are using another ResultSet which has no data inside, even though you already got the data.
My suggestion is to have methods that Declare the above (connection, statement and resultset), "open" them, "close" them and return the results.

You are not gaining anything by having them global. They shouldn't be in the same class. You should have another class with those methods, that return only results

I don't get what values would they return?

[JBeginer7891]

You have a method: Patient pt = getPatient(ptNo); which executes correctly, you call it and you get the patient info.
You are calling the method that brings you the data you want.

Then you call the rs.getString("..") again, outside the method, after you acquired the data
Why?

Delete all the global declarations of Connection, ResultSet and Statement.

Should I post the entire code for you to analyze?

[JBeginer7891]

Should I post the entire code for you to analyze?

I've tried to change things .
Now I'm using the JTable to display the search result.
here is what i've done

//Do search button
    Searchbtn = new JButton("Search");
    Searchbtn.addActionListener(new ActionListener(){
        public void actionPerformed(ActionEvent e){
            try{
            
            String[] tableColumnsName = {"Patient No","FirstName","Surname","Location","D.O.B",
            "D.O.R","Race","Gender","Status","Initials","ID Number"}; 
            DefaultTableModel aModel = (DefaultTableModel) aTable.getModel();
            aModel.setColumnIdentifiers(tableColumnsName);

            // the query
            ResultSet rs = statement.executeQuery("SELECT * FROM PatientTable "
                    +"WHERE sname = '"+ jtffreeQuery.getText()+ "'");
                if (rs.next()){
            // Loop through the ResultSet and transfer in the Model
                java.sql.ResultSetMetaData rsmd = rs.getMetaData();
                int colNo = rsmd.getColumnCount();
                while(rs.next()){
                Object[] objects = new Object[colNo];         
                    for(int i=0;i<colNo;i++){
                    objects[i]=rs.getObject(i+1);
                    }
                aModel.addRow(objects);
                }
                aTable.setModel(aModel);
             }else{
                aTable.setVisible(false);
                JOptionPane.showMessageDialog(null, "Record Not Found!");
             }
            }catch(SQLException evt){
               evt.printStackTrace();
            }
        }
        
    });

this works fine except that, when I search for a patient surname 'Obama' for example if the are 3 record with this surname, it only shows two and left one out. Also could you please help me get the table to fit its contents, the table appears in the center and all the columns are shrunk into it.
Thanks !

[javaAddict]

I don't get what values would they return?

As explained before you don't need to declare them (conn, st, rs) globally. You will have separate method where you will declare these locally, use them, close them and that is it.
You can have those methods in another class, if you want, but the idea is to call those methods and at the place you call them, you are not suppose to bother with "database stuff":

public method() {
// do database stuff to get the data
// open , close connection, ....
}

....

{
  // call the method somewhere else
  // simply get the results, like the getPatient method
}

Also in your previous question, one record is left out because you call the rs.next twice and you don't get for the first call any data:

if ([B]rs.next()[/B]){
  java.sql.ResultSetMetaData rsmd = rs.getMetaData();
  int colNo = rsmd.getColumnCount();
  
  while([B]rs.next()[/B]){
     // get the data
  }
}

As you can see the first time you call rs.next, you only take the ResultSetMetaData. Then when you call rs.next() again and you skip the first row you read.

In the Searchbtn.addActionListener code you have, again you are using the global Connection and statement. I don't see them being closed anywhere!!!
Remove the global declarations. Initialize them there locally and after you are done, CLOSE them, as well as the ResultSet

[JBeginer7891]

As explained before you don't need to declare them (conn, st, rs) globally. You will have separate method where you will declare these locally, use them, close them and that is it.
You can have those methods in another class, if you want, but the idea is to call those methods and at the place you call them, you are not suppose to bother with "database stuff":

public method() {
// do database stuff to get the data
// open , close connection, ....
}

....

{
  // call the method somewhere else
  // simply get the results, like the getPatient method
}

Also in your previous question, one record is left out because you call the rs.next twice and you don't get for the first call any data:

if ([B]rs.next()[/B]){
  java.sql.ResultSetMetaData rsmd = rs.getMetaData();
  int colNo = rsmd.getColumnCount();
  
  while([B]rs.next()[/B]){
     // get the data
  }
}

As you can see the first time you call rs.next, you only take the ResultSetMetaData. Then when you call rs.next() again and you skip the first row you read.

In the Searchbtn.addActionListener code you have, again you are using the global Connection and statement. I don't see them being closed anywhere!!!
Remove the global declarations. Initialize them there locally and after you are done, CLOSE them, as well as the ResultSet

Thanks! but as I said in my previous post its contents are shrunk in the center. I mean how can make the width of the Jtable and the jscrollPane wider so that the columns are not shrunk?
Thanks for the support.
Here is the code

//Do search button
    Searchbtn = new JButton("Search");
    Searchbtn.addActionListener(new ActionListener(){

        public void actionPerformed(ActionEvent e){

           try{
            // Connect to the database
            Connection connection = DriverManager.getConnection(url,userid,password);
            Statement statement = connection.createStatement();
            // the query
            ResultSet rs = statement.executeQuery("SELECT * FROM PatientTable "
                        +"WHERE Surname = '"+ jtffreeQuery.getText()+ "'");
            String[] tableColumnsName = {"Patient No","FirstName","Surname","Location","D.O.B", "D.O.R",
                                         "Race","Gender","Status","Initials","ID Number"};
            DefaultTableModel aModel = (DefaultTableModel) aTable.getModel();
                aModel.setColumnIdentifiers(tableColumnsName);

                if (rs!=null){
                    // Loop through the ResultSet and transfer in the Model
                    java.sql.ResultSetMetaData rsmd = rs.getMetaData();
                    int colNo = rsmd.getColumnCount();
                    while(rs.next()){
                        Object[] objects = new Object[colNo];
                        for(int i=0;i<colNo;i++){
                            objects[i]=rs.getObject(i+1);
                        }
                        aModel.addRow(objects);
                    }
                    aTable.setModel(aModel);
                }else{                aTable.setVisible(false);
                JOptionPane.showMessageDialog(null, "Record Not Found!");
                }
              if(rs!=null) rs.close();
              if(connection!=null) connection.close();
              if(statement!= null) statement.close();

            }catch(SQLException evt){
                evt.printStackTrace();
            }
        }
    });

[javaAddict]

Thanks! but as I said in my previous post its contents are shrunk in the center. I mean how can make the width of the Jtable and the jscrollPane wider so that the columns are not shrunk?
Thanks for the support.
Here is the code

//Do search button
    Searchbtn = new JButton("Search");
    Searchbtn.addActionListener(new ActionListener(){

        public void actionPerformed(ActionEvent e){

           try{
            // Connect to the database
            Connection connection = DriverManager.getConnection(url,userid,password);
            Statement statement = connection.createStatement();
            // the query
            ResultSet rs = statement.executeQuery("SELECT * FROM PatientTable "
                        +"WHERE Surname = '"+ jtffreeQuery.getText()+ "'");
            String[] tableColumnsName = {"Patient No","FirstName","Surname","Location","D.O.B", "D.O.R",
                                         "Race","Gender","Status","Initials","ID Number"};
            DefaultTableModel aModel = (DefaultTableModel) aTable.getModel();
                aModel.setColumnIdentifiers(tableColumnsName);

                if (rs!=null){
                    // Loop through the ResultSet and transfer in the Model
                    java.sql.ResultSetMetaData rsmd = rs.getMetaData();
                    int colNo = rsmd.getColumnCount();
                    while(rs.next()){
                        Object[] objects = new Object[colNo];
                        for(int i=0;i<colNo;i++){
                            objects[i]=rs.getObject(i+1);
                        }
                        aModel.addRow(objects);
                    }
                    aTable.setModel(aModel);
                }else{                aTable.setVisible(false);
                JOptionPane.showMessageDialog(null, "Record Not Found!");
                }
              if(rs!=null) rs.close();
              if(connection!=null) connection.close();
              if(statement!= null) statement.close();

            }catch(SQLException evt){
                evt.printStackTrace();
            }
        }
    });

You have started another thread with the same question. Try the suggestions there and also check the API for JTable