hehexd123 hehexd123 - 19 days ago 7
SQL Question

Using jDateChooser in SQL query

I'm working on a little exercise, using ORACLE HR database and Netbeans, and i want to calculate difference between two jDateChooser values using a search button, here is a screenshot of my jFrame which explains everything, including my output error :

http://prnt.sc/d7k538

And here is my code :

import java.util.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;


public class Exercise extends javax.swing.JFrame {

Connection Con = myConn.getCon();
Statement St = null;
ResultSet Rs = null;

String tab[] = {"LAST_NAME", "FIRST_NAME", "JOB_TITLE", "DEPARTMENT_NAME", "SALARY"};
DefaultTableModel dtm = new DefaultTableModel(tab, 0);

public Exercise() {
initComponents();

jTable1.setModel(dtm);

fill_combo_job();
fill_combo_dept();

int Rc = dtm.getRowCount();
row_count_label.setText(Integer.toString(Rc));
}

public void fill_combo_job(){

try{
//Getting data from table JOBS

St = Con.createStatement();
Rs = St.executeQuery("SELECT DISTINCT JOB_ID FROM JOBS");

//Filling our jcombobox

while(Rs.next()){
job_id_combo.addItem(Rs.getString("JOB_ID"));
}

}catch(Exception ex){

System.out.println(ex.toString());
}
}

private void fill_combo_dept(){

try{

St = Con.createStatement();
Rs = St.executeQuery("SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL ORDER BY DEPARTMENT_ID");

while(Rs.next()){
dept_id_combo.addItem(Rs.getString("DEPARTMENT_ID"));
}

}catch(Exception ex){

System.out.println(ex.toString());
}

}

private void search_btn(){

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
String date1 = df.format(jDateChooser1.getDate());
String date2 = df.format(jDateChooser2.getDate());



try{

St = Con.createStatement();
Rs = St.executeQuery("SELECT e.LAST_NAME, e.FIRST_NAME, j.JOB_TITLE, d.DEPARTMENT_NAME, e.SALARY FROM EMPLOYEES e, JOBS j, DEPARTMENTS d "
+ "WHERE j.JOB_ID = e.JOB_ID "
+ " AND d.DEPARTMENT_ID = e.DEPARTMENT_ID"
+ " AND j.JOB_ID = "+job_id_combo.getSelectedItem().toString()+" AND e.DEPARTMENT_ID = "+dept_id_combo.getSelectedItem().toString()
+ " AND HIRE_DATE BETWEEN TO_CHAR ('"+date1+"', 'dd/MM/yyyy') AND TO_CHAR('"+date2+"', 'dd/MM/yyyy')"
+ " AND SALARY > "+salary_field.getText());

while(Rs.next()){

dtm.addRow(new Object[]{Rs.getString("LAST_NAME"), Rs.getString("FIRST_NAME"),
Rs.getString("JOB_TITLE"), Rs.getString("DEPARTMENT_NAME"), Rs.getInt("SALARY")});
}

Con.close();
St.close();
Rs.close();


}catch(Exception ex){

System.out.println(ex.toString());
}


}

public void reset_form(){

job_id_combo.setSelectedIndex(0);
dept_id_combo.setSelectedIndex(0);
salary_field.setText("");


}

Joe Joe
Answer

This error means there is something wrong with your query. Typo, incorrect quotes, incorrect capitalization.

Comments