[SOLVED] SQLite Update query from LineEdit

[nHx4U]

Hello;

I have looked at (and tried) many of the responses that have touched on this issue, but I still can't get it to work. The "insert/add query works with LineEdit variables BUT the update query (edit existing record) only works properly when I hardcode a value in the where = . The other fields are read/written correctly from the LineEdit fields on the form. code segment is attached along with error messages.

Thanks for your help.

CUSTOMERINFO.CPP

@#include "customerinfo.h"
#include "ui_customerinfo.h"
#include<QMessageBox>

CustomerInfo::CustomerInfo(QWidget *parent) :
QDialog(parent) ,
ui(new Ui::CustomerInfo)
{
ui->setupUi(this);

Login conn;
     if (!conn.connOpen())
             ui->label_sec_status->setText("CONNECTION FAILED");
     else
            ui->label_sec_status->setText("CONNECTED. . . . .");

}

CustomerInfo::~CustomerInfo()
{
delete ui;
}

void CustomerInfo::changeEvent(QEvent *e)
{

}

void CustomerInfo::on_pushButton_Add_clicked()
{
Login conn;
QString PsW, LastName, UserName, LotNo;
PsW=ui->txt_cid->text();
LastName=ui->txt_lastname->text();
UserName=ui->txt_username->text();
LotNo=ui->txt_lotno->text();

if(!conn.connOpen())
{
    qDebug()<<"Connection Failed";
    return;
}

conn.connOpen();
QSqlQuery qry;

// IF MYDB.OPEN

    {

   qry.prepare("INSERT into Test_LOGIN (PassWord,LastName,UserName,LotNo) values (?,?,?,?)");

    qry.addBindValue(PsW);   // if ? as values in query then bindvalue as LineEdit name ----- txt_cid etc
    qry.addBindValue(LastName);
    qry.addBindValue(UserName);
    qry.addBindValue(LotNo);

    if(qry.exec() )

      {
        QMessageBox::critical(this, tr("Save"), tr("Saved"));
        conn.connClose();
       }
    else
    {
        QMessageBox::critical(this, tr("Error"),qry.lastError().text() );
    }

}
}

void CustomerInfo::on_pushButton_Edit_clicked()
{
Login conn;
QString PsW, LastName, UserName, LotNo;
PsW=ui->txt_cid->text();
LastName=ui->txt_lastname->text();
UserName=ui->txt_username->text();
LotNo=ui->txt_lotno->text();

if(!conn.connOpen())
{
qDebug()<<"Connection Failed";
return;
}

conn.connOpen();
QSqlQuery qry;

// IF MYDB.OPEN

   {
   qry.prepare("UPDATE Test_LOGIN set PassWord=?, LastName=? , UserName=?, LotNo=?  WHERE PassWord=5");

   qry.addBindValue(PsW);   // if ? as values in query then bindvalue as LineEdit name ----- txt_cid etc
   qry.addBindValue(LastName);
   qry.addBindValue(UserName);
   qry.addBindValue(LotNo);

   if(qry.exec() )

     {

       qDebug()<<"OK ROWS AFFECTED AAA "<<UserName<<"  "<< qry.numRowsAffected();
        QMessageBox::critical(this, tr("Edit"), tr("Updated"));
       conn.connClose();
      }
   else
   {
      qDebug()<<"ERROR ROWS AFFECTED 222 "<<pwd<<"  "<< qry.numRowsAffected();
       QMessageBox::critical(this, tr("Error"),qry.lastError().text() );
   }

}
}
@

---

*MESSAGES:
*
When “PassWord” field hard coded as “5”
database location: "C:/Users/Steve/Desktop/Qt_Tut-015/SQLIte_Login/SQLIte_Login/debug/data/dbCURR_SQLite.db"
CONNECTED. . . . .
OK ROWS AFFECTED AAA "7" 1
QSqlDatabasePrivate::removeDatabase: connection 'qt_sql_default_connection' is still in use, all queries will cease to work.

*When “?” replaces “5”
*
Message Box Error
Parameter count mismatch

*qDebug error:

*CONNECTED. . . . .
ERROR ROWS AFFECTED 222 "" -1

[jmimi]

Why you open database several times?!!!

For "Parameter count mismatch" error, I say when you bind values according to your case, you should obey count of calling addBindValues and count of '?' symbol.
It's better you bind by names according below example:
@
qry.prepare("UPDATE Test_LOGIN set PassWord=:pass, LastName=:name WHERE PassWord=:pass");
qry.bindValue(":pass", password);
qry.bindValue(":name", display_name);
@
Now the bound name is important not count of parameters.

[nHx4U]

Hi jmimi:

Thank you very much. It actually works! :-)

[KingslyEn]

@jmimi said in [SOLVED] SQLite Update query from LineEdit:

qry.prepare("UPDATE Test_LOGIN set PassWord=:pass, LastName=:name WHERE PassWord=:pass");

It's useful yet :) Thanks a lot...