0

I am trying to take data from six fields on a form and pass the data the user enters into a database (A local SQLite3 database). The ID is an integer and the primary key and the rest are varchars of enough length. After reading a lot of related questions, I still cannot figure out where my code is going wrong. I have confirmation that the program is connected to the database, but I keep getting 'parameter count mismatch'. Where am I going wrong? I'm using Qt 5.15 and C++. The table TOOL exists, as I have it open in SQLite Manager with a test row already inserted.

Below is the function that is supposed to submit the data:

//passes data to database upon clicking submit button
void Add_item::on_submitButton_clicked()    {
    QString name, location, safety, summary, uses, idNumber;
    idNumber =       ui->testIdBox->text();
    name =           ui->nameField->text();
    location =       ui->locationField->text();
    safety =         ui->safetyField->text();
    summary =        ui->summaryField->text();
    uses =           ui->useField->text();

    QSqlQuery qry;
    qry.prepare("INSERT INTO TOOLS (TOOL_ID, TOOL_NAME, TOOL_SUMMARY, TOOL_LOCATION, TOOL_USE, TOOL_SAFETY) "
                "VALUES (:idNumber, :name, :summary, :location, :uses, :safety)");
    
    //binding all values to prevent sql injection attacks
    qry.bindValue(":idNumber", idNumber);
    qry.bindValue(":name", name);
    qry.bindValue(":summary", summary);
    qry.bindValue(":location", location);
    qry.bindValue(":uses", uses);
    qry.bindValue(":safety", safety);


    if(qry.exec()){
        QMessageBox::critical(this,tr("Confirmation Message"),tr("Success!"));
    }
    else    {
        QMessageBox::critical(this,tr("Confirmation Message"),tr("Error, data was not saved."), qry.lastError().text());
    }
    connClose();
}

Here is where I connect to the database:

//connecting to database
bool Add_item::connOpen()   {
    QSqlDatabase mydb = QSqlDatabase::addDatabase("QSQLITE");
    mydb.setDatabaseName("C:/Users/laesc/OneDrive/Documents/ToolBuddy/test.db");
    if (mydb.open())    {
        ui->statusLabel->setText("Connected!");
        qDebug()<<("Connected");
        return true;
    }
    else    {
        ui->statusLabel->setText("Connection Not Successful...");
        qDebug()<<("Not Connected");
        return false;
    }
}
catfriendly
  • 1
  • 3
  • It lloks like the string concatenation in the call `qry.prepare` is wrong... I think you need a `+` between the 2 strings otherwise they will be considered 2 different parameters for the function `prepare`: `qry.prepare("INSERT INTO TOOLS (TOOL_ID, TOOL_NAME, TOOL_SUMMARY, TOOL_LOCATION, TOOL_USE, TOOL_SAFETY) " + "VALUES (:idNumber, :name, :summary, :location, :uses, :safety)");` – Marco Beninca May 02 '22 at 06:44
  • @MarcoBeninca I went ahead and tried and it threw an immediate error. Invalid operands to binary expression – catfriendly May 02 '22 at 06:59
  • 1
    @MarcoBeninca no the original code is correct in that respect, C++ does concatenation of `const char *` literals without + in between, see e.g. [this answers to another question](https://stackoverflow.com/a/6061677) (bottom) – codeling May 02 '22 at 07:06
  • SQLite3 probably does not support `:name` style of paramaters. You can try `?` placeholders... – HiFile.app - best file manager May 02 '22 at 08:05
  • Ok @catfriendly you are rigth... it's better to use a std::string or a QString to create the SQL statement and then pass it to the prepare method – Marco Beninca May 02 '22 at 10:08
  • @MarcoBeninca It is perfectly fine as it is. The char array literal converts automatically into `QString`. And you should not mess with `std::string` in Qt facing code, that does not provide any benefit. – HiFile.app - best file manager May 02 '22 at 10:29

1 Answers1

0

For future viewers, I ended up reducing the number of parameters to 2 and the query worked. For one reason or another the query didn't work if I tried to bind more than 2 variables. I'm just doing multiple queries.

catfriendly
  • 1
  • 3