QsqlQuery SQLite insert statement param count mismatch

[NR5P]

INSERT INTO applicatinForPayments is where I'm running into an issue. If I don't include anything after customerName I have no issues and query is inserted. If I include anything past customerName I get mismatched param count error. It does not appear from looking at the documentation that I'm hitting anywhere near a maximum # of arguments or columns for an sqlite insertion. I don't see anything wrong with my datatypes. Literately one more column is causing the issue.

Here is the part that's causing an issue and the second is the entire .cpp file

{
    QString projectCode = applicationForPayment.projectCode;
    QString applicationNumber = applicationForPayment.applicationNumber;
    QString applicationDate = applicationForPayment.applicationDate.toString("MM/dd/yyyy");
    QString contractDate = applicationForPayment.contractDate.toString("MM/dd/yyyy");
    QString periodFrom = applicationForPayment.periodFrom.toString("MM/dd/yyyy");
    QString periodTo = applicationForPayment.periodTo.toString("dd/MM/yyyy");
    QString customerName = applicationForPayment.customerName;
    QString customerAddress = applicationForPayment.customerAddress;
    QString customerCity = applicationForPayment.customerCity;
    QString customerState = applicationForPayment.customerState;
    QString customerZip = applicationForPayment.customerZip;
    QString contractorName = applicationForPayment.contractorName;
    QString contractorAddress = applicationForPayment.contractorAddress;
    QString contractorCity = applicationForPayment.contractorCity;
    QString contractorState = applicationForPayment.contractorState;
    QString contractorZip = applicationForPayment.contractorZip;
    QString lastProjectDate = applicationForPayment.applicationDate.toString("dd/MM/yyyy");
    if (newProject)
    {
        QSqlQuery query;
        query.prepare("INSERT INTO projects (name, address, lastProjectDate) VALUES(:customerName, :customerAddress, :lastProjectDate);");
        query.bindValue(":customerName",customerName);
        query.bindValue(":customerAddrss",customerAddress);
        query.bindValue(":lastProjectDate",lastProjectDate);
        query.exec();

        int lastInsertId = query.lastInsertId().toInt();
        QSqlQuery query2;
        //query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName, contractorAddress, contractorCity, contractorState, contractorZip, projectId) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress, :customerCity, :customerState, :customerZip, :contractorName, :contractorAddress, :contractorCity, :contractorState, :contractorZip, :projectId);");
          query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress);");
        query2.bindValue(":projectCode", projectCode);
        query2.bindValue(":applicationNumber", applicationNumber);
        query2.bindValue(":applicationDate", applicationDate);
        query2.bindValue(":contractDate", contractDate);
        query2.bindValue(":periodFrom", periodFrom);
        query2.bindValue(":periodTo", periodTo);
        query2.bindValue(":customerName", customerName);
        query2.bindValue(":customerAddress", customerAddress);
        /*
        query2.bindValue(":customerCity", customerCity);
        query2.bindValue(":customerState", customerState);
        query2.bindValue(":customerZip", customerZip);
        query2.bindValue(":contractorName", contractorName);
        query2.bindValue(":contractorAddress", contractorAddress);
        query2.bindValue(":contractorCity", contractorCity);
        query2.bindValue(":contractorState", contractorState);
        query2.bindValue(":contractorZip", contractorZip);
        query2.bindValue(":projectId", lastInsertId);
        */
        query2.exec();
        QString localLastError = query2.lastError().text();
        QMessageBox msg;
        msg.setText(QString::number(lastInsertId) + localLastError);
        msg.exec();

#include "database.h"
#include "project.h"

#include <QFile>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDir>
#include <QVariant>
#include <QDate>
#include <QVariant>
#include <QDebug>
#include <QMessageBox>

Database::Database()
{
        QDir path;
        QString dbPath = path.currentPath() + "mydb.db";
        mydb = QSqlDatabase::addDatabase("QSQLITE");
        mydb.setDatabaseName(dbPath);
        mydb.open();
        QSqlQuery query0("PRAGMA foreign_keys = ON");
        QSqlQuery query1("CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT, address TEXT, lastProjectDate TEXT)");
        QSqlQuery query2("CREATE TABLE applicationForPayments (id INTEGER PRIMARY KEY, projectCode TEXT, applicationNumber TEXT, applicationDate TEXT,"
                         "contractDate TEXT, periodFrom TEXT, periodTo TEXT, customerName TEXT, customerAddress TEXT, customerCity TEXT, customerState TEXT,"
                         "customerZip TEXT, contractorName TEXT, contractorAddress TEXT, contractorCity TEXT, contractorState TEXT, contractorZip TEXT,"
                         "projectId INTEGER, FOREIGN KEY (projectId) REFERENCES projects(id))");
        QSqlQuery query3("CREATE TABLE settings (id INTEGER PRIMARY KEY, darkMode INTEGER)");
}

QList<Project> Database::getProjectsFromDb()
{
    QList<Project> projects;
    QSqlQuery query("SELECT id, name, address, lastProjectDate FROM projects");
    while(query.next())
    {
        Project project = Project(query.value(0).toInt(), query.value(1).toString(), query.value(2).toString(), query.value(3).toString());
        projects.append(project);
    }
    return projects;
}

QList<ApplicationForPayment> Database::getApplicationsForPayment(int projectId)
{
    QList<ApplicationForPayment> applicationForPayments;
    QSqlQuery query("SELECT projectId, projectCode, applicationNumber, applicationDate, contractDate, periodFrom"
                    "periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName,"
                    "contractorAddress, contractorCity, contractorState, contractorZip"
                    "FROM applicationForPayments WITH projectId = " + QString::number(projectId));
    while(query.next())
    {
        ApplicationForPayment applicationForPayment = ApplicationForPayment(query.value(0).toInt(), query.value(1).toString(), query.value(2).toString(), query.value(3).toString(),
                                                                            query.value(4).toString(), query.value(5).toString(), query.value(6).toString(), query.value(7).toString(),
                                                                            query.value(8).toString(), query.value(9).toString(), query.value(10).toString(), query.value(11).toString(),
                                                                            query.value(12).toString(), query.value(13).toString(), query.value(14).toString(), query.value(15).toString(),
                                                                            query.value(16).toString());
        applicationForPayments.append(applicationForPayment);
    }
    return applicationForPayments;
}

void Database::addApplicationForPayment(ApplicationForPayment applicationForPayment, bool newProject, int projectId)
{
    QString projectCode = applicationForPayment.projectCode;
    QString applicationNumber = applicationForPayment.applicationNumber;
    QString applicationDate = applicationForPayment.applicationDate.toString("MM/dd/yyyy");
    QString contractDate = applicationForPayment.contractDate.toString("MM/dd/yyyy");
    QString periodFrom = applicationForPayment.periodFrom.toString("MM/dd/yyyy");
    QString periodTo = applicationForPayment.periodTo.toString("dd/MM/yyyy");
    QString customerName = applicationForPayment.customerName;
    QString customerAddress = applicationForPayment.customerAddress;
    QString customerCity = applicationForPayment.customerCity;
    QString customerState = applicationForPayment.customerState;
    QString customerZip = applicationForPayment.customerZip;
    QString contractorName = applicationForPayment.contractorName;
    QString contractorAddress = applicationForPayment.contractorAddress;
    QString contractorCity = applicationForPayment.contractorCity;
    QString contractorState = applicationForPayment.contractorState;
    QString contractorZip = applicationForPayment.contractorZip;
    QString lastProjectDate = applicationForPayment.applicationDate.toString("dd/MM/yyyy");
    if (newProject)
    {
        QSqlQuery query;
        query.prepare("INSERT INTO projects (name, address, lastProjectDate) VALUES(:customerName, :customerAddress, :lastProjectDate);");
        query.bindValue(":customerName",customerName);
        query.bindValue(":customerAddrss",customerAddress);
        query.bindValue(":lastProjectDate",lastProjectDate);
        query.exec();

        int lastInsertId = query.lastInsertId().toInt();
        QSqlQuery query2;
        //query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName, contractorAddress, contractorCity, contractorState, contractorZip, projectId) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress, :customerCity, :customerState, :customerZip, :contractorName, :contractorAddress, :contractorCity, :contractorState, :contractorZip, :projectId);");
          query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress);");
        query2.bindValue(":projectCode", projectCode);
        query2.bindValue(":applicationNumber", applicationNumber);
        query2.bindValue(":applicationDate", applicationDate);
        query2.bindValue(":contractDate", contractDate);
        query2.bindValue(":periodFrom", periodFrom);
        query2.bindValue(":periodTo", periodTo);
        query2.bindValue(":customerName", customerName);
        query2.bindValue(":customerAddress", customerAddress);
        /*
        query2.bindValue(":customerCity", customerCity);
        query2.bindValue(":customerState", customerState);
        query2.bindValue(":customerZip", customerZip);
        query2.bindValue(":contractorName", contractorName);
        query2.bindValue(":contractorAddress", contractorAddress);
        query2.bindValue(":contractorCity", contractorCity);
        query2.bindValue(":contractorState", contractorState);
        query2.bindValue(":contractorZip", contractorZip);
        query2.bindValue(":projectId", lastInsertId);
        */
        query2.exec();
        QString localLastError = query2.lastError().text();
        QMessageBox msg;
        msg.setText(QString::number(lastInsertId) + localLastError);
        msg.exec();

    } else {
        QSqlQuery query2;
        query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName, contractorAddress, contractorCity, contractorState, contractorZip, projectId) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress, :customerCity, :customerState, :customerZip, :contractorName, :contractorAddress, :contractorCity, :contractorState, :contractorZip, :projectId);");
        query2.bindValue(":projectCode", projectCode);
        query2.bindValue(":applicationNumber", applicationNumber);
        query2.bindValue(":applicationDate", applicationDate);
        query2.bindValue(":contractDate", contractDate);
        query2.bindValue(":periodFrom", periodFrom);
        query2.bindValue(":periodTo", periodTo);
        query2.bindValue(":customerName", customerName);
        query2.bindValue(":customerAddress", customerAddress);
        query2.bindValue(":customerCity", customerCity);
        query2.bindValue(":customerState", customerState);
        query2.bindValue(":customerZip", customerZip);
        query2.bindValue(":contractorName", contractorName);
        query2.bindValue(":contractorAddress", contractorAddress);
        query2.bindValue(":contractorCity", contractorCity);
        query2.bindValue(":contractorState", contractorState);
        query2.bindValue(":contractorZip", contractorZip);
        query2.bindValue(":projectId", projectId);
        query2.exec();

    }
}

[NR5P]

btw way just to clarify if I remove a customerAddress from the INSERT statement and I comment out the customerAddress bind along with the rest of the code that is commented out this query works. It is not working as is

[SGaist]

Hi,

@NR5P said in QsqlQuery SQLite insert statement param count mismatch:

customerAddrss

You have a typo between your query and binding statementX

[NR5P]

Oh man I can't believe I didn't see that. That query was actually not having an issue, it was the next one. But maybe it's causing problems. I will revaluate that

[NR5P]

it was a problem, but did not resolve this issue. It's driving me nuts, this does not work

        int lastInsertId = query.lastInsertId().toInt();
        QSqlQuery query2;
        //query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName, contractorAddress, contractorCity, contractorState, contractorZip, projectId) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress, :customerCity, :customerState, :customerZip, :contractorName, :contractorAddress, :contractorCity, :contractorState, :contractorZip, :projectId);");
          query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress);");
        query2.bindValue(":projectCode", projectCode);
        query2.bindValue(":applicationNumber", applicationNumber);
        query2.bindValue(":applicationDate", applicationDate);
        query2.bindValue(":contractDate", contractDate);
        query2.bindValue(":periodFrom", periodFrom);
        query2.bindValue(":periodTo", periodTo);
        query2.bindValue(":customerName", customerName);
        query2.bindValue(":customerAddress", customerAddress);
        /*
        query2.bindValue(":customerCity", customerCity);
        query2.bindValue(":customerState", customerState);
        query2.bindValue(":customerZip", customerZip);
        query2.bindValue(":contractorName", contractorName);
        query2.bindValue(":contractorAddress", contractorAddress);
        query2.bindValue(":contractorCity", contractorCity);
        query2.bindValue(":contractorState", contractorState);
        query2.bindValue(":contractorZip", contractorZip);
        query2.bindValue(":projectId", lastInsertId);
        */
        query2.exec();
        QString localLastError = query2.lastError().text();
        QMessageBox msg;
        msg.setText(QString::number(lastInsertId) + localLastError);
        msg.exec();

but this does

        int lastInsertId = query.lastInsertId().toInt();
        QSqlQuery query2;
        //query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress, customerCity, customerState, customerZip, contractorName, contractorAddress, contractorCity, contractorState, contractorZip, projectId) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress, :customerCity, :customerState, :customerZip, :contractorName, :contractorAddress, :contractorCity, :contractorState, :contractorZip, :projectId);");
          query2.prepare("INSERT INTO applicationForPayments (projectCode, applicationNumber, applicationDate, contractDate, periodFrom, periodTo, customerName, customerAddress) VALUES(:projectCode, :applicationNumber, :applicationDate, :contractDate, :periodFrom, :periodTo, :customerName, :customerAddress);");
        query2.bindValue(":projectCode", projectCode);
        query2.bindValue(":applicationNumber", applicationNumber);
        query2.bindValue(":applicationDate", applicationDate);
        query2.bindValue(":contractDate", contractDate);
        query2.bindValue(":periodFrom", periodFrom);
        query2.bindValue(":periodTo", periodTo);
        query2.bindValue(":customerName", customerName);
        query2.bindValue(":customerAddress", customerAddress);
        /*
        query2.bindValue(":customerCity", customerCity);
        query2.bindValue(":customerState", customerState);
        query2.bindValue(":customerZip", customerZip);
        query2.bindValue(":contractorName", contractorName);
        query2.bindValue(":contractorAddress", contractorAddress);
        query2.bindValue(":contractorCity", contractorCity);
        query2.bindValue(":contractorState", contractorState);
        query2.bindValue(":contractorZip", contractorZip);
        query2.bindValue(":projectId", lastInsertId);
        */
        query2.exec();
        QString localLastError = query2.lastError().text();
        QMessageBox msg;
        msg.setText(QString::number(lastInsertId) + localLastError);
        msg.exec();

[JonB]

@NR5P
I copied & pasted each of your examples, then compared them with a differencer. They are identical!

What makes you think one works while the other does not? Since your code shows the message box unconditionally, if it succeeds you won't know and it may display the query2.lastError() left over from the previous unsuccessful call? You must check the return result of query2.exec()?

[NR5P]

yes I mistakenly pasted the same code. If I add one more param after customer name it does not work though. If I remove customerAddress from the prepare and customerAddress from the bind, it works.

I'm not using the msg anywhere else, but I checked the result of query.exec() and it is false when I have any param after customerName. Of course I've also been checking the table.

[hskoglund]

Hi, just a guess but you might have run into a limit of SQLite, in Qt's source you'll find this in qtbase/src/3rdparty/sqlite/sqlite3.h:

#define SQLITE_LIMIT_VARIABLE_NUMBER      9

Granted you hit the limit after 7 columns, not 9, but maybe they're connected...