A little direction needed

[Sonny101]

Hi,

I am currently doing a JDBC project where I am manipulating data in a Access DB on a table named empDetail and one of my tasks has got me stumped, considering I only started learning about JDBC yesterday.
I hope somebody can give me a bit of direction as to what I need to do.

Basically I have to populate Jtextfields on a JFrame on start up, which I have done here:

public void getFirstRecord(){//method to retrieve first record of DB
        String sql = "SELECT * FROM empDetail";
        openDatabase();
        try{
            ResultSet rs = stmt.executeQuery(sql);
            

            if(rs.next()){
                txtEmpNo.setText(rs.getString(1));
                txtName.setText(rs.getString(2));
                txtAddress.setText(rs.getString(3));
                txtSuburb.setText(rs.getString(4));
                txtPostCode.setText(rs.getString(5));
                txtDOB.setText(rs.getString(6));
                txtPhone.setText(rs.getString(7));
                txtWorkExt.setText(rs.getString(8));
                txtMobile.setText(rs.getString(9));
                txtEmail.setText(rs.getString(10));
                
            }
        }
        catch(SQLException e){
           msgMessage.showMessageDialog(this,e.toString());
        }
        closeDatabase();
    }

Now I need to make a action performed method for a Jbutton (called New)
this is the task:
"When the new button is clicked, all the Jtextfields should be cleared except for the Employee No which should be populated by an employee number one higher then the largest Employee number in the EmpDetails table".

Clearing the text is no problem, but I am a little stumped on the rest.
Any pointers would be very much appreciated.

[dantinkakkar]

When "New" is clicked, the question wants you to create a new employee entry. The employee number will be one higher than the existing number of employees present obviously. So what I suggest is that you get from the DB [unless you've already stored it somewhere] the employee no. of the last employee, add it by one, and set the value for the employee number textbox field. Just reply if you have a problem with writing the code and I'll help you out there, too! :)

[Sonny101]

When "New" is clicked, the question wants you to create a new employee entry. The employee number will be one higher than the existing number of employees present obviously. So what I suggest is that you get from the DB [unless you've already stored it somewhere] the employee no. of the last employee, add it by one, and set the value for the employee number textbox field. Just reply if you have a problem with writing the code and I'll help you out there, too! :)

Sorry I may not have written my question clearly but you are right that is exactly what I need to do but its the code that I am unsure of.

I need to figure out what code to write to find the last record and add 1
I know this won't make sense but I was trying to use while(rs.next)and then do something ?
am I even close?

[dantinkakkar]

You'll have to play around with the SQL Query. This is the query you might need:

SELECT LAST(column_name) FROM table_name

Execute this, and the employee number of the last record will be returned as the output.

You are familiar with executing SQL queries, I hope? If you're not, the here's how you can go about:

QueryString = "SELECT * from user_master1";
  rs = statement.executeQuery(QueryString);
  while (rs.next()) {
  System.out.println(rs.getInt(1) + " " + rs.getString(2) + "  
                  " + rs.getString(3) + "  "+rs.getString(4)+"\n");

[EDIT: In your case, only one value will be returned, so I don't think you'll need the loop. :) ]

[Sonny101]

You'll have to play around with the SQL Query. This is the query you might need:

SELECT LAST(column_name) FROM table_name

Execute this, and the employee number of the last record will be returned as the output.

You are familiar with executing SQL queries, I hope? If you're not, the here's how you can go about:

QueryString = "SELECT * from user_master1";
  rs = statement.executeQuery(QueryString);
  while (rs.next()) {
  System.out.println(rs.getInt(1) + " " + rs.getString(2) + "  
                  " + rs.getString(3) + "  "+rs.getString(4)+"\n");

[EDIT: In your case, only one value will be returned, so I don't think you'll need the loop. :) ]

Thankyou,

this is what I have:

clearFields();
       String sql = "SELECT [Emp ID] FROM empDetail";
        openDatabase();
        try{
            ResultSet rs = stmt.executeQuery(sql);
      
       while(rs.next()){
                txtEmpNo.setText(rs.getString(1));
            }}
            catch(SQLException e){}

which is doing all that I need except adding 1 so the value that shows up is 103 which is the last record, how do I add 1 to this to make it 104.

It's probably simple for you experts :) but I can't seem to get it:'(. Thanks again for your help

[dantinkakkar]

It's nothing. Just do me a favour, and mark this thread as solved! :)

[Sonny101]

It's nothing. Just do me a favour, and mark this thread as solved! :)

I will as soon as you tell me how to add one(see my last reply) lol!!

[dantinkakkar]

Wait. You got something wrong with your code there.

clearFields();
String sql = "SELECT LAST(Emp_ID) FROM empDetail";
openDatabase();
try{
ResultSet rs = stmt.executeQuery(sql);
String res = rs.getString(1);
int hh = Integer.parseInt(res);
hh+=1;
}
catch(SQLException e){}

I've modified line 2. Look at that first. Now, look at the rest. hh is your final result which you need.

[dantinkakkar]

(Am not editing since this is an explanation and you may miss out on it if I edit.)

The query

SELECT LAST(Emp_id) FROM empDetail

Will return the last value of Emp_id, or the last employees' number, say 100.
Now, I've taken it in a string as follows:

String res = rs.getString(1)

No loops 'coz the output will bein the form of a lone String only. Now, parse the integer out of it.

int hh = Integer.parseInt(res);
hh+=1;

Added it by one and now append it to the TBox.

txtEmpNo.setText(hh.toString());

[Sonny101]

(Am not editing since this is an explanation and you may miss out on it if I edit.)

The query

SELECT LAST(Emp_id) FROM empDetail

Will return the last value of Emp_id, or the last employees' number, say 100.
Now, I've taken it in a string as follows:

String res = rs.getString(1)

No loops 'coz the output will bein the form of a lone String only. Now, parse the integer out of it.

int hh = Integer.parseInt(res);
hh+=1;

Added it by one and now append it to the TBox.

txtEmpNo.setText(hh.toString());

thanks a lot I can see clearly now

[Sonny101]

this:

txtEmpNo.setText(hh.toString());

gives me this error:int cannot be dereferenced

[Sonny101]

this:

txtEmpNo.setText(hh.toString());

gives me this error:int cannot be dereferenced

This is the code I ended up with and seems to be working thank you all!

clearFields();
             String sql = "SELECT MAX([Emp ID]) FROM empDetail";
             openDatabase();
                try{
                     ResultSet rs = stmt.executeQuery(sql);
                     rs.next();
                     int hh = rs.getInt(1);
                     hh+=1;
                     txtEmpNo.setText(Integer.toString(hh));


                   }
                   catch(SQLException e){
                   msgMessage.showMessageDialog(this,e.toString());}
        
                   closeDatabase();

[dantinkakkar]

Alright, sure! :) Happy to help! :D