[SQLLITE] - whoes (Parameter count mismatch upon Insert)

helgur

I am having issues making my program insert data into an SQLLite db file. I am a bit lost due to the ambigious error message aswell, as I am passing the correct parameters to the insert command(?)

Here is the SQL Lite Table structure:

@CREATE TABLE inventory (
id INT PRIMARY KEY NOT NULL,
name CHAR(120) NOT NULL,
description TEXT,
category INT,
employee INT,
location INT
);@

And here is my QT/C++ Code:

@ QSqlQuery query(this->db);

if (!this->db.open()) {
    qDebug() <<this->db.lastError().text();
}

query.prepare
        ("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);



if (!query.exec&#40;&#41;) {
    qDebug() <<query.lastError().text();
}@

For the record, the QSqlDatabase object is initiated in the constructor, and I am able to run SELECT queries from the database. So the connection works ....

@ QSettings settings;
this->db = QSqlDatabase::addDatabase("QSQLITE", "NETTO_CONNECTION");
this->db.setDatabaseName(settings.value("db").toString());@

Any ideas to what I am doing wrong here?

dheerendra

It is issue with your bind value. What is 'name', 'description' etc in addBindValue call ? Are they string variables ?

you can look at following. It may help you .

@QSqlQuery query;
query.prepare("INSERT INTO person (employeeid, company, name) "
"VALUES (?, ?, ?)");
query.addBindValue(2456);
query.addBindValue("pthinks.com");
query.addBindValue("dheerendra");
query.exec();@

helgur

The BindValue parameters is defined as follows:

@ QString name = ui->inputName->text();
QString description = ui->inputDescription->toPlainText();
int category = ui->inputCategory->currentData().toInt();@

(name and description is a QString, category is an integer. I think that is correct?)

I've also changed:

@QSqlQuery query(this->db);@

To:

@QSqlQuery query(this->db.database("NETTO_CONNECTION"));@

And the table structure is now:

@CREATE TABLE inventory ( id INTEGER PRIMARY KEY NOT NULL UNIQUE, name CHAR(120) NOT NULL, description TEXT, category INTEGER, employee INTEGER, location INTEGER );@

Following the advice from someone else (changed table INT definition to INTEGER).

Still no dice!

dheerendra

It may be issue with constructor. It will get executed if you give constructor. Just try with the following and it should work.

@QSqlQuery query;

if (!this->db.open()) {
    qDebug() <<this->db.lastError().text();
}

query.prepare
        ("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);@

helgur

Sorry, but I don't understand. Could you be a little more specific? If you are referring to the object in which I execute the code, the constructor works as I am able to do SELECT statements from a different function:

@void NewInventory::populateFields() {
QSqlQuery query(this->db.database("NETTO_CONNECTION"));

if (!this->db.open()) {
    qDebug() <<this->db.lastError().text();
}

query.prepare
        ("SELECT id, name FROM invCat");
if (!query.exec&#40;&#41;) {
    qDebug() <<query.lastError().text();
}
while (query.next()) {
    ui->inputCategory->addItem(query.value(1).toString(),QVariant(query.value(0).toInt()));
}

}@

Or do you mean something else? I don't really understand what you want me to try as the code you provide in your answer is the exact same code I am using (and quoted in my first post) ......

dheerendra

It is not exact. You are providing something in the constructor while declaring the QSqlQuery. It may be a issue there.

helgur

I now created a new QSqlDatabase object on the stack:

@ QSettings set;
QSqlDatabase base;
base.addDatabase("QSQLITE", "NETTO");
db.setDatabaseName(set.value("db").toString());

QString name = ui->inputName->text();
QString description = ui->inputDescription->toPlainText();
int category = ui->inputCategory->currentData().toInt();

ui->inputName->clear();
ui->inputDescription->clear();

ui->inputName->setFocus();

QSqlQuery query(base.database("NETTO"));

if (!this->db.open()) {
    qDebug() <<db.lastError().text();
    qDebug() <<"Not open\n";
}

query.prepare
        ("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);



if (!query.exec&#40;&#41;&#41; {
    qDebug() <<query.lastError().text();
}@

But the result is still the same

clochydd

Hi,
if you still see the same error message, I suggest to start debugging by inserting ALL fields in the first step.
If this operation is successful you may reduce the fields in your INSERT, if not, post your error message again in this thread.

helgur

I tried that long before I made this thread. Doesn't work, same error message

helgur

Allright, revisiting this thread as I have come up with a solution to my problem, and not contributing to a closure when you have found a workaround is bad form.

Basically the solution was be more competent. (even though I have no idea why my initial approach didn't work in the first place, but I digress).

I scrapped the notion of running direct queries on the Sqlite database, and switched my entire codebase over to QT's model view architecture. So where my code previously looked like this:

@ QSqlQuery query(this->db);

if (!this->db.open()) {
    qDebug() <<this->db.lastError().text();
}

query.prepare
        ("INSERT INTO inventory (name, description, category) VALUES (?,?,?)");
query.addBindValue(name);
query.addBindValue(description);
query.addBindValue(category);



if (!query.exec&#40;&#41;) {
    qDebug() <<query.lastError().text();
}@

It now looks like this:

@ QHash<QString, QVariant> tableInsert;

tableInsert["name"] = ui->inputName->text();
tableInsert["description"] = ui->inputDescription->toPlainText();
tableInsert["category"] = ui->inputCategory->currentData().toInt();

if (!this->inventory->insert(tableInsert)){
    qDebug() << this->inventory->getModel().lastError().text();
} else {
    ui->inputName->clear();
    ui->inputDescription->clear();
    
    ui->inputName->setFocus();        
}@

The Inventory object has a QSqlTableModel attached to it, it's "insert" method reads like this:

@bool Dataset::insert(QHash<QString, QVariant> values) {

QHashIterator<QString, QVariant> i(values);
QSqlRecord record = this->model->record();

while (i.hasNext()){
    i.next();
    record.setValue(i.key(), i.value());
}

this->model->insertRecord(-1, record);
return this->model->submitAll();

}@

(where the model object is offcourse an instance of QSqlTableModel).

This way I not only get more readable code, but I manage to abstract the code into a more logical workflow.

Let me know if you have any comments or suggestions for improvement