Results 1 to 15 of 15

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

  1. #1
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

    Default 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

  2. #2
    Join Date
    Oct 2009
    Posts
    151
    Thanks
    6
    Thanked 13 Times in 11 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    Your record names have spaces in them!

  3. #3
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

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

    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.

  4. #4
    Join Date
    Oct 2009
    Posts
    151
    Thanks
    6
    Thanked 13 Times in 11 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

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

  5. #5
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,525
    Thanked 279 Times in 274 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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".
    Last edited by Lesiok; 27th April 2010 at 18:03.

  6. #6
    Join Date
    Oct 2009
    Posts
    151
    Thanks
    6
    Thanked 13 Times in 11 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    Quote Originally Posted by JD2000 View Post
    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:
    Qt Code:
    1. QSqlQuery* aQuery = new QSqlQuery(db);
    To copy to clipboard, switch view to plain text mode 
    This sometimes helps

    Failing that
    You can also compare the number of parameters QT thinks it has
    Qt Code:
    1. const QVector<QVariant> values = boundValues();
    2. int paramCount = values.count();
    To copy to clipboard, switch view to plain text mode 
    against how many sqlite thinks it has
    Qt Code:
    1. int paramCount = sqlite3_bind_parameter_count(aQuery);
    To copy to clipboard, switch view to plain text mode 
    Last edited by JD2000; 27th April 2010 at 21:25.

  7. #7
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

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

    @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;
    }

  8. #8
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,525
    Thanked 279 Times in 274 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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.

  9. #9
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

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

    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
    Qt Code:
    1. QSqlQuery* Contacts::getUpdateSQLQueryPtr()
    2. {
    3. QSqlQuery *aQuery = 0;
    4.  
    5. if (contactObjectInUse)
    6. {
    7. aQuery = new QSqlQuery();
    8. QString updateStatement = "\"update Contacts set ";
    9. if (nameInUse)
    10. updateStatement += "name = :name, ";
    11. if (urlInUse)
    12. updateStatement += "url = :url, ";
    13. if (contactTypeInUse)
    14. updateStatement += "contactType = :contactType, ";
    15. updateStatement += "where contactID = :contactID\"";
    16. int i = updateStatement.indexOf(", where");
    17. // strip the final comma out
    18. if (i >= 0)
    19. updateStatement.remove(i, 1);
    20.  
    21. aQuery->prepare(updateStatement);
    22. cout << "Contacts update statement: " << qPrintable(updateStatement) << endl;
    23.  
    24. if (nameInUse)
    25. aQuery->bindValue(":name", name);
    26. if (urlInUse)
    27. aQuery->bindValue(":url", url);
    28. if (contactTypeInUse)
    29. aQuery->bindValue(":contactType", contactType);
    30. aQuery->bindValue(":contactID", contactID);
    31. }
    32. return aQuery;
    To copy to clipboard, switch view to plain text mode 

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

    main
    Qt Code:
    1. QPointer<Contacts> aContact = new Contacts();
    2. aQueryPtr = new QSqlQuery();
    3. aQueryPtr->exec("select url, contactType from contacts where contactID = 4");
    4. aQueryPtr->next();
    5. // set aContact to selected values;
    6. aContact->setContactID(4);
    7. aContact->setUrl(aQueryPtr->value(0).toString());
    8. aContact->setContactType(aQueryPtr->value(1).toString());
    9.  
    10. // set aContact variables to new values;
    11. aContact->setUrl("www.a_really_cool_url");
    12.  
    13.  
    14. aQueryPtr = aContact->getUpdateSQLQueryPtr();
    15. // check bound values
    16. QMapIterator<QString, QVariant> i(aQueryPtr->boundValues());
    17. while (i.hasNext())
    18. {
    19. i.next();
    20. cout << qPrintable(i.key()) << ": "
    21. << qPrintable(i.value().toString()) << endl;
    22. }
    23. int numBoundValues = aQueryPtr->boundValues().size();
    24. cout << "Number of bound values = " << qPrintable(QString::number(numBoundValues)) << endl;
    25. //int paramCount = sqlite3_bind_parameter_count(aQueryPtr);
    26. //cout << "SQLite number of bound values = " << qPrintable(QString::number(paramCount)) << endl;
    27.  
    28. aQueryPtr->exec();
    29. cout << " QUERY IS: " << qPrintable(aQueryPtr->lastQuery()) << endl;
    30. delete aContact;
    To copy to clipboard, switch view to plain text mode 

    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

  10. #10
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

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

    Fixed it.

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

    Cheers
    Jeff

  11. #11
    Join Date
    Oct 2009
    Posts
    151
    Thanks
    6
    Thanked 13 Times in 11 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    Glad you solved it,

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

  12. #12
    Join Date
    Apr 2010
    Posts
    77
    Thanks
    10
    Thanked 7 Times in 6 Posts
    Qt products
    Qt4
    Platforms
    MacOS X

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

    Thanks JD

    Thats good to know for future use.

    Cheers
    Jeff

  13. #13
    Join Date
    Dec 2010
    Posts
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Symbian S60

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

    I had a similar problem leading to "parameter count mismatch" error.
    I was trying to bind the table name like:
    Qt Code:
    1. q.prepare("SELECT * FROM :table");
    2. q.bind(":table", "myTable");
    3. q.exec();
    To copy to clipboard, switch view to plain text mode 

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

  14. The following user says thank you to remy_david for this useful post:

    curtwagner1984 (13th May 2017)

  15. #14
    Join Date
    Apr 2017
    Posts
    2
    Thanks
    1
    Platforms
    Unix/X11 Windows

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

    Quote Originally Posted by remy_david View Post
    I had a similar problem leading to "parameter count mismatch" error.
    I was trying to bind the table name like:
    Qt Code:
    1. q.prepare("SELECT * FROM :table");
    2. q.bind(":table", "myTable");
    3. q.exec();
    To copy to clipboard, switch view to plain text mode 

    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:

    Qt Code:
    1. query.prepare("INSERT INTO Mytable (?,?) VALUES (?,?))"
    2. query.addBindValue("columnOne");
    3. query.addBindValue("columnTwo");
    4. query.addBindValue("firstValue");
    5. query.addBindValue("secondValue");
    To copy to clipboard, switch view to plain text mode 

  16. #15
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    4,783
    Thanks
    262
    Thanked 773 Times in 763 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

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

    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:

    Qt Code:
    1. QString myTableName = "MyTable";
    2. QString column1Name = "columnOne";
    3. QString column2Name = "columnTwo";
    4. QString queryStr = QString( "INSERT INTO %1 (%2,%3) VALUES (?,?);" ).arg( myTableName ).arg( column1Name ).arg( column2Name );
    5.  
    6. QSqlQuery query;
    7. query.prepare( queryStr );
    8. query.addBindValue( "firstValue" );
    9. query.addBindValue( "secondValue" );
    10. query.exec();
    To copy to clipboard, switch view to plain text mode 
    Last edited by d_stranz; 16th May 2017 at 04:55.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

Similar Threads

  1. Parameter count mismatch in create table statement
    By croscato in forum Qt Programming
    Replies: 5
    Last Post: 4th February 2011, 10:38
  2. Replies: 1
    Last Post: 7th April 2010, 22:46
  3. Replies: 3
    Last Post: 25th August 2009, 14:03
  4. "Treat wchar_t as Built-in Type" to "yes" link error
    By sungaoyong in forum Qt Programming
    Replies: 1
    Last Post: 5th June 2008, 12:45
  5. QFile Problem~ "Unknow error" in "open(QIODevice::ReadWrite)"
    By fengtian.we in forum Qt Programming
    Replies: 3
    Last Post: 23rd May 2007, 16:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.