CODEWITHSUNDEEP
javasql
Sumeet Gavhale
Sumeet Gavhale

Reputation: 800

check if the record exists in database

I need to check the box no if exist in the database under the remittance id that I enter if the box no exists then i need to show the message that the box no already exists but if it doesn't the it should insert new box i have written some code but its showing error 

private void txtboxnoFocusLost(java.awt.event.FocusEvent evt) {
    DBUtil util = new DBUtil();

    try {
        Connection con = util.getConnection();
        PreparedStatement stmt = con.prepareStatement(
            "select box_no from dbo.soil_det where rm_id = ? and box_no = ?");
        stmt.setLong(1, Long.parseLong(tf_rm_id.getText()));
        stmt.setString(1, (txtboxno.getText()));       
        ResultSet rs=stmt.executeQuery();
        while(rs.next()){
            rs.equals().txtboxno.getText());
        }
        JOptionPane.showMessageDialog(rootPane, "hello!S");
    } catch (Exception ex) {
        Logger.getLogger(DATAENTRY.class.getName()).log(Level.SEVERE, null, ex);
    }

Upvotes: 2

Views: 22307

Answers (6)

Sindhukumari P
Sindhukumari P

Reputation: 350

simplified version

private void txtboxnoFocusLost(java.awt.event.FocusEvent evt) {
DBUtil util = new DBUtil();

try {
    Connection con = util.getConnection();
    PreparedStatement stmt = con.prepareStatement(
        "select box_no from dbo.soil_det where rm_id = ? and box_no = ?");
    stmt.setLong(1, Long.parseLong(tf_rm_id.getText()));
    stmt.setString(2, (txtboxno.getText()));       
    ResultSet rs=stmt.executeQuery();
    if(!rs.next()){
      JOptionPane.showMessageDialog(rootPane, "Record added");
    }else{
       JOptionPane.showMessageDialog(rootPane, "Record already exists");
    }
} catch (Exception ex) {
    Logger.getLogger(DATAENTRY.class.getName()).log(Level.SEVERE, null, ex);
}
  • rs.next() followed by if condition returns true if the record exists in a table. if not, return false.

Upvotes: 0

Brian Agnew
Brian Agnew

Reputation: 272297

You need to get the appropriate record from the ResultSet e.g.

boolean found = rs.getString(1).equals().txtboxno.getText());

At the moment you're simply comparing the ResultSet object itself to a string, and that won't work. The above pulls the first record from the ResultSet and performs the comparison on that (note: your datatype may be different and you may need rs.getInt(1) etc.)

Perhaps its sufficient in your case just to check if you have a ResultSet result (via rs.next())

Upvotes: 0

Rahul
Rahul

Reputation: 281

Try this code

private void txtboxnoFocusLost(java.awt.event.FocusEvent evt) {
DBUtil util = new DBUtil();

try {
    Connection con = util.getConnection();
    PreparedStatement stmt = con.prepareStatement(
        "select box_no from dbo.soil_det where rm_id = ? and box_no = ?");
    stmt.setLong(1, Long.parseLong(tf_rm_id.getText()));
    stmt.setString(2, (txtboxno.getText()));       
    ResultSet rs=stmt.executeQuery();
    bool recordAdded = false;
    while(!rs.next()){            
       /// Do your insertion of new records
         recordAdded = true;
    }
    if( recordAdded ){
      JOptionPane.showMessageDialog(rootPane, "Record added");
    }else{
       JOptionPane.showMessageDialog(rootPane, "Record already exists");
    }
} catch (Exception ex) {
    Logger.getLogger(DATAENTRY.class.getName()).log(Level.SEVERE, null, ex);
}

Upvotes: 2

pgras
pgras

Reputation: 12770

First you could add -- on the db table -- a unique constrain on the columns (rm_id, box_no), this is anyway a good thing to do.

Then you could simply try to insert the box and catch the exception and check if it is a violation of the unique constraint.

Another option (still keeping the unique constraint) would be to make a more complicated SQL insert statement that inserts only if not existing, you may google "sql insert if not exist" to find some examples...

Upvotes: 0

Prafulla
Prafulla

Reputation: 39

very First You have to get count using sql if count is greater than zero then do not insert records and show message like already exists and in else part insert record. see following example

private boolean findCount(int rm_id,String box_no)
{
      int count=0;
     //write query here 
     count = assign query result;
     //check count 
if(count>0)
{
     return false;//records exists
}else{
    return true;//records do not exists
}

}

public void insertData()
{
     if(findCount(1,"1")){//pass values
       //Write down your insert logic
     }else{
      JOptionPane.showMessageDialog(rootPane, "Records Already Exists");
     }
}

Note: Friend in Your Example you have not written the insert logic. only select was there

Upvotes: 0

B11
B11

Reputation: 223

or you could use a count:

String query = "select count(*) 
from dbo.soil_det where rm_id = ? and box_no = ?";

then after executing the query you get the count with

                   rs.getInt(1)

using that you can decide which info to show to the user

Upvotes: 0

Related Questions