can't insert data in a sqlite3 database in qt 6

burrito1111

I have a test.db database on my project directory, which I'm trying to insert data into. The database is connected, but I can't seem to insert data in it. The query is not executed at all (it seems), since the qDebug shows "Bad".

QSqlDatabase connectDB(){
    QSqlDatabase db= QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("test.db");
    return db;

}

void planner::on_dataSend_clicked()
{
    QSqlDatabase datba = connectDB();
    if (datba.open()){
        qDebug()<< "DB Suc";
    } else{
        qDebug() << "DB Fail";
    }
    QString what = ui->addPlan->text();
    QSqlQuery qry;
    qry.prepare("insert into [plan] values :what");
    qry.bindValue(":what",what);
    if (qry.exec()){
             qDebug()<< "Good";
             qry.clear();

          } else{
             qDebug()<<"Bad";
             qDebug()<<qry.lastError();
             qry.clear();

          }
    datba.close();
    ui->addPlan->clear();


}

QSqlError shows "parameter count mismatch" . I am using DB Browser for SQLite.

burrito1111

@Christian-Ehrlicher thanks for the heads up. I will try using the prepare statement correctly. will get back to you if i run into issues.

 q.prepare("insert into plan(plan) values (:plan)");
    q.bindValue(":plan",what);
    if (q.exec()){
        qDebug()<< "Ok";
    }

works now.

@JonB thanks a lot for the insight too.

thanks a ton to both of you. hope you have a great day <3

Christian Ehrlicher

@burrito1111 said in can't insert data in a sqlite3 database in qt 6:

qry.prepare("insert into [plan] values :what");

Please check the return value of the prepare statement

burrito1111

This post is deleted!

burrito1111

@Christian-Ehrlicher it returns false if I qDebug()<<query.prepare("INSERT INTO plan VALUES (:plan)");
if that's what u meant.

JonB

@burrito1111
That is what he meant, and it means SQLite does not accept it.
Your syntax is why you get QSqlError shows "parameter count mismatch" . from ("insert into [plan] values :what)
So fix your statement, before you can insert anything.

burrito1111

@JonB hi jonb. yes i think it was the syntax too. I used the q.exec directly and it worked.

QString what = ui->addPlan->text();
    QSqlQuery q;
    if(q.exec("insert into plan(plan) values ('"+what+"')")){
        qDebug()<< "Ok";
    };

Christian Ehrlicher

@burrito1111 said in can't insert data in a sqlite3 database in qt 6:

and it worked.

Because now the syntax is correct. But it will fail as soon as 'what' contains e.g. a ') or similar. Use a prepared statement and fix your syntax.

JonB

@burrito1111
That means/implies it does not accept a bound value (like :what) in the values segment if that is so. Which I don't think is the case. Try again now that you know what the correct syntax for insert is, which you had wrong earlier.

burrito1111

@Christian-Ehrlicher thanks for the heads up. I will try using the prepare statement correctly. will get back to you if i run into issues.

 q.prepare("insert into plan(plan) values (:plan)");
    q.bindValue(":plan",what);
    if (q.exec()){
        qDebug()<< "Ok";
    }

works now.

@JonB thanks a lot for the insight too.

thanks a ton to both of you. hope you have a great day <3

Christian Ehrlicher

But you still should check the return values of prepare() and exec() and print out the error string when there is an error.