Click here to Skip to main content
14,935,198 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello everyone i am stuck at this for quite some time i am new to java help would be very appreciated
Basically i am getting result of all customers between specified dates
bill_issued_on datatype=date\time in access
Java
String sDate=( (JTextField)startDateChooser.getDateEditor().getUiComponent() ).getText() ;
        String eDate=( (JTextField)endDateChooser.getDateEditor().getUiComponent() ).getText() ;
        DateFormat format = new SimpleDateFormat("YYYY-MM-dd ");
        Date sDateFinal = null;
        Date eDateFinal = null;
        System.out.println(sDate+"/"+eDate);
        try {
            sDateFinal = format.parse(sDate);
            eDateFinal = format.parse(eDate);
        } catch (ParseException ex) {
            Logger.getLogger(MonthlyRecord.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        String qry = "SELECT * FROM records where bill_issued_on BETWEEN '" + sDateFinal+ "' AND '" +eDateFinal+"'";
        System.out.println(qry);
         try {
            db.setStmt((Statement) db.getCon().createStatement());
            ResultSet rs = ((java.sql.Statement) db.getStmt()).executeQuery(qry);
            if(rs.next()){
                jTable1.setModel(DbUtils.resultSetToTableModel(rs));
                System.out.println("I was here");
            }
            
            else{
                System.out.println("False");
            }


        } catch (Exception e) {
            System.out.println("Problem in query \n" + e);
        }


output:
 SELECT * FROM records where bill_issued_on BETWEEN '2015-01-01' AND '2015-12-28'
Problem in query 
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Posted
Updated 2-Jan-15 4:14am
v2
Comments
jaket-cp 2-Jan-15 10:33am
   
It could be the single quote (') is being used instead of hash (#) used for the date
Check out this link, it may be of some help:
https://support.office.com/client/Using-dates-as-criteria-in-Access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762

First of all, you are applying BETWEEN to strings, but apparently you need to apply it to dates. Please see, for example: http://www.techonthenet.com/sql/between.php[^].

Worse, the way you compose your query is wrong from the very beginning. Your query is composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection. The text taken fro UI can be anything, including… fragment of SQL code.

This is how it works: http://xkcd.com/327[^].

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection[^].

Please see:
https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java[^],
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html[^],
https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet[^].

Please see my past answers for some more detail (they are on .NET, so for Java-specific directions, see the links above):
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA
   
v3
Comments
Member 11348104 2-Jan-15 11:10am
   
can you guide me how to get date from jdatechooser
   
Sorry for some possible confusion; please see the update to my answer; I replaced some part of the post, added Java-specific links.
—SA
Member 11348104 2-Jan-15 12:27pm
   
i am doing this now
Date dt=startDateChooser.getDate();
Date dt1=endDateChooser.getDate();
java.sql.Date sqlDateObjectStart = new java.sql.Date(dt.getTime());
java.sql.Date sqlDateObjectEnd = new java.sql.Date(dt1.getTime());


does this help?
Member 11348104 2-Jan-15 12:28pm
   
i am running the same query in ms access it is giving me fine result but cannot handle this in java
Maciej Los 2-Jan-15 15:13pm
   
Good advice, Sergey, 5ed!
There is only one small "but"... See my answer ;)
   
Thank you, Maciej.
—SA
When you work with MS Access database, proper query is:
SQL
SELECT * FROM records where bill_issued_on BETWEEN #2015-01-01# AND #2015-12-28#


See this: Examples of using dates as criteria in Access queries[^]

BTW: i do not recommend to use SELECT *, because of query performance. Rather than it, use SELECT <Field_List>

Second tip: use parameters[^].
SQL
PARAMETERS [datefrom] DATE, [dateto] DATE;
SELECT <Field_list>
FROM TableName
WHERE DateField BETWEEN [datefrom] AND [dateto]


How to call it? See this: ADO.NET for the Java Programmer[^]

Good luck!
   
v4
Comments
   
Sure, a 5.
You are editing, hope you will fix the typo "reocmmend".
—SA
Maciej Los 2-Jan-15 15:23pm
   
Thank you, Sergey ;)
Fixed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900