Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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



  • 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


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    What version of Qt are you using ?

    You can also use positional placeholders.



  • @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"


  • 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)");

  • Lifetime Qt Champion

    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 :)


Log in to reply