CODEWITHSUNDEEP
javasqlms-access
Will_K
Will_K

Reputation: 25

SQL error Too few parameters

I'm having an sql problem. I writing a java application on top of a Access database. It's a search query for several fields and I know the error is in the part where I need to calculate the age of a person when he or she went missing. I'm returning a tablemodel in my method so i need to do the calculations in my query. My latest atempt to make it work is this:

public TableModel UpdateTable(String dossiernr, String naam, String voornaam,
    String startleeftijd, String eindleeftijd, String dossierjaar, String geslacht)
{
    TableModel tb = null;
    String sql= "SELECT [Nr dossier],[Annee],[Nom],[Prenom],[Disparu le],[Ne le],[Sexe], DATEDIFF('yyyy',[Ne le],[Disparu le]) - iif(DATEADD('yyyy', DATEDIFF('yyyy',[Ne le],[Disparu le]),"
        + "[Ne le])>[Disparu le],1,0) AS Age FROM TotalTable "
        + "WHERE [Nr dossier] LIKE ? AND [Nom] LIKE ? AND [Prenom] LIKE ? AND [Annee] LIKE ? AND Age >= ? AND Age <= ? AND [Sexe] LIKE ?;";

    try
    {
        PreparedStatement pstatement;
        Connection connection = PersistentieController.getInstance().getConnection();

        pstatement = initStatement(connection,sql);
        pstatement.setString(1, "%" + dossiernr + "%");
        pstatement.setString(2, "%" + naam + "%");
        pstatement.setString(3, "%" + voornaam + "%");
        pstatement.setString(4, "%" + dossierjaar + "%");
        pstatement.setString(5, startleeftijd);
        pstatement.setString(6, eindleeftijd);
        pstatement.setString(7, "%" + geslacht + "%");
        rs=pstatement.executeQuery();
        tb = DbUtils.resultSetToTableModel(rs);
        pstatement.close(); 
    }//einde try
    catch (SQLException e)
    {
        e.printStackTrace();
    } //einde catch

    return tb;
}

When i run it, i get following error:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8.

I only work with 7 parameters and don't get why he's asking for 8. Thanks

Upvotes: 2

Views: 2811

Answers (1)

HansUp
HansUp

Reputation: 97101

You count 7 parameters in your WHERE clause. Unfortunately, the Access db engine treats Age as another parameter in that situation, so it thinks you have 8 parameters instead of only 7.

To understand why, start with this query which runs without error with my Access database:

SELECT some_text AS foo
FROM tblFoo
WHERE some_text Is Not Null;

However, when attempting to use the alias instead of the field name in the WHERE clause, Access prompts me to supply a value for foo because it treats it as a parameter:

SELECT some_text AS foo
FROM tblFoo
WHERE foo Is Not Null;

Access limits your ability to re-use alias names later in a query. In certain cases, it will accept the alias, but yours is not one of those cases.

You could define the alias in a subquery. Then the db engine will recognize it correctly when you reference the subquery's alias in the parent query.

If possible, test your SQL statements directly in Access. If they fail, that effort will give you the best chance to determine why.

Upvotes: 3

Related Questions