Unable to get the result of query

Question

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 2-Jan-15 4:11am

Member 11348104

Updated 2-Jan-15 4:14am

v2

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sergey Alexandrovich Kryukov · Answer 1 · 2015-01-02T04:32:00

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

Maciej Los · Answer 2 · 2015-01-02T09:10:00

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!

Unable to get the result of query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0