QSqlError Parameter count mismatch (I've read previous posts )

michalos

Hi,
I have a problem with updating fields in my SQLite database.
I am trying to change valuest in a row using:

query.prepare("UPDATE group_names SET (name, favorite, private, owner, department) WHERE id;"
              "VALUES (:name, :favorite, :private, :owner, :department)");
query.bindValue(":name", name);
query.bindValue(":favorite", favorite);
query.bindValue(":gn_private", gn_private);
query.bindValue(":owner", owner);
query.bindValue(":department", department);
query.bindValue(":id", id);

or

query.prepare("UPDATE contacts VALUES SET (contactid=?, name =?, extno =?, private =?, favorite =?, owner =?, type =?, info =?, im =?, dafaultphoneid =?) WHERE contactid=?;");
query.addBindValue( contactid);
query.addBindValue( ct_name );
query.addBindValue( extno);
query.addBindValue( ct_private);
query.addBindValue( ct_favorite);
query.addBindValue( ct_owner);
query.addBindValue( type );
query.addBindValue( info);
query.addBindValue( im);
query.addBindValue( defaultphoneid);
query.addBindValue( contactid);

But when I do query.exec() all I get is

"UPDATE group_names SET (name, favorite, private, owner, department) WHERE id;VALUES (?, ?, ?, ?, ?)"
QSqlError("", "Parameter count mismatch", "")

or

"UPDATE contacts VALUES SET (name =?, extno =?, private =?, favorite =?, owner =?, type =?, info =?, im =?, dafaultphoneid =?) WHERE contactid=?;"
QSqlError("", "Parameter count mismatch", "")

I read that most of this errors are due to mistyping something, but I don't seem to find any mistake in my statement.

VRonin

@michalos said in QSqlError Parameter count mismatch (I've read previous posts ):

but I don't seem to find any mistake in my statement

ehm... you are missing =:id after WHERE id and there is a typo in query.bindValue(":gn_private", gn_private);, it should be query.bindValue(":private", gn_private);

Anyhow, I'm not sure the syntax you are using is valid SQL

michalos

Thank You for Your answer.

I've left only

    query.prepare("UPDATE group_names SET (id = :id, name= :name, favorite= :favorite, private= :private, owner= :owner, department = :department)"); //WHERE (SELECT name, favorite, private, owner, department) FROM group_names WHERE id = :id) ");
    query.bindValue(":id", id);
    query.bindValue(":name", name);
    query.bindValue(":favorite", favorite);
    query.bindValue(":private", gn_private);
    query.bindValue(":owner", owner);
    query.bindValue(":department", department);

But I still get

"UPDATE group_names SET (id = ?, name= ?, favorite= ?, private= ?, owner= ?, department = ?)"
QSqlError("", "Parameter count mismatch", "")

michalos

Ok. I've made it work, but i think that there is an isue when it comes to name binding..

I've tried:

    query.prepare("UPDATE contacts SET name = :name, extno = :extno, private = :private, favorite = :favorite, owner = :owner, type = :type, info = :info, im = :im, defaultphoneid = :defaultphoneid WHERE contactid = :contactid");
    query.bindValue(":name", ct->getCont_name() );
    query.bindValue( ":extno",  ct->getCont_extno());
    query.bindValue( ":private", ct->getCont_private());
    query.bindValue( ":favorite", ct->getCont_favorite());
    query.bindValue( ":owner", ct->getCont_owner());
    query.bindValue( ":type", ct->getCont_type() );
    query.bindValue( ":info", ct->getCont_info());
    query.bindValue( ":im", ct->getCont_im());
    query.bindValue( ":defaultphoneid", ct->getCont_defaultPhoneId());
    query.bindValue( ":contactid", ct->getCont_defaultPhoneId());

and it's not working.
but binding by position worked with values, and by caling get function:

    query.prepare("UPDATE contacts SET name = ?, extno = ?, private = ?, favorite = ?, owner = ?, type = ?, info = ?, im = ?, defaultphoneid = ? WHERE contactid = ?");
    query.addBindValue( ct->getCont_name());
    query.addBindValue( ct->getCont_extno());
    query.addBindValue( ct->getCont_private());
    query.addBindValue( ct->getCont_favorite());
    query.addBindValue( ct->getCont_owner());
    query.addBindValue( ct->getCont_type());
    query.addBindValue( ct->getCont_info());
    query.addBindValue( ct->getCont_im());
    query.addBindValue( ct->getCont_defaultPhoneId());
    query.addBindValue( ct->getCont_id());

I would like to know why, though..

BjornW

I don't know how this stuff works, but I think you are too quick to call Qt bugged :)

mrjj

Hi
I cannot reproduce it.
Binding works fine here in Qt5.7 mingw.

So if you can reproduce in a small sample. then please post it.

Test code.

bool createConnection() {
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName(":memory:");
  if (!db.open()) {
    QMessageBox::critical(0, qApp->tr("Cannot open database"), "Click Cancel to exit.", QMessageBox::Cancel);
    return false;
  }
  QSqlQuery query;
  qDebug() << "table:" <<   query.exec("create table person (id int primary key, "
                                       "firstname varchar(20), lastname varchar(20), num int )");
  query.exec("insert into person values(101, 'Dennis', 'Young','1')");
  query.exec("insert into person values(102, 'Christine', 'Holand','2')");
  query.exec("insert into person values(103, 'Lars junior', 'Gordon','4')");
  query.exec("insert into person values(104, 'Roberto', 'Robitaille','5')");
  query.exec("insert into person values(105, 'Maria', 'Papadopoulos','3')");
  return true;
}
// test
  createConnection();

  QSqlQuery query;
  int ok = query.prepare("SELECT firstname,num FROM person WHERE num=:thenum AND firstname=:thename AND lastname=:lastname");
  query.bindValue(":thenum", 4);
  query.bindValue(":thename", "Lars junior");
  query.bindValue(":lastname", "Gordon");
  query.exec();

  ok = query.prepare("update person set lastname=:lastname WHERE firstname=:thename");
  query.bindValue(":thenum", 4);
  query.bindValue(":thename", "Lars junior");
  query.bindValue(":lastname", "CHANGED");
  qDebug() << ">UPDATE:" << query.exec() << "-" << query.lastError().databaseText() ;

  ok = query.prepare("SELECT * from person ");
  query.exec();
  while (query.next()) {
    QString name = query.value(2).toString(); // col 1 = name
    qDebug() << "lastname:" << name;
  }

VRonin

@michalos said in QSqlError Parameter count mismatch (I've read previous posts ):

I would like to know why, though

Q_ASSERT(query.driver()->hasFeature(QSqlDriver::NamedPlaceholders));

michalos

@mrjj Your code works fine in MSVC 2015 64 bit so it must be my mistake.
I apologise for jumping into conclusions before thorough examination of the subject.

I am very grateful for all Your answers.

michalos

Could someone mark the topic as SOLVED? I don't know why, but I don't have such option in the Topic Tools

mrjj

@michalos
No worries :)
Sometimes is indeed a Qt bug :)
You must first select Ask as Question. ( on first post)
Then it can be Marked as Solved. ( Yes we are aware this is very confusing :)

michalos

Thanks :)