Using bound values in insert gives "parameter count mismatch" error in SQLite

Hi all

Any help is appreciated.

This is the error output:
ROLLBACK Parameter count mismatch

This is the create statement for the SQLite database table entered through the command line:
create table Contacts(contactID integer primary key, name text, URL text, contactType text);

This is the relevant code:
db.transaction();
QSqlQuery* aQuery = new QSqlQuery();
aQuery->prepare("insert into Contacts (contactID, name, URL, contactType) "
"values (?, ?, ?, ?)");
aQuery->addBindValue(5);
aQuery->addBindValue("New One");
aQuery->addBindValue("www.newone.com");
aQuery->addBindValue("web");
aQuery->exec();
if (!aQuery->isActive())
{
db.rollback();
cout << "ROLLBACK" << qPrintable(aQuery->lastError().text()) << endl;
}
else
{
db.commit();
cout << "COMMIT" << endl;
}

Cheers
Jeff

Your record names have spaces in them!

Hi JD2000

What do you mean?

Also I just changed the query to a simple:
aQuery = new QSqlQuery();
aQuery->exec("insert into contacts (contactID, name, URL, contactType) values (4, 'New One', 'www.newone.com', 'web')");

and got this error:
"no such table: contacts Unable to execute statement"

So have I actually connected to the database properly? This is the code:
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setHostName("localhost");
db.setDatabaseName("test.db");
if (!db.open())
{
cout << "DB Didn't Open: " << qPrintable(db.lastError().text()) << endl;
}

Since I'm not getting the error code for not opening, I must be getting a connection somewhere.
Just to check, QT comes with SQLite as part of <QtSQL> so I don't have to add anything else do I? For instance somewhere in the Qt documentation it talks about linking in the driver to the Qt Library.

In your create table statement, I believe that field names with spaces need to be quoted.

In firts post You are using name of table "Contacts", now you are using "contacts". And this is why You have an error : "no such table: contacts Unable to execute statement".

Quote:

---

Originally Posted by JD2000

In your create table statement, I believe that field names with spaces need to be quoted.

---

Apologies, I misread the 'text' data types as being part of the respective field names, this is not what caused the mismatch.

You could try explicity stating which DB is being queried:

Code:

---

QSqlQuery* aQuery = new QSqlQuery(db);

---

This sometimes helps

Failing that
You can also compare the number of parameters QT thinks it has

Code:

---

const QVector<QVariant> values = boundValues();
int paramCount = values.count();

---

against how many sqlite thinks it has

Code:

---

int paramCount = sqlite3_bind_parameter_count(aQuery);

---

@Lesiok
I tried it with both uppercase and lowercase c for contacts and it still doesn't work.

@JD2000
I'll keep those in mind but think I'll try and get the "no such table Contacts" error sorted out first.
I tried specifying the database as you suggested but it didn't help.

I tried a simple select statement and still get the "DIDN'T WORK: no such table: Contacts Unable to execute statement" error:

aQuery->exec("select name, URL, contactType from Contacts where contactID = 2");
if (!aQuery->isActive())
{
cout << "DIDN'T WORK: " << qPrintable(aQuery->lastError().text()) << endl;
}
else
{
while (aQuery->next()) {
QString aName = aQuery->value(1).toString();
QString aUrl = aQuery->value(2).toString();
QString aContactID = aQuery->value(3).toString();
cout << "Name is " << qPrintable(aName) << ", URL is " << qPrintable(aUrl) << ", ContactID is " <<
qPrintable(aContactID) << endl;
}
cout << "COMMIT" << endl;
}

First please use tag CODE for examples. It will be more readable.
Second show us TRUE code - full method or procedure in which You try SQL.

I got the select and insert worked out.
Insert now works with bound values.

But now I can't get the update working - I get the same error "parameter count mismatch".
Relevant code is below. Note that the QSqlQuery is returned as a pointer from the Contacts Class and executed in main.

Contacts class

Code:

---

QSqlQuery* Contacts::getUpdateSQLQueryPtr()
{
    QSqlQuery *aQuery = 0;
 
    if (contactObjectInUse)
    {
        aQuery = new QSqlQuery();
        QString updateStatement = "\"update Contacts set ";
        if (nameInUse)
            updateStatement += "name = :name, ";
        if (urlInUse)
            updateStatement += "url = :url, ";
        if (contactTypeInUse)
            updateStatement += "contactType = :contactType, ";
        updateStatement += "where contactID = :contactID\"";
        int i = updateStatement.indexOf(", where");
        // strip the final comma out
        if (i >= 0)
            updateStatement.remove(i, 1);
 
        aQuery->prepare(updateStatement);
        cout << "Contacts update statement: " << qPrintable(updateStatement) << endl;
 
        if (nameInUse)
            aQuery->bindValue(":name", name);
        if (urlInUse)
            aQuery->bindValue(":url", url);
        if (contactTypeInUse)
            aQuery->bindValue(":contactType", contactType);
        aQuery->bindValue(":contactID", contactID);
    }
    return aQuery;

---

Note: name, url and contactType are all QStrings. contactID is an int.

main

Code:

---

QPointer<Contacts> aContact = new Contacts();
            aQueryPtr = new QSqlQuery();
            aQueryPtr->exec("select url, contactType from contacts where contactID = 4");
            aQueryPtr->next();
            // set aContact to selected values;
            aContact->setContactID(4);
            aContact->setUrl(aQueryPtr->value(0).toString());
            aContact->setContactType(aQueryPtr->value(1).toString());
 
            // set aContact variables to new values;
            aContact->setUrl("www.a_really_cool_url");
 
 
            aQueryPtr = aContact->getUpdateSQLQueryPtr();
            // check bound values
            QMapIterator<QString, QVariant> i(aQueryPtr->boundValues());
            while (i.hasNext())
            {
                i.next();
                cout << qPrintable(i.key()) << ": "
                          << qPrintable(i.value().toString()) << endl;
            }
            int numBoundValues = aQueryPtr->boundValues().size();
            cout << "Number of bound values = " << qPrintable(QString::number(numBoundValues)) << endl;
            //int paramCount = sqlite3_bind_parameter_count(aQueryPtr);
            //cout << "SQLite number of bound values = " << qPrintable(QString::number(paramCount)) << endl;
 
            aQueryPtr->exec();
            cout << " QUERY IS: " << qPrintable(aQueryPtr->lastQuery()) << endl;
            delete aContact;

---

Also I tried using sqlite3_bind_parameter_count to see how many bound values SQLite thought it had but I got an error:
'sqlite3_bind_parameter_count was not declared in this scope'.
How do I fix that?

The output is below

:contactID: 4
:contactType: web
:url: www.a_really_cool_url
Number of bound values = 3
QUERY IS: "update Contacts set url = :url, contactType = :contactType where contactID = :contactID"
ROLLBACK - Parameter count mismatch

Thanks for all the help
Jeff

Fixed it.

It was in getUpdateSQLQueryPtr() lines 8 and 15. I was surrounding the string with quotation marks.

Cheers
Jeff

Glad you solved it,

FYI 'sqlite3_bind_parameter_count' is included in "sqlite3.h"

Thanks JD

Thats good to know for future use.

Cheers
Jeff

I had a similar problem leading to "parameter count mismatch" error.
I was trying to bind the table name like:

Code:

---

QSqlQuery q;
q.prepare("SELECT * FROM :table");
q.bind(":table", "myTable");
q.exec();

---

This is not possible, one can only bind filed values.
Could be useful for future readers.

Quote:

---

Originally Posted by remy_david

I had a similar problem leading to "parameter count mismatch" error.
I was trying to bind the table name like:

Code:

---

QSqlQuery q;
q.prepare("SELECT * FROM :table");
q.bind(":table", "myTable");
q.exec();

---

This is not possible, one can only bind filed values.
Could be useful for future readers.

---

Your post helped my figure out what I was doing wrong with the same problem.
It's should be pointed out that it's also impossible to bind column names like so:

Code:

---

query.prepare("INSERT INTO Mytable (?,?) VALUES (?,?))"
query.addBindValue("columnOne");
query.addBindValue("columnTwo");
query.addBindValue("firstValue");
query.addBindValue("secondValue");

---

Quote:

---

It should be pointed out that it's also impossible to bind column names like so:

---

That's why the method is named addBindValue(). It isn't a general purpose "substitute anything for anything" method, it is specifically for binding variable values to VALUES() placeholders in a query.

If you need to specify things like table and column names at run time, then construct the prepare() statement as a QString:

Code:

---

QString myTableName = "MyTable";
QString column1Name = "columnOne";
QString column2Name = "columnTwo";
QString queryStr = QString( "INSERT INTO %1 (%2,%3) VALUES (?,?);" ).arg( myTableName ).arg( column1Name ).arg( column2Name );
 
QSqlQuery query;
query.prepare( queryStr );
query.addBindValue( "firstValue" );
query.addBindValue( "secondValue" );
query.exec();

---