trouble inserting a row in SQLite database, Error: " Parameter count mismatch"

[Geargate]

Hello, I have a program that can insert data into a database's table but I'm having trouble doing so, I've been trying to figure out what's wrong with my query but I can't find the error

bool PaymentSystem::addGroup(QString group, QString year)
{

    QSqlDatabase db;

    const QString DRIVER("QSQLITE");
    if(QSqlDatabase::isDriverAvailable(DRIVER)){
        qDebug("Driver disponible");
        db = QSqlDatabase::addDatabase(DRIVER);
        db.setDatabaseName("C:\\Users\\jesus\\Documents\\DB\\emeritusdb.db");
    }
    else {
        return false;
    }
    if(!db.open())
    {
        qWarning() << "ERROR: " << db.lastError();
        return false;
    }

    for(uint i = 0 ; i < groups.size(); i++)
    {
        if(groups[i].name == group && groups[i].year == year)
        {
            return false;
        }

    }

    QSqlQuery *query = new QSqlQuery(db);
    QSqlQuery queryInsert(db);
    try
    {
        query->prepare("SELECT * FROM grade WHERE name=(:year)");
        query->bindValue(":year", year);
        if(!query->exec())
        {
            qWarning() << "ERROR: " << query->lastError().text();
            return false;
        }


    } catch (std::exception)
    {
        return false;
    }

    int grade_id=0;

    while (query->next())
    {
        grade_id = query->value(0).toInt();

    }

    qDebug() << "is DB open? "<<db.isOpen();
    if (grade_id==0)
    {
      return false;
    }
    try
    {
        QString name= group;
        queryInsert.exec("PRAGMA foreign_keys = ON");
        queryInsert.prepare("INSERT INTO group(grade_id, name) VALUES(:grade_id, :name)");

        queryInsert.bindValue(":name", name);
        queryInsert.bindValue(":grade_id", grade_id);


        if(!queryInsert.exec())
        {
            qDebug()<<"bound value 0: "<<queryInsert.boundValue(0);
            qDebug()<<"bound value 1: "<<queryInsert.boundValue(1);
            qDebug()<<"Executed query: "<<queryInsert.executedQuery();
            qDebug()<<"Last query: "<<queryInsert.lastQuery();
            qWarning() << "Error: " <<queryInsert.lastError().text();
            return false;
        }

    } catch (std::exception& e)
    {
        return false;
    }

    Group g;
    g.name = group;
    g.year = year;
    groups.push_back(g);
    return true;
}

In the output, I get the following

is DB open?  true
bound value 0:  QVariant(int, 25)
bound value 1:  QVariant(QString, "ggggggg")
Executed query:  "INSERT INTO group(grade_id, name) VALUES(:grade_id, :name)"
Last query:  "INSERT INTO group(grade_id, name) VALUES(:grade_id, :name)"
Error:  " Parameter count mismatch"

the bound values are correct however I don't know why I'm getting the <Error: " Parameter count mismatch">

My database table is the following

CREATE TABLE [group] (
    id       INTEGER NOT NULL
                     PRIMARY KEY AUTOINCREMENT
                     UNIQUE,
    grade_id INTEGER DEFAULT (0),
    name     TEXT    NOT NULL
                     DEFAULT 'grupo',
    FOREIGN KEY (
        grade_id
    )
    REFERENCES grade (id) ON DELETE SET NULL
);

What am I doing wrong? Any help would be appreciated

[SGaist]

Hi and welcome to devnet,

What version of Qt are you using ?

You can also use positional placeholders.

[Geargate]

@sgaist Hi, I'm using Qt 4.9.2. I substituted my query with positional placeholders like this

queryInsert.exec("PRAGMA foreign_keys = ON");
queryInsert.prepare("INSERT INTO group(grade_id, name) VALUES(?, ?)");
queryInsert.addBindValue(grade_id);     
queryInsert.addBindValue(name);        

however I'm getting the same error

is DB open?  true
bound value grade_id:  QVariant(int, 25)
bound value name:  QVariant(QString, "some name")
Executed query:  "INSERT INTO group(grade_id, name) VALUES(:a, :bb)"
Last query:  "INSERT INTO group(grade_id, name) VALUES(?, ?)"
Error:  "Parameter count mismatch"

[Geargate]

I finally got it to work, the issue was that "group" is a reserved keyword, changing "group" to "[group]" in my original query solved the issue

queryInsert.prepare("INSERT INTO [group](grade_id, name) VALUES(:grade_id, :name)");

[SGaist]

Tricky one !

4.9.2 ? This one doesn't exists, the 4 series ended at 4.8.7.

Anyway, glad you found out and thanks for sharing !

Since you have it working now please mark the thread as solved using the "Topic Tools" button so that other forum users may know a solution has been found :)