"Parameter count mismatch" error during the execution of my query.

ealione

I am receiving a "Parameter count mismatch" error when running the function below

QList<QObject*> DatabaseHandler::ListBudget(const QString name, const QString nameFilterType, const QString nameFilterConnection,
                                 const QString description, const QString descriptionFilterType, const QString descriptionFilterConnection,
                                 const QDateTime creationTime, const QString creationTimeFilterType, const QString creationTimeFilterConnection,
                                 const QDateTime expiryFrom, const QDateTime expiryTo, const QString expiryFilterConnection,
                                 const bool expired, const QString expiredFilterConnection,
                                 const int value, const QString valueFilterType)
{
    QSqlQuery query;
    query.setForwardOnly(true);

    QString queryString;

    QString headerPart;
    QString namePart;
    QString descriptionPart;
    QString creationTimePart;
    QString expiryFromPart;
    QString expiredPart;
    QString valuePart;
    QString footerPart;

    // Header part.
    headerPart = "SELECT * FROM glf_budget WHERE";

    // Name part.
    if (nameFilterType == QLatin1String("is equal"))
        namePart = " (glb_name = :glb_name1 OR :glb_name2 IS NULL) " % nameFilterConnection;
    else if (nameFilterType == QLatin1String("contains"))
        namePart = " (glb_name LIKE %:glb_name1% OR :glb_name2 IS NULL) " % nameFilterConnection;
    else
        namePart = " (glb_name NOT LIKE %:glb_name1% OR :glb_name2 IS NULL) " % nameFilterConnection;

    // Description part.
    if (descriptionFilterType == QLatin1String("is equal"))
        descriptionPart = " (glb_description = :glb_description1 OR :glb_description2 IS NULL) " % descriptionFilterConnection;
    else if (descriptionFilterType == QLatin1String("contains"))
        descriptionPart = " (glb_description LIKE %:glb_description1% OR :glb_description2 IS NULL) " % descriptionFilterConnection;
    else
        descriptionPart = " (glb_description NOT LIKE %:glb_description1% OR :glb_description2 IS NULL) " % descriptionFilterConnection;

    // CreationTime part.
    if (creationTimeFilterType == QLatin1String("before"))
        creationTimePart = " (glb_createdat <= :glb_createdat1 OR :glb_createdat2 IS NULL) " % creationTimeFilterConnection;
    else
        creationTimePart = " (glb_createdat >= :glb_createdat1 OR :glb_createdat2 IS NULL) " % creationTimeFilterConnection;

    // ExpiryFrom part.
    expiryFromPart = " (glb_expiresat BETWEEN (:expiryFrom1 AND :expiryTo1) OR (:expiryFrom2 IS NULL AND :expiryTo2 IS NULL)) " % expiryFilterConnection;

    // Expired part.
    expiredPart = " (glb_expired = :glb_expired1 OR :glb_expired2 IS NULL) " % expiredFilterConnection;

    // Value part.
    if (valueFilterType == QLatin1String("is equal"))
        valuePart = " (glb_value = :glb_value1 OR :glb_value2 IS NULL) AND";
    else if (descriptionFilterType == QLatin1String("bigger"))
        valuePart = " (glb_value > :glb_value1 OR :glb_valu2e IS NULL) AND";
    else
        valuePart = " (glb_value < :glb_value1 OR :glb_value2 IS NULL) AND";

    footerPart = " glb_owner = :glb_owner AND glb_deleted = 0";

    queryString = headerPart %
            namePart %
            descriptionPart %
            creationTimePart %
            expiryFromPart %
            expiredPart %
            valuePart %
            footerPart;

    query.prepare(queryString);

    query.bindValue(":glb_name1", name == "" ? QVariant(QVariant::String) : name);
    query.bindValue(":glb_name2", name == "" ? QVariant(QVariant::String) : name);
    query.bindValue(":glb_description1", description == "" ? QVariant(QVariant::String) : description);
    query.bindValue(":glb_description2", description == "" ? QVariant(QVariant::String) : description);
    query.bindValue(":glb_createdat1", creationTime.toMSecsSinceEpoch());
    query.bindValue(":glb_createdat2", creationTime.toMSecsSinceEpoch());
    query.bindValue(":expiryFrom1", expiryFrom.toMSecsSinceEpoch());
    query.bindValue(":expiryTo1", expiryTo.toMSecsSinceEpoch());
    query.bindValue(":expiryFrom2", expiryFrom.toMSecsSinceEpoch());
    query.bindValue(":expiryTo2", expiryTo.toMSecsSinceEpoch());
    query.bindValue(":glb_expired1", expired == true ? QVariant(QVariant::Bool) : expired);
    query.bindValue(":glb_expired2", expired == true ? QVariant(QVariant::Bool) : expired);
    query.bindValue(":glb_value1", value == 0 ? QVariant(QVariant::Int) : value);
    query.bindValue(":glb_value2", value == 0 ? QVariant(QVariant::Int) : value);
    query.bindValue(":glb_owner", QVariant(QVariant::String));

    if (!query.exec()) {
        qWarning("Could not get a list for all available budgets.");
        GlobalData::lastRegisterError = query.lastError().databaseText();
        qDebug() << query.lastError() << query.executedQuery();
    } else {
        QList<QObject*> budgets;
        while (query.next()) {
            Budget *budget = new Budget();
            budget->setName(query.value("glb_name").toString());
            budget->setDescription(query.value("glb_description").toString());

            QDateTime creationDate;
            creationDate.setDate(query.value("glb_createdat").toDate());
            budget->setCreationDate(creationDate);

            QDateTime expirationDate;
            expirationDate.setDate(query.value("glb_expiresat").toDate());
            budget->setExpirationDate(expirationDate);

            budget->setExpired(query.value("glb_expired").toBool());
            budget->setValue(query.value("glb_value").toInt());

            budgets.append(budget);
        }

        return budgets;
    }
}

The executed query looks like this

"SELECT * FROM glf_budget WHERE (glb_name NOT LIKE %?% OR ? IS NULL) AND (glb_description NOT LIKE %?% OR ? IS NULL) AND (glb_createdat >= ? OR ? IS NULL) AND (glb_expiresat BETWEEN (? AND ?) OR (? IS NULL AND ? IS NULL)) AND (glb_expired = ? OR ? IS NULL) AND (glb_value < ? OR ? IS NULL) AND glb_owner = ? AND glb_deleted = 0"

Why does this error occur when the number of bindValue commands is the same with the variables in my query?

Paul Colby

$ cat bind.cpp | sed -nre 's/.*[^:]:(\w+).*/\1/p' | sort -u | wc -l
16
$ cat bind.cpp | sed -nre 's/.*bindValue\(":(\w+)".*/\1/p' | sort -u | wc -l
15
$ cat bind.cpp | sed -nre 's/.*[^:]:(\w+).*/\1/p' | sort -u > a.txt
$ cat bind.cpp | sed -nre 's/.*bindValue\(":(\w+)".*/\1/p' | sort -u > b.txt 
$ diff -u a.txt b.txt 
--- a.txt       2016-04-18 07:52:31.491422946 +1000
+++ b.txt       2016-04-18 07:53:46.730939724 +1000
@@ -11,6 +11,5 @@
 glb_name1
 glb_name2
 glb_owner
-glb_valu2e
 glb_value1
 glb_value2

So it looks like you've misspelled glb_value2 as glb_valu2e in one place.

Cheers.

ealione

@Paul-Colby you are correct. I should not program after 11 :)
The error is still persisting after the change though. That is because of an extra parenthesis at (glb_expiresat BETWEEN (:expiryFrom1 AND :expiryTo1)