CODEWITHSUNDEEP
javasqljdbcucanaccess
Faisal_mthe
Faisal_mthe

Reputation: 83

How to get data from DB table between two specific dates(specified by user) by SQL statement?

Here is my code..I got error

"UCanAccess error - net.ucanaccess.jdbc.UcanaccessSQLException: incompatible data type in operation: at following line..

PreparedStatement pStmt = conn.prepareStatement(sql)

public void showCeilingMaterials(Site_Details site_detail) 
{
    String sql="SELECT SiteName, SUM(PlanTileQuantity), SUM(PlanTilePrice), SUM(PellingQuantity),SUM(PellingPrice), SUM(PowderQuantity),SUM(PowderPrice),SUM(LpattiQuantity),SUM(LpattiPrice),LpattiSize,SUM(CeilingTotalPrice) FROM CeilingMaterials Where SiteName='?' AND Date<='?' AND Date>=?";
        try (PreparedStatement pStmt = conn.prepareStatement(sql)) {
            SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");
            java.util.Date parsed = format.parse(site_detail.getStartDate());
            java.sql.Date sql_date1 = new java.sql.Date(parsed.getTime());
            format.format(sql_date1);
            java.util.Date parsed1 = format.parse(site_detail.getEndDate());
            java.sql.Date sql_date2 = new java.sql.Date(parsed1.getTime());
            format.format(sql_date2);
            pStmt.setString(1, site_detail.getSiteName());
            pStmt.setDate(2, sql_date1);
            pStmt.setDate(2,sql_date2);
            ResultSet rs= pStmt.executeQuery();
            while(rs.next()){
                showCeil.setSiteName(rs.getString("SiteName"));
                showCeil.setTileQuantity(rs.getString("PlanTileQuantity"));
                showCeil.setTilePrice(rs.getString("PlanTilePrice"));
                showCeil.setPellingQuantity(rs.getString("PellingQuantity"));
                showCeil.setPellingPrice(rs.getString("PellingPrice"));
                showCeil.setLpattiQuantity(rs.getString("LpattiQuantity"));
                showCeil.setLpattiPrice(rs.getString("LpattiPrice"));
                showCeil.setLpattiSize(rs.getString("LpattiSize"));
                showCeil.setPowderQuantity(rs.getString("PowderQuantity"));
                showCeil.setPowderPrice(rs.getString("PowderPrice"));
                showCeil.setTotalCeilingPrice(rs.getString("CeilingTotalPrice"));
                show_ceil_w=new Site_Details_Show_Interface();
                show_ceil_w.showGui(showCeil);
            }
        }
    }

Upvotes: 1

Views: 408

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

The SQL command text for a parameterized query must never include quotes (or other delimiters) around parameter placeholders. You have the following, which is incorrect:

... Where SiteName='?' AND Date<='?' AND Date>=?

Specifically, it is Date<='?' that is causing the error cited in the question, although the error message is slightly different in newer versions of UCanAccess (in this case, v3.0.2):

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 incompatible data types in combination

Instead, you need to have

... Where SiteName=? AND [Date]<=? AND [Date]>=?

[Note that Date is a reserved word (built-in function name) in Access so you should use square brackets around it if you want to refer to a column named Date.]

Once that error is corrected then other errors in your SQL manifest themselves. The next one you will have to address is:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 expression not in aggregate or GROUP BY columns: PUBLIC.CEILINGMATERIALS.SITENAME

because you have included SiteName in your list of columns to be returned, but it is not part of an aggregate function (e.g., MIN(), MAX()) or a GROUP BY clause. You have that same problem with LpattiSize as well.

You also have

pStmt.setDate(2, sql_date1);
pStmt.setDate(2,sql_date2);

where you have assigned a value to parameter #2 twice (so parameter #3 has no value).

Finally, be aware that when you SUM() a column without providing an alias, as in

SELECT ... SUM(PlanTileQuantity), ...

the resulting column will not be named "PlanTileQuantity". UCanAccess will assign it an arbirtary column name like "C1", "C2", etc.. It is always best to explicitly assign an alias, e.g.,

SELECT ... SUM(PlanTileQuantity) AS SumOfPlanTileQuantity, ...

Upvotes: 2

Related Questions